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 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.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 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 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 is found under the C:\Developers folder, then the folder name Projects are returned in the strOut variable, otherwise returns an empty string.
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 the presence of the Projects folder and 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 exists, otherwise, it returns an empty string:
? Dir("D:", vbVolume)
Result: RECOVERY
Earlier Post Link References:
we can also use combine Len() with Dir() to test file existing. we could say:
ReplyDeleteIf Len(Dir(FilePath)) Then
.....
End If