Microsoft DOS Commands in VBA
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 it’s parent application through the Shell() Command or make a copy of that file to a different location with the FileCopy() Command or delete 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.exe
Public 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 specific location on 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 Function
Dir() Function also can be used for checking the presence of a folder in preparation for creating a new folder in a particular location on the Hard Drive.
The following Command checks for the presence of a particular folder on C: drive:
strOut = Dir("C:\Developers\Projects", vbDirectory)
The second parameter vbDirectory tells the Dir() command what to look for and if the folder Projects found under C:\Developers folder, then the folder name Projects returned in the strOut variable, otherwise returns an empty string.
The MkDir() Command can be used for creating a new folder if the Projects folder doesn’t exists.
Let us write a small program to check the presence of Projects folder and if it doesn’t exists 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
Dir() Command can be used to check the Volume label of a Disk Drive.
The following command, run directly from the Debug window, gets the Volume Label of the Hard Drive, if exists, otherwise returns an empty string:
? Dir("D:", vbVolume)