Continued from Last Week.
Continued from last week’s Article: Disk Operating System Commands in VBA.
Once we determine the presence of a file in a folder with the Dir() Command, we can do certain operations on the file, like opening that file in its parent application through the Shell() Command or making a copy of that file to a different location with the FileCopy() Command, or deleting it with the Kill() Command.
Example-1:
Check for the presence of a text file in a folder, and if found, open it in Notepad.exePublic Function OpenTextFile() Dim txtFilePath As String Dim NotePad As String txtFilePath = "C:\msaccesstips\htaccess.txt" NotePad = "C:\Windows\System32\Notepad.exe" If Dir(txtFilePath, vbNormal) = "htaccess.txt" Then Call Shell(NotePad & " " & txtFilePath, vbNormalFocus) Else MsgBox "File: " & txtFilePath & vbcr & "Not Found...!" End If End Function
Example-2:
: Make a copy of the file with the FileCopy() Command.Public Function CopyTextFile() Dim SourcefilePath As String Dim TargetFilePath As String SourcefilePath = "C:\msaccesstips\htaccess.txt" TargetFilePath = "C:\New Folder\htaccess.txt" If Dir(SourcefilePath, vbNormal) = "htaccess.txt" Then FileCopy SourcefilePath, TargetFilePath MsgBox "File copy complete." Else MsgBox "File Not Found...!" End If End Function
Example-3: Find and Delete a File from a specific location on the Hard Disk.
Public Function DeleteFile()
Dim FilePath As String, msgtxt As String
FilePath = "C:\New Folder\htaccess.txt"
If Dir(FilePath, vbNormal) = "htaccess.txt" Then
msgtxt = "Delete File: " & FilePath & vbCr & vbCr
msgtxt = msgtxt & "Proceed...?"
If MsgBox(msgtxt, vbYesNo + vbDefaultButton2 + vbQuestion, "DeleteFile()") = vbNo Then
Exit Function
End If
Kill FilePath
MsgBox "File: " & FilePath & vbCr & "Deleted from Disk."
Else
MsgBox "File: " & FilePath & vbCr & "Not Found...!"
End If
End FunctionCheck for a Folder Name:
Dir() Function can be used to check whether a particular folder exists on the Disk. The following Command checks for the presence of a particular folder on the C: drive:
strOut = Dir("C:\Developers\Projects", vbDirectory)
The second parameter, vbDirectory, specifies what the Dir() command should look for in the specified Path. If the folder Projects is found under the C:\Developers folder, the folder name Projects is returned in the strOut variable; otherwise, an empty string is returned.
The MKDIR Command.
The MkDir() Command can be used for creating a new folder if the Projects folder doesn't exist.
Let us write a small program to check whether the Projects folder exists. If it doesn’t exist, then let us create the folder.
Public Function CreateFolder()
Dim folderPath As String
Dim msgtxt As String
folderPath = "C:\Developers\Projects"
If Dir(folderPath, vbDirectory) = "" Then
msgtxt = "Create new Folder: " & folderPath & vbCr & "Proceed ...?"
If MsgBox(msgtxt, vbYesNo + vbDefaultButton1 + vbQuestion, "CreateFolder()") = vbNo Then
Exit Function
End If
MkDir folderPath
If Dir(folderPath, vbDirectory) = "Projects" Then
msgtxt = folderPath & vbCr & "Created successfully."
MsgBox msgtxt
Else
msgtxt = "Something went wrong," & vbCr & "Folder creation was not successful."
MsgBox msgtxt
End If
Else
msgtxt = folderPath & vbCr & "Already exists."
MsgBox msgtxt
End If
End Function The Dir() Command can check the volume label of the Disk Drive.
The following command, run directly from the Debug window, gets the Volume Label of the Hard Drive if it exists; otherwise, it returns an empty string:
? Dir("D:", vbVolume)
Result: RECOVERY
Earlier Post Link References:
DateAndSeq.zip
