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)
   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."
   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."
   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
      msgtxt = "Something went wrong," & vbCr & "Folder creation was not successful."
      MsgBox msgtxt
   End If
   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)