Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

DIRectory and File Copy Utility


Last week we saw how to use Dir () DOS Command, its ability to read files from the Disk one by one and display it in the Debug Window.

In continuation of that, we will create a VBA Utility using DIR Command with a very useful VBA Statement FileCopy (it is a statement, not a Function) to read and transfer files from one folder to a different location on the disk.  The files can be of any type, like *.pdf, *.docx, Xls, or *.* (all files).

The files will be read and listed in a Listbox from the selected folder, specified in a text box.,  with the use of the DIR() Command.  All the files in the list or selected ones can be copied to a different location specified in a text box, defined as the target location.

The Utility Form.

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


The design is simple with two text boxes, one Listbox,  three Command Buttons, and a Label Control to display messages from this Utility Program.  You can download this Utility Form in 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, then ensure that you give the controls the same names as given above because the VBA code, that you are going to copy, and paste into the Module, will reference all these names in the Code

Besides the above main controls, there is a Label Control below the first Source Textbox showing examples as to how to specify Source File Path correctly.

The label control at the bottom of the form shows messages that pop up during validation checks of the inputs and when errors are detected, during the execution of the VBA Code.

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


You may create this User Interface with the names of the Controls as given above.  After designing the form with the correct names for the controls, display the VBA Window of the Form, Copy and Paste the following code into the Form’s 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

Exit Sub

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) & ","
    strfile = Dir() ' read next file

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

Exit Sub

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

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

End If


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

Exit Sub

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

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
   End If

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
Exit Sub

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 may copy different sets of files from the list of files displayed in the List Box to different Target Folders by selecting the files (after de-selecting earlier selections) and after changing the Destination Location address in the Text Control.

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


DIR Getting File Names From Folder


We all know Dir() Function from the time of the Windows DOS Operating System.  This is the first Command introduced to those who sit on a Personal Computer to learn how to use Computers.  This command has several options, to get the output from the disk in so many ways, under Windows Operating System.  You can take a full list of Folders, Subfolders, and Files from the hard disk in a single command.  The entire list can be sent to a Printer or saved into a text file with the use of the redirection symbol (>).

We are not going to use all those options here.  We will see how Dir() Function is used in VBA to read file names from a folder one by one and display them in the Debug Window.  Every time we run this function with a Folder Path as a parameter, it returns the first file name from the folder.  Now, the question is how to get the next few file names or all the files one-by-one from the Folder.

Using DIR Command in Debug Window.

We will try 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 Microsoft Access VBA Window and then display 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 any specific folder name in the function parameter, it looks for files in the active folder on the disk.

  3. Now, issue the following command without any parameter to get the next file name in the current folder
    ? Dir()
    ? Dir
  4. Each time you run the DIR() command it will get the next file from the 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\")
    ? Dir("D:\Documents\*.*")

If D:\Documents\ folder doesn't have any files in it 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 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()
End Function

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

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

All the files from the specified folder will be printed with a serial number in the debug window. After reading and printing the last file from the folder the Dir() function executes one more time and ends up with an empty string. The Do While condition will prove false and the program stops.

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


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

The above example will read-only Excel files and print in the Debug window.

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





Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code