UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Function To Show Progress Bar While Copy/move Files, Access 2016    
 
   
aggiemarine07
post Aug 9 2019, 02:17 PM
Post#1



Posts: 32
Joined: 7-January 11



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 (http://www.UtterAccess.com/forum/lofiversion/index.php/t2020070.html).

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.
Go to the top of the page
 
June7
post Aug 9 2019, 02:34 PM
Post#2



Posts: 737
Joined: 25-January 16



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.
CODE
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)

rs.MoveLast
rs.MoveFirst
'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)
        rs.MoveNext
    End With
Loop

'Remove the Progress Meter
varReturn = SysCmd(acSysCmdClearStatus)

rs.Close

End Sub

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
theDBguy
post Aug 9 2019, 02:38 PM
Post#3


Access Wiki and Forums Moderator
Posts: 76,073
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
aggiemarine07
post Aug 9 2019, 02:57 PM
Post#4



Posts: 32
Joined: 7-January 11



@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.
Go to the top of the page
 
June7
post Aug 9 2019, 03:05 PM
Post#5



Posts: 737
Joined: 25-January 16



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.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
aggiemarine07
post Aug 9 2019, 03:14 PM
Post#6



Posts: 32
Joined: 7-January 11



@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)
Go to the top of the page
 
aggiemarine07
post Aug 9 2019, 03:15 PM
Post#7



Posts: 32
Joined: 7-January 11



found a solution! https://stackoverflow.com/questions/1422717...th-progress-bar

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
Public Const FOF_SIMPLEPROGRESS = &H100

Public Type SHFILEOPSTRUCT
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)
Dim op As SHFILEOPSTRUCT

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

'~~> Perform operation
SHFileOperation op
End Sub
Go to the top of the page
 
theDBguy
post Aug 9 2019, 03:18 PM
Post#8


Access Wiki and Forums Moderator
Posts: 76,073
Joined: 19-June 07
From: SunnySandyEggo


QUOTE
@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...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
strive4peace
post Aug 9 2019, 03:30 PM
Post#9


strive4peace
Posts: 20,447
Joined: 10-January 04



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.






--------------------
have an awesome day,
crystal
Go to the top of the page
 
aggiemarine07
post Aug 9 2019, 03:39 PM
Post#10



Posts: 32
Joined: 7-January 11



@theDBguy yep makes sense; thanks for giving me the detailed explanation smile.gif
Go to the top of the page
 
aggiemarine07
post Aug 9 2019, 03:42 PM
Post#11



Posts: 32
Joined: 7-January 11



@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.....
This post has been edited by aggiemarine07: Aug 9 2019, 03:43 PM
Go to the top of the page
 
theDBguy
post Aug 9 2019, 03:47 PM
Post#12


Access Wiki and Forums Moderator
Posts: 76,073
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
strive4peace
post Aug 11 2019, 02:59 PM
Post#13


strive4peace
Posts: 20,447
Joined: 10-January 04



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.

PREFACE

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

SCRIPTING

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

OBJECT BROWSER

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:

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


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

where n = the number of files copied

then when done:
CODE
   '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:
CODE
         sFilename = oFile.Name
           n = n + 1
           sMsg = "copying " & n & " of " & nCount & ": " & sFilename
           'show message on Status Bar
           Application.SysCmd acSysCmdSetStatus, sMsg


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


===

that said, here is code you can use:

CODE
' 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
    
        'PARAMETERS
        '  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:

CODE
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"
        Else
           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


LOGIC

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

Parameters
psPathSource
the folder to copy files FROM

psPathTo
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)

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

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

Note:

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



This post has been edited by strive4peace: Aug 11 2019, 03:57 PM
Attached File(s)
Attached File  Object_Browser___Scripting___File___Copy__s4p.png ( 24.21K )Number of downloads: 2
 

--------------------
have an awesome day,
crystal
Go to the top of the page
 
aggiemarine07
post Aug 13 2019, 08:12 AM
Post#14



Posts: 32
Joined: 7-January 11



thanks for the code! Even though I found a solution that works for me, I will keep this in mind for the future.
Go to the top of the page
 
ADezii
post Aug 13 2019, 11:53 AM
Post#15



Posts: 2,550
Joined: 4-February 07
From: USA, Florida, Delray Beach


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.
This post has been edited by ADezii: Aug 13 2019, 11:53 AM
Attached File(s)
Attached File  Screen_Dump.JPG ( 31.57K )Number of downloads: 3
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd August 2019 - 08:30 PM