DIRectory and File Copy Utility

Introduction.

Last week, we explored how to use the Dir() DOS command to read files from the disk one by one and display their names in the Debug window.

Building on that, we will now create a VBA utility that uses the Dir() command together with the very useful FileCopy statement (note that it is a statement, not a function) to read and transfer files from one folder to another location on the disk. The files can be of any type—for example, *.pdf, *.docx, *.xls, or *.* (all files).

This utility will read the files from a folder specified in a text box, list them in a list box using the Dir() command, and allow you to copy either all the listed files or only the selected ones to a target folder specified in another text box.

The Utility Form.

The design view image of a Form created for this purpose is given below for reference:

Right-click to open Large Image in New Tab/Window.

The form design is simple, consisting of two text boxes, one list box, three command buttons, and a label control to display messages from the utility program. You can download this utility form as part of a sample database at the end of this article.

These are the names of the Controls on the Form:

1. Top Text box: Source

2. Text Box 2:  Target

3. List Box:  List1

4. Top Command Button: cmdDir

5. Second Command Button: cmdSelected

6. Last Command Button: cmdClose

7. Bottom empty Label Name: msg

Note: If you are designing this form yourself, make sure that the same control names are used as described above. The VBA code you will copy into the module references these exact names.

In addition to the main controls, there is a Label control below the first (source) text box that provides examples of how to specify the source file path correctly.

Another label control at the bottom of the form displays messages during input validation and shows error notifications while the VBA code is executing.

An Image of a sample run of the FileCopy Statement is given below:

Right-click to open Large Image in New Tab/Window.

You can create this user interface using the control names provided above. Once the form is designed with the correct control names, open the VBA window for the form and copy the following code into its VBA module:

The Form Module Code.

Option Compare Database
Option Explicit
Dim strSource1 As String
Dim strSource2 As String, strMsg As String

Private Sub cmdClose_Click()
On Error GoTo cmdClose_Click_Error

If MsgBox("Close File Copy Utility?", vbOKCancel + vbQuestion, "cmdClose_Click()") = vbOK Then
   DoCmd.Close acForm, Me.Name, acSaveYes
End If

cmdClose_Click_Exit:
Exit Sub

cmdClose_Click_Error:
MsgBox Err.Description, , "cmdClose_Click()"
Resume cmdClose_Click_Exit
End Sub

Private Sub cmdDir_Click()
'=========================================================
'Author : a.p.r.pillai
'Date   : June 2018
'Purpose: Take directory listing
'Rights : All Rights Reserved by www.msaccesstips.com
'=========================================================
Dim strSource As String, strMsg As String
Dim i As Integer, x As String
Dim j As Integer, strfile As String
Dim strList As ListBox, LList As String

On Error GoTo cmdDir_Click_Err
msg.Caption = ""

'Read Source location address
strSource = Nz(Me!Source, "")
If Len(strSource) = 0 Then
    strMsg = "Source Path is empty."
    MsgBox strMsg,vbOKOnly + vbCritical, "cmdDir_Click()"
msg.Caption = strMsg
    Exit Sub
End If

'check for the last back-slash location
'this can be used to split the folder name
'and file name type values separately.

i = InStrRev(strSource, "\")

'get the folder name part into the variable
strSource1 = Left(strSource, i)

'take file type (*.docx, *.exl, *.txt etc.) value into a separate
'variable temporarily
If Len(strSource) > i Then
    strSource2 = Right(strSource, Len(strSource) - i)
End If

'define Listbox object
Set strList = Me.List1

'Read the first file from the folder
strfile = Dir(strSource, vbHidden)
If Len(strfile) = 0 Then
    strMsg = "No Files of the specified type: '" & strSource2 & "' in this folder."
    MsgBox strMsg, vbCritical + vbOKOnly, "cmdDir()"
    msg.Caption = strMsg
    Exit Sub
End If

j = 0
LList = ""
Do While Len(strfile) > 0
   If Left(strfile, 1) = "~" Then 'ignore backup files, if any
      GoTo readnext:
   End If
    j = j + 1 'File list count
    LList = LList & Chr(34) & strfile & Chr(34) & ","
    
readnext:
    strfile = Dir() ' read next file
Loop

LList = Left(LList, Len(LList) - 1) ' remove the extra comma at the end of the list
strList.RowSource = LList 'insert the files list into the listbox RowSource property
strList.Requery 'refresh the listbox
msg.Caption = "Total: " & j & " Files found."

Me.Target.Enabled = True

cmdDir_Click_Exit:
Exit Sub

cmdDir_Click_Err:
MsgBox Err.Description, , "cmdDir_Click()"
Resume cmdDir_Click_Exit

End Sub


Private Sub cmdSelected_Click()
'=========================================================
'Author : a.p.r.pillai
'Date   : June 2018
'Purpose: Copy Selected/All Files to Target Location
'Rights : All Rights Reserved by www.msaccesstips.com
'=========================================================

Dim lstBox As ListBox, ListCount As Integer
Dim strfile As String, j As Integer, t As Double
Dim strTarget As String, strTarget2 As String
Dim chk As String, i As Integer, yn As Integer
Dim k As Integer

On Error GoTo cmdSelected_Click_Err

msg.Caption = ""
'Read Target location address
strTarget = Trim(Nz(Me!Target, ""))

'validate Destination location
If Len(strTarget) = 0 Then
   strMsg = "Enter a Valid Path for Destination!"
   MsgBox strMsg, vbOKOnly + vbCritical, "cmdSelected()"
   msg.Caption = strMsg
   Exit Sub
ElseIf Right(strTarget, 1) <> "\" Then
      strMsg = "Correct the Path as '" & Trim(Me.Target) & "\' and Re-try"
      MsgBox strMsg, vbOKOnly + vbCritical, "cmdSelected()"
      msg.Caption = strMsg
      Exit Sub
End If

'Take a count of files in listbox
Set lstBox = Me.List1
ListCount = lstBox.ListCount - 1

'take a count of selected files, if any, for copying
i = 0
For j = 0 To ListCount
If lstBox.Selected(j) Then
  i = i + 1
End If
Next

'identify user's response for copy
If (i = 0) And (ListCount > 0) Then
       strMsg = "Copy all Files..?"
       Me.cmdSelected.Caption = "Copy All"
Else
       strMsg = "Copy Selected Files..?"
       Me.cmdSelected.Caption = "Copy Marked files"

End If

'Me.cmdSelected.Requery

'get copy option from User
yn = MsgBox(strMsg, vbOKCancel + vbQuestion, "cmdSelected_Click()")

'Run Copy selected option
If (i = 0) And (yn = vbOK) Then
    GoSub allCopy
ElseIf (i > 0) And (yn = vbOK) Then
    GoSub selectCopy
Else
    Exit Sub
End If

'disable Copy button to stop a repeat copy of the same files.
'Remarks: User can make fresh selections from the same list
'To copy them to the same target locatiion.
'Or to a different location by specifying different Path
'in the Destination Text Box
Me.List1.SetFocus
Me.cmdSelected.Enabled = False

'Display copy status
strMsg = "Total " & k & " File(s) Copied." & vbCrLf & "Check the Target Folder for accuracy."
MsgBox strMsg, vbInformation + vbOKOnly, "cmdSelected_Click()"
Me.msg.Caption = strMsg

cmdSelected_Click_Exit:
Exit Sub

allCopy:
k = 0
For j = 0 To ListCount
    strfile = lstBox.ItemData(j)
   
    strSource2 = strSource1 & strfile
    strTarget2 = strTarget & strfile
    
    FileCopy strSource2, strTarget2
  'give enough time to copy the file
  'before taking the next file
  k = k + 1
  t = Timer()
  Do While Timer() > (t + 10)
    'do nothing
  Loop
Next
Return

selectCopy:
k = 0
For j = 0 To ListCount
   If lstBox.Selected(j) Then
        strfile = lstBox.ItemData(j)
        strSource2 = strSource1 & strfile
        strTarget2 = strTarget & strfile
        
            FileCopy strSource2, strTarget2
               'give enough time to copy the file
               'before taking the next file
               k = k + 1
                t = Timer()
                Do While Timer() > (t + 10)
                    'do nothing
                Loop
   End If
Next
Return


cmdSelected_Click_Err:
MsgBox Err.Description, , "cmdSelected_Click()"
Me.msg.Caption = Err.Description
Resume cmdSelected_Click_Exit

End Sub


Private Sub List1_AfterUpdate()
On Error GoTo List1_AfterUpdate_Error
Me.cmdSelected.Enabled = True
List1_AfterUpdate_Exit:
Exit Sub

List1_AfterUpdate_Error:
MsgBox Err.Description, , "List1_AfterUpdate()"
Resume List1_AfterUpdate_Exit
End Sub

You may save the Form with the name FileCopy.

Note: FileCopy is a VBA Statement, not a built-in Function.

You can copy different sets of files from the list displayed in the list box to different target folders by first deselecting any previous selections, selecting the desired files, and updating the destination folder address in the text box.

Download the Demo Database.

You may download the sample database with the VBA Code from the Link given below:

Download FileCopy2007.zip
Download (2003) FileCopy.zip

• Disk Operating System Commands in VBA
• Saving Report Pages as Separate PDF Files
• How to Use Form Filter on Report
• Back-tracking Open Forms

Read More

DIR Getting File Names From Folder

Introduction.

Most of us are familiar with the Dir() function from the days of the DOS operating system. Dir() has often been the very first command introduced to anyone learning how to use a personal computer. Under Windows, this command still offers several options for retrieving information from the disk in various ways. For example, you can generate a complete list of folders, subfolders, and files on a hard disk with a single command and even redirect that list to a printer or save it to a text file using the redirection symbol (>).

In this article, however, we will focus only on how the Dir() function is used in VBA to read file names from a folder one by one and display them in the Debug window. When you run this function with a folder path as a parameter, it returns the first file name found in that folder. The question then is: how do we retrieve the remaining file names—one after another—from the same folder?

Using the DIR Command in the Debug Window.

We will try the Dir() Function from the Debug Window directly, so that it is easy to understand how to use this function to get a few file names from a folder one after the other.

1. Open the Microsoft Access VBA Window and then display the Debug Window (Ctrl+G).
2. Type the following command in the Debug Window and press Enter Key:

   ? Dir("")

   Dir() Function with an empty string as a parameter will fetch the first file name from the Current Folder and display it in the debug window. 

   Since we have not given a specific folder or pathname in the function parameter, it looks for files in the active folder on the disk.

3. Now, issue the following command without any parameters to get the next file name in the current folder

   ? Dir()
   OR
   ? Dir

4. Each time you run the DIR() command, it will get the next file from the same folder.
5. Use a specific Folder Path as the parameter, in place of the empty string, to get files from that particular folder.
6. Example:

   ? Dir("D:\Documents\")
   OR
   ? Dir("D:\Documents\*.*")
   

If D:\Documents\folder doesn't have files, then the above command will return an empty string. If you go further and execute the Dir command again, then it will end up with an error message.

There is an optional second parameter to the Dir() Command that we have not used in the above examples. Since this is a DOS Command executed in its own window, we can specify this second parameter to show its normal window(vbNormal) or hide the execution window (vbHidden), among other options available.

A VBA Wrapper Function for the DIR Function.

I have written a small function for you to list all the files in a folder in the Debug Window.

Public Function TestDir(ByVal strFolder As String) As String
'Function Usage e.g.: TestDir "D:\Documents\"
Dim j As Integer, strFile As String
'files counter
j = 1
'Run the function with the specified folder in a hidden window
strFile = Dir(strFolder, vbHidden)
'Next steps of Dir() function is nested in a loop
'to read all the files and print in the Debug Window

Do While Len(strFile) > 0
 Debug.Print j & ":" & strFile
 j = j + 1
 strFile = Dir()
Loop
End Function

Call the function from the Debug Window by giving the full path of the Folder as the parameter.

? TestDir("D:\Documents\")
OR
? TestDir("D:\Documents\*.*")

All the files from the specified folder will be listed in the Debug window along with a serial number. After reading and printing the last file from the folder, the Dir() function is called one more time and returns an empty string. At this point, the Do While condition evaluates to False, and the program stops.

If you need only a specific type of File to be read and displayed, then you may specify the parameter with the file type extension.

Example:

? TestDir("D:\Documents\*.xls")

The above example retrieves only Excel files and prints in the Debug window.

I have used the terms Function and Command interchangeably. Dir() is referred to as a Function in VBA reference documents and as a Command in Disk Operating System documents; both refer to the same operations done in different environments.

• Disk Operating System Commands in VBA
• Microsoft DOS Command in VBA
• Microsoft DOS Command in VBA-2
• ChDir and IN Clause of Access Query
• Deleting Folders with DOS Command

Read More

Deleting Folders with DOS Command

Introduction.

We have already seen how to create folders with the MkDir() DOS command and how to change the default folder to the active database’s folder using the ChDir() command.

Conversely, if we can create folders, we should also be able to remove them. The RmDir() command serves this purpose, though it is not as commonly used as MkDir(). Typically, folders or sub-folders are created to organize files for easier access when needed. Folder removal becomes necessary only when files are relocated or deleted, and freeing up disk space is required.

To understand its usage, you can try running the RmDir() command directly from the Immediate Window (Debug Window), as shown below:

RmDir "C:\MyFolder"
Validation Checks.

1. The RmDir() command has a built-in safety check. When executed, it first verifies whether the specified folder is completely empty—containing no files or subfolders. If the command runs successfully, it completes silently without displaying any message. However, if the folder is not empty or cannot be removed, one of the following two error messages will appear:

   If the specified folder path does not exist, it will show a 'Path Not Found' Error Message.

2. If the folder is not empty, then the message ‘Path/File access error’ is displayed.

Through Windows Explorer, we can remove a folder with all its sub-folders and files in one clean sweep.  If this is done by mistake, then we can always restore them from the Recycle bin, also before emptying it.

Important Note: Folders or files deleted from a network location using Windows Explorer are not sent to the Recycle Bin. If you have the necessary access rights to delete items on the network, recovery cannot be done locally. In such cases, you must contact your Network Administrator to restore them from the most recent LAN backup. As a safeguard, many organizations allow users to create folders but restrict deletion rights to administrators, reducing the risk of accidental data loss.

A Custom Function.

Let’s create a small function named FolderDeletion() that will execute the RmDir() DOS command with proper validation checks. You can add this function to your common utilities library for reuse.

1. Before physically removing a folder, the function will perform the following validations:

   1. Check if the folder exists – Ensure the specified path is valid.

   2. Confirm the folder is empty – The folder should not contain any subfolders or files.

   3. Validate permissions – Confirm the user has rights to delete the folder.

   4. Error handling – Provide meaningful messages if deletion fails.

The Function VBA Code:

Public Function DeleteFolder(ByVal strFolder As String)
On Error Resume Next

If Len(Dir(strFolder, vbDirectory)) > 0 Then
  'Folder exists, ask for permission to delete the folder
  If (MsgBox("Deleting Folder: '" & strFolder & "', Proceed...?", vbOKCancel + vbDefaultButton2 + vbQuestion, "DeleteFolder()") = vbNo) Then
     'User says not to delete the folder, exit program
     GoTo DeleteFolder_Exit
  Else
     'Delete Folder
     RmDir strFolder
     
     If Err = 75 Then 'folder is not empty, have sub-folders or files
        MsgBox "Folder: '" & strFolder & "' is not empty, cannot be removed."
        GoTo DeleteFolder_Exit
     Else
        MsgBox "Folder: '" & strFolder & "' deleted."
        GoTo DeleteFolder_Exit
     End If
  End If
Else
  MsgBox "Folder: '" & strFolder & "' Not found."
End If

DeleteFolder_Exit:
On Error GoTo 0
End Function

If you want an alternative to the same result, we can use VBScript in Microsoft Access to do that.  VB Script is mostly used in Web Pages for server-side actions.  VB Script uses the FileSystemObject to manage Drives, Folders, & Files.  We have used it for creating Text, Word, and Excel Files before.

You can find those examples in the following links:

• Create Text File Access.
• Create Excel, Word Files.

VBScript Function: FolderCreation()

First, let us write a VBScript Function to create a Folder -  C:\MyProjects.

Public Function FolderCreation(ByVal strFolder As String)
Dim FSysObj, fldr
  
  On Error Resume Next 'arrange to capture the error so that it can be check
  'Create the File System Object
  Set FSysObj = CreateObject("Scripting.FileSystemObject")
  'Call the Create Folder Method of the File System Object with Folder Path as parameter
  Set fldr = FSysObj.CreateFolder(strFolder)
  'if this action ended up with error code 58 then the folder already exists
  If Err = 58 Then
     MsgBox "Folder: '" & strFolder & "' already exists."
     GoTo FolderCreation_Exit
  Else
     MsgBox "Folder: " & strFolder & " created successfully."
  End If
  
FolderCreation_Exit:
On Error GoTo 0
End Function

Copy and paste the above function into the Standard Module of your database.  You can try the function by calling it from the Debug Window with a folder name as shown below:

FolderCreation "C:\MyProjects"

VBScript Function: FolderDeletion().

After the sample run, use Windows Explorer to look for the folder name c:\MyProjects. The following VB Script Function FolderDeletion() can be used for removing a folder:

Public Function FolderDeletion(ByVal strFolder As String)
  Dim FSysObj, fldr
  
  On Error Resume Next
  Set FSysObj = CreateObject("Scripting.FileSystemObject")
  Set fldr = FSysObj.GetFolder(strFolder)
  If Err = 76 Then
     MsgBox "Folder: '" & strFolder & "' No found!"
  Else
     If MsgBox("Delete Folder: '" & strFolder & "' Proceed...?", vbOKCancel + vbDefaultButton2 + vbQuestion, "FolderDeletion()") = vbNo Then
         GoTo FolderDeletion_Exit
     Else
         fldr.Delete 'call the Delete Method of the Folder Object
         MsgBox "Folder: '" & strFolder & "' Deleted."
     End If
  End If
FolderDeletion_Exit:
On Error GoTo 0
End Function

Copy and paste the above code into the Standard Module of your database.  You can run the above code either from the Debug Window or call it from a Command Button Click Event Procedure.

Sample Run from Debug Window:

FolderDeletion "C:\MyProjects"

OR

Private Sub cmdRun_Click()
    FolderDeletion txtFolderPath
End Sub

Earlier Post Link References:

• Users and Groups Listing from Workgroup Information File
• Access Security Key Diagram
• Updating Sub-Form Recordset from Main Form
• Easy Reference: Access2003 Commands in Access2007
• IIF vs Switch function in MS-Access

Read More

Microsoft DOS Commands in VBA-2

Continued from Last Week.

With the MkDir() Command, we were able to create a folder on disk with a small VBA routine that we wrote last week.  We don’t even need a separate program to do this; we can directly execute this command from the Debug Window, like the following example:

MkDir "C:\Developers\Projects"

The only disadvantage of this method is that we cannot perform a validation check before executing this command.  In the VBA program, we have included the validation checks.  That program uses constant values as Path, and with a few modifications, this program can be further improved to accept the Path string as a Parameter to the CreateFolder() Function.  The Code with improvements is given below:

Creating a Folder.

Public Function CreateFolder(ByVal folderPath As String)

Dim msgtxt As String, folderName As String

'extract the new Folder Name from the folderPath Parameter
folderName = Right(folderPath, Len(folderPath) - InStrRev(folderPath, "\"))

'check for the new folder name, if not found proceed to create it
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 'try to create the new folder

'check whether the folder creation was successful or not
   If Dir(folderPath, vbDirectory) = folderName Then
      msgtxt = folderPath & vbCr & "Created successfully."
      MsgBox msgtxt
   Else
'if the code execution enters here then something went wrong
      msgtxt = "Something went wrong," & vbCr & "Folder creation was not successful."
      MsgBox msgtxt
   End If
Else
  'the validation check detected the presence of the folder
   msgtxt = folderPath & vbCr & "Already exists."
   MsgBox msgtxt
End If

End Function

In all the above and earlier examples, we have provided the full path of the existing location, where the new folder is to be created, with the new folder name at the end.  If you are sure where the current location is (or the active path of the current database on disk), then you can issue the MkDir() command with the new folder name alone, like the following example:

MkDir "Projects"

Finding out the Current Folder.

As far as VBA is concerned, the current location is not what you have selected using Windows Explorer. Or the one selected using DOS Command ChDir(), which runs directly under the DOS Command Prompt.

But, with a small trick, we can find out which is the current folder that VBA is aware of, that is, by running the Shell() command directly from the Debug Window to invoke the DOS Command Prompt from VBA, like the example given below:

Call Shell("cmd.exe")

The above command will open the DOS Command Prompt (if it is minimized on the taskbar, then click on it to make that window current), and the Cursor will be positioned in the current folder. Check the sample image given below:

If you have used MkDir "Projects" like a command without knowing where it is going to be created, then type Dir and press Enter Key to display a list of files and directory names with the label <Dir> to indicate they are folders.

That doesn’t mean the above method is the only option to check the Default Database Folder location.  Select Access Options from the Office Button, and select the Popular Option Group(Access 2007), and the Default Database Folder settings. You can change it if it becomes necessary.  Check the image given below:

Try to open a database from another location on disk, but this setting will not change, and the Default Database Folder will remain active as per this setting.  Without touching the above Access default setting, we can change the active folder to the newly opened database’s parent directory with the use of the following DOS Commands from VBA (you can try this by typing these commands directly on the Debug Window):

? CurrentProject.Path

This is not a DOS Command, but the above VBA statement retrieves the active database's Path. Let us assume that the retrieved location of the current database is: C:\MDBS

Using the above information in the next two DOS Commands, we can change the control to the active database's location, without changing the Default Database Path setting, which we have seen earlier:

ChDrive "C" 'change control to C: Drive.  This is necessary if the control was on a different drive
ChDir CurrentProject.Path 'change control to the active database's folder

ChangeDir() Command.

By combining the above statements, we can write a very useful Function ChangeDir() to change the control to the current Database Folder.  Copy and paste the following Code into a Standard Module of your Database and save it:

Public Function ChangeDir()
Dim vDrive As String * 1, sysPath As String

'get current database Path
  sysPath = CurrentProject.Path

'extract the drive letter alone
'vDrive Variable is dimensioned to hold only one character
  vDrive = sysPath 

'change control to the Drive
  ChDrive vDrive 

'change current location to the database folder
  ChDir sysPath 

End Function

Call the above Function from an Autoexec macro with the RunCode Action or from the Form_Load() Event Procedure of the first Form open (like the Startup Screen or Main Switchboard) to change control to the active database folder.

• Updating Sub-Form Recordset from Main Form
• Easy Reference: Access2003 Commands in Access2007
• IIF vs Switch function in MS- Access
• Preparing Rank List
• Restoring Disabled Full Menus Access 2007

Read More

Microsoft DOS Commands in VBA

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.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 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 Function

Check for a Folder Name:

Dir() Function can also be used to inspect 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 the C: drive:

strOut =  Dir("C:\Developers\Projects", vbDirectory)

The second parameter, vbDirectory, asks the Dir() command what to look for in the specified Path. If the folder Projects is found under the C:\Developers folder, then the folder name Projects is returned in the strOut variable; otherwise, it 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. 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:

• Updating Sub-Form Recordset from Main Form
• Easy Reference: Access2003 Commands in Access2007
• IIF vs Switch function in MS- Access
• Preparing Rank List
• Restoring Disabled Full Menus in Access 2007

Read More