Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Q and A _ Function To Show Progress Bar While Copy/move Files

Posted by: aggiemarine07 Aug 9 2019, 02:17 PM

I searched here for a solution on how to display a progress bar while using VBA to copy a large number of files from my computer to a network share (sharepoint) and came across this one thread (

However, the thread is locked and the OP does not explain or show his final code that he used in order to show a progress bar while the code loops through all folders to copy them to another folder. Does anyone have an idea of how they did this? Thanks.

Posted by: June7 Aug 9 2019, 02:34 PM

This is a fairly common topic. Should be able to find more discussions with sample code. I pulled the following from one some time ago.

Sub ProgressBar()

'The following code will loop through all Records in table and print field value
'The relative completion percentage of this operation will be displayed in Progress Meter.

Dim rs As DAO.Recordset
Dim varReturn, intCounter As Long, intMax As Long

Set rs = CurrentDb.OpenRecordset("Rates", dbOpenDynaset)

'Initialize the Progress Meter, set Maximum Value to number of records
varReturn = SysCmd(acSysCmdInitMeter, "Updating...", rs.RecordCount)

Do While Not rs.EOF
    With rs
        Debug.Print rs.Fields(0)
        intCounter = intCounter + 1
        'Update the Progress Meter
        varReturn = SysCmd(acSysCmdUpdateMeter, intCounter)
    End With

'Remove the Progress Meter
varReturn = SysCmd(acSysCmdClearStatus)


End Sub

Posted by: theDBguy Aug 9 2019, 02:38 PM

Hi. Pretty much any progress bar displays in Access is just a graphic representation of "counting" down or up the whole process. For example, if you have 10 files to copy, a simple progress notification might say something like: 1 of 10, 2 or 10, 3 of 10, etc. To make it a progress bar, you simply divide the size of the bar by the progress number to have different/progressing size of the bar in another color. What the other thread, you pointed out, was saying was simply recommending/suggesting to create a code in a separate container, so it can be reusable and also give the ability to fire events, to call a function or perform the progress bar update calculation. I don't think it's really necessary right now to find out exactly how this idea was implemented in that thread. If you know any way to do it, it should also work the same way. Otherwise, as I was saying, you should be able to create a progress bar even if it's not a class object. Hope it makes sense...

Posted by: aggiemarine07 Aug 9 2019, 02:57 PM

@June7 thanks for showing me that but how do i adapt that to FSO? Im using FSO to do the file transfer and it seems that your code is using DAO to show the progress bar based on a table within the database.

Posted by: June7 Aug 9 2019, 03:05 PM

Doesn't matter what controls the loop nor what else happens within loop - put the progress bar update within whatever loop you have for copying files. Post your code.

Posted by: aggiemarine07 Aug 9 2019, 03:14 PM

@theDBguy I understand your logic and dont mind using the Windows Explorer progress bar (since my code uses FSO I think that would be easiest to implement as well)

Posted by: aggiemarine07 Aug 9 2019, 03:15 PM

found a solution!

Private Sub Sample()
'~~> Copy Files
Call VBCopyFolder("C:\Sample.Avi", "C:\NewSample.Avi")

'~~> Copy Folders
Call VBCopyFolder("C:\Temp1", "C:\Temp2")
End Sub


Public Declare Function SHFileOperation Lib "shell32.dll" _
Alias "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long

Public Const FO_COPY = &H2

hWnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Long
hNameMappings As Long
lpszProgressTitle As Long
End Type

Public Sub VBCopyFolder(ByRef strSource As String, ByRef strTarget As String)

With op
.wFunc = FO_COPY
.pTo = strTarget
.pFrom = strSource
End With

'~~> Perform operation
SHFileOperation op
End Sub

Posted by: theDBguy Aug 9 2019, 03:18 PM

@theDBguy I understand your logic and dont mind using the Windows Explorer progress bar (since my code uses FSO I think that would be easiest to implement as well)
Hi. Not sure if you understood my point or have decided to use a different method. All I was saying was what June7 also just said. The basic principle of a progress bar is knowing how long or how many files you're going to process ahead of time. You simply then just divide a specific value (like the width of your progress bar), representing 100%, with the amount of progress (files processed) so far. So, if you're using FSO, find out first how many files there are, and then in each loop as you copy each file, update your progress bar. Hope it makes sense...

Posted by: strive4peace Aug 9 2019, 03:30 PM

hi aggiemarine07 (what is your name?),

glad you got a solution, and thanks for sharing. There was confusion because it wasn't clear if you were asking about how to copy files, and/or how to show a progress meter.

if Access is processing the files one at a time, it can control updates to the screen while its running. If, however, another process does the work, then Access sits back while its happens, and during that time, screen updates are controlled by THAT process, not Access.

Posted by: aggiemarine07 Aug 9 2019, 03:39 PM

@theDBguy yep makes sense; thanks for giving me the detailed explanation smile.gif

Posted by: aggiemarine07 Aug 9 2019, 03:42 PM

@strive4peace yep fully understand now. I had a solution to copy the files but was looking for an "add-on" to show a progress bar because I was transferring so many files.

I tried to PM you with my name but it said it was disabled.....

Posted by: theDBguy Aug 9 2019, 03:47 PM

Hi. Glad to hear you're all sorted out. Good luck with your project.

Posted by: strive4peace Aug 11 2019, 02:59 PM

hi aggie,

I've written alternate code you can try, if you are still looking for a way to let the user know something is happening as each files is copied.

Processing files one at a time is not as efficient as doing them in a batch, but maybe the user will like it better to see that something is happening -- hopefully it still works ok specifying your network share folder. If not, please let us know what was the error, thanks.


the code you posted uses a Windows API
API stands for Application Programming Interface and gives VBA a way to get Windows to do things.
Instead of calling a specific API for this, VBA can instead loop through Files in the FileSystemObject for a given path


When you are in the VBE (Visual Basic Editor), where you write VBA ... you can see a menu bar across the top (File, Edit, View, ... )

choose Tools, References... from the menu,
then check the Microsoft Scripting Runtime library
... and then explore what is available woohoo.gif


Once the scripting library is referenced, you can press F2 or choose View, Object Browser from the menu. This gives you a great way to reference and learn!

In the upper left of the Object Browser window, you'll see a dropdown that says <All Libraries>. Change this to Scripting, short for Microsoft Scripting Runtime.

In the lower left, you see Classes. One of these is File.
Click on it and then on the right, you'll see its members, which include properties that describe things, like DateLastModified, and methods that do things, like Copy.

Click on Copy to select and see more about the Copy method for a file.

On the bottom, for whatever is selected, you'll see specific syntax. I've attached a picture showing Copy syntax for File

Think of this as a programmable way to do what you do interactively in Windows Explorer or My Computer

When you're done exploring and developing, UNCHECK that library, again using Tools, References... and use late-binding for deploying to users, for ability to run in any environment, unless you know you don't need to do that.

> "how to display a progress bar while using VBA to copy a large number of files from my computer to a network share"

to get a progress bar, the easy way is to use the status bar in the lower left to show the progress meter. There are three parts to doing this: 1. initialize the meter, 2. update the meter, and 3. remove the meter.

... so, if you want to use a Progress Meter, which only shows an initial message and then an updated meter, you can do this:

   'initialize the meter    Application.SysCmd acSysCmdInitMeter, "Copying " & nCount & " files", nCount

then in the loop, you can show the meter moving, but not anything else:
            'update the meter
               Application.SysCmd acSysCmdUpdateMeter, n

where n = the number of files copied

then when done:
   'remove the meter
       Application.SysCmd acSysCmdRemoveMeter

However, I prefer to construct the loop message myself so it can be more descriptive. For simplicity, let's use what is built-in (instead of a form), which is the Status Bar that displays in the lower left corner of the screen.

in the loop:
         sFilename = oFile.Name
           n = n + 1
           sMsg = "copying " & n & " of " & nCount & ": " & sFilename
           'show message on Status Bar
           Application.SysCmd acSysCmdSetStatus, sMsg

clear when done:
   'clear the StatusBar
     Application.SysCmd acSysCmdClearStatus


that said, here is code you can use:

' module name: mod_CopyFolderFilesToFolder_FileSystemObject
     Function CopyFolderFilesToFolder( _
        psPathSource As String _
        , psPathTo As String _
        , Optional psMask As String = "*.*" _
        , Optional pbOverwrite As Boolean = True _
        , Optional psPrefix As String = "" _
        ) As Long
     '190811 strive4peace copy files from one folder to another
     'copy will be done one file at a time, to show progress to user
        '  psPathSource is the folder to copy files FROM
        '  psPathTo is the folder to copy files TO
        '     if psPrefix is specified, psPathTo MUST be terminated with a folder separator such as \
        '  psMask = pattern for files to copy
        '     ie: *.*, my*.xls*, *.jpg
        '  pbOverwrite. True to overwrite file if it already exists.
        '  psPrefix is what to add to the beginning of the filename, if anything
        Dim nCount As Long _
           , n As Long _
           , sFilename As String _
           , sPathFileTo As String _
           , sMsg As String
        'early binding
        '   reference: Microsoft Scripting Runtime
     '   Dim oFile As scripting.File
        'late binding
        Dim oFile As Object
        CopyFolderFilesToFolder = 0
        n = 0
        sPathFileTo = psPathTo
        With CreateObject("Scripting.FileSystemObject").GetFolder(psPathSource)
           nCount = .Files.Count
           If Not nCount > 0 Then
              MsgBox "there are no files to copy", , "exiting"
              Exit Function
           End If
           For Each oFile In .Files
              sFilename = oFile.Name
              If psMask = "*.*" Or sFilename Like psMask Then
                 sMsg = "copying " & n + 1 & " of " & nCount & ": " & sFilename
                 'show message on Status Bar
                 Application.SysCmd acSysCmdSetStatus, sMsg
                 'copy file
                 If psPrefix <> "" Then
                    sPathFileTo = psPathTo & psPrefix & sFilename
                 End If
                 oFile.Copy sPathFileTo, pbOverwrite
                 'number of files copied
                 n = n + 1
                 CopyFolderFilesToFolder = n
              End If
           Next oFile
        End With
        'clear the StatusBar
        Application.SysCmd acSysCmdClearStatus
     End Function

and a launcher function to make it easy for you to test, and pattern after:

Sub callCopyFolderFilesToFolder()
     '190811 strive4peace launch procedure to
     '  copy files from one folder to another
        Dim sPathSource As String _
           , sPathTo As String _
           , sMask As String _
           , bOverwrite As Boolean _
           , sPrefix As String _
           , sMsg As String _
           , nCount As Long
        sPathSource = "C:\TEMP"
        sPathTo = "C:\TEMP\FolderTo\"
        sMask = "*.*"
        bOverwrite = True
        sPrefix = "copy_" & Format(Date, "yymmdd") & "_"
        'do it!
        nCount = CopyFolderFilesToFolder(sPathSource, sPathTo, sMask, bOverwrite, sPrefix)
        If nCount = 0 Then
          sMsg = "No files found in " & sPathSource & " for " & sMask
          MsgBox sMsg, , "Nothing copied"
           sMsg = nCount & " Files copied. Open Desitnation Folder?"
           If MsgBox(sMsg, vbYesNo, "Done") = vbYes Then
              'open desination folder
              Application.FollowHyperlink sPathTo
           End If
        End If
     End Sub


Dimension variables. There are 2 code blocks to dimension oFile, one of which is commented. Late-binding, discussed below, is used

Initialize the return value to be 0 (zero) since no files have been copied yet.

Set n= 0 (zero) where n represents the number of files actually copied. since no files have been copied yet.

To avoid having to create a variable for a folder, use With CreateObject("Scripting.FileSystemObject").GetFolder(psPathSource) where psPathSource is the path of the source files.

Count the files in that location and assign to nCount.

If there aren't any files, give the user a message that there are no files to copy, and exit the function.

Loop through each file in the folder.

Construct a message for the status bar with the file number being processed, the total number of files, and the current file name. Show message in the Status Bar.

If the new file will have a prefix before the rest of the name, specify that with sPathFileTo. If there is no extra prefix, sPathFileTo is whatever was sent for psPathTo, and the file name won't change.

Once copied, increment the file counter, update the return value for the function, and loop back to the next file in the folder.

When the loop is done, clear the status bar.

There is no error handling in this code. It would be good to add it ... that can be another post though

As such, there is also no cleanup if an error happens ... such as clearing the status bar ...

the folder to copy files FROM

the folder to copy files TO
if psPrefix is specified, psPathTo MUST be terminated with a folder separator such as \

Optional Parameters
psMaskpattern for files to copy
other examples: my*.xls*, *.jpg, *.*x

default is "" (all files)

True (default) to overwrite file if it already exists.

what to add to the beginning of the filename, if anything. For instance, the calling program may use something like
"copy_" & Format(Date, "yymmdd") & "_"

default is "", which means that the file name won't change.

Late vs Early Binding

One of the libraries you can reference when writing VBA is called Microsoft Scripting Runtime and contains definitions for

- File
- Folder
- Drive
- FileSystemObject - the whole the file system
- Dictionary
- TextStream

In case you're interested, the file this library is in is called scrrun.dll, which, for my installation, is located in C:\Windows\SysWOW64

Early Binding

Early Binding means that a reference library is loaded whenever an application that references it is opened.

A big bonus is that early binding gives you, the developer, the benefit of Intellisense while coding. This is super-helpful!

This is efficient when running too, since the library is pre-loaded and instantly available whenever needed.

In this case, the library is called Microsoft Scripting Runtime.

The disadvantage, however, is that early-binding makes the code version dependent. If the user isn't running the same versions, they will hit an error which could be avoided by late-binding.

Late Binding

When you deploy your application to users, it is a good idea to consider late binding. This means that whatever version of the library is available will be loaded at runtime when it is needed.

Late-binding is used in this example. Code to early-bind is there, but commented.


Processing files one at a time is not as efficient as copying files in a batch, but provides an opportunity to give the user a message as each copy is done so they can see something is happening.


this might seem like a lot to digest at once! Maybe read it tomorrow morning after some rest? ... if you are still wanting progress for each file, that is.

If you have questions about where to put the code and how to launch it, or anything else, just ask ~~ look forward to hearing back from you


Posted by: aggiemarine07 Aug 13 2019, 08:12 AM

thanks for the code! Even though I found a solution that works for me, I will keep this in mind for the future.

Posted by: ADezii Aug 13 2019, 11:53 AM

I created a simple Demo that completely bypasses the API, uses the File System Object exclusively, uses a native Access Label Control to indicate progress, and finally uses an Access Textbox for the typical Copying File# X of X display. These Controls exist in an Access Form which hopefully will be depicted below. If anyone is interested in this approach, simply let me know and I'll Upload the Demo.