Progress Bar..

N

Number11

Member
Local time
Today, 17:48
Joined
Jan 29, 2020
Messages
624
Hi, so i am looking for some code to run a progress bar form at the start of my code and then finish when the code as completed any ideas?
 
you can use syscmd to instantiate the "Meter" (syscmd acSysCmdInitMeter google its usage).
 
Look at the bottom of this thread for some existing posts about progress bars.
 

Progress Bar

Good Morning All, I have a "Simple" problem that I'm struggling to solve. My code loops through multiple txt files in a specific fold and converts them to excel files. I'm developing a piece of code to show the user the Progress of conversion of each file. What do I need to change to make it...
www.access-programmers.co.uk
 
umm haven't really got much done here, so i would like a progress form to show between the time the VBA code is running and then to stop once call the code as finished. Look at the links but seems to only show a process bar that is running on a timer, and i dont know who long the code will take to run so need to look for some other solution....
 
Unless you have a fixed number of steps that are run through a progress bar won't know how far through the process (whatever that is) it is.

If you have a loop going through 100 or 1000 records, then you have something to work with.
If you have 10 stages in your process then you have something to increment a bar with 10 % at a time.

if you have a single process that sometimes takes 20 seconds but next time take 2 minutes I'm not sure how you would expect access and therefore your progress bar to know.
 
Number11 said:
umm haven't really got much done here, so i would like a progress form to show between the time the VBA code is running and then to stop once call the code as finished. Look at the links but seems to only show a process bar that is running on a timer, and i dont know who long the code will take to run so need to look for some other solution....
Click to expand...
If you are referring to my example app, it states clearly:
For the purposes of the example database, a form timer event is used to show progress
Click to expand...
However, in normal usage, a timer event isn't used.
Instead the progress bar is updated after each action is completed. So the code goes something like this:

Code: 
 'start progress bar code
    Dim iCount As Integer
    iCount = 10 'change to match the number of events
    SetupProgressBar

    'run first event
    Procedure1 'this could be a sub, function or a query
    UpdateProgressBar

    'run second event
    Procedure2
    UpdateProgressBar     

    '....

   'run final event
    Procedure10
    UpdateProgressBar

    'remove progress bar now it has completed
    HideProgressBar

The code is normally run either from a button click or in the Form_Load event
 
Last edited:
Number11 said:
umm haven't really got much done here, so i would like a progress form to show between the time the VBA code is running and then to stop once call the code as finished. Look at the links but seems to only show a process bar that is running on a timer, and i dont know who long the code will take to run so need to look for some other solution....
Click to expand...
Why don't you explain to us, WHAT your code does and how it runs, and explain specifically what would be the logic in your code that would tell any Progress Bar what percent complete, to show? That way we don't have to guess.

For example, the link I posted contains a progress bar approach that depends on a known number of Loop iterations. I would hazard a guess that is probably the most common approach dependency as it makes the most sense. A slight "variation" of that same thing would be a "known" number of tasks (i.e., your code is going to do Thing1, then Thing2, then Thing3), and you update the Progress Bar to show 0, 30%, whatever, during it.

IMO but the first step is to make the Progress Bar itself, and create a small Sub or Function that can be called on demand which:
1) either Shows or Hides the progress bar
2) updates the caption, if you want
3) displays the % width of the bar control itself

And can be called like ShowProgress(True, "Processing", .7)

Then you look at your code and determine if the progress is predictable and makes sense to show an incrementing bar - it may not.

Not all code lends itself and makes sense to run a progress bar. As Microsoft has generously demonstrated for us by continually showing progress bars on Windows tasks that make no sense, like ones that show 95% for the entire time - That's an example of someone making a progress bar where none was called for, and a rotating GIF might have made more sense. :)
 
Hi @isladogs, how could I add your code to one where I use an each next to add records to a table in a folder on the computer? I have seen the code from post 11, but this would not work for me. Thanks
 
In the code I refer to from post 11, I see that it is a function in which other procedures are called, and between each of them, the progress bar is updated.

However, I have this code, that what it does is add the files and folders of a main folder in a table, and I want to add a progress bar. So, I don't know how to apply the above to my specific case.

Code: 
Public Sub SpanAndLog(DefaultFolderNumber As Integer)
    InitializeSpan DefaultFolderNumber
    InitializeSummationSpan DefaultFolderNumber
    DesmarcarFound
    CurrentDb.Execute "qryClearFolders"
    CurrentDb.Execute "qryClearSummations"
End Sub

Public Sub InitializeSpan(DefaultFolderNumber As Integer)
  Set FSO = New FileSystemObject
  SpanFolders GetDefaultFolderPath(DefaultFolderNumber), DefaultFolderNumber
  Set FSO = Nothing
End Sub
Public Sub InitializeSummationSpan(DefaultFolderNumber As Integer)
  Set FSO = New FileSystemObject
  SpanFolderSummations GetDefaultFolderPath(DefaultFolderNumber), DefaultFolderNumber
  UpdateTotalCounts
  Set FSO = Nothing
End Sub
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'************************************************************************  Span Folders  *********************************************************************************
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Private Sub SpanFolders(SourceFolderFullName As String, DefaultFolderNumber As Integer, Optional ParentID As Long = 0, Optional ByVal FolderLevel = 0)

' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
    'Dim FSO As Object 'Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder 'Scripting.Folder
    Dim SubFolder As Scripting.Folder 'Scripting.Folder
    Dim FileItem As Scripting.File 'Scripting.File

   ' Dim ParentID As Long

    
    Set SourceFolder = FSO.GetFolder(SourceFolderFullName)
    
    FolderLevel = FolderLevel + 1
    
    If Nz(DLookup("FolderFileID", "qryFoldersFiles", "FolderFileFullName1='" & Replace(SourceFolder.Path, "'", "") & "'"), 0) = 0 Then
        LogFilesFolders DefaultFolderNumber, SourceFolder.Name, SourceFolder.Path, SourceFolder.Type, SourceFolder.Attributes, ParentID, fft_Folder, FolderLevel, True
    End If
    
    ParentID = GetFolderID(SourceFolder.Path)
    For Each FileItem In SourceFolder.Files
        If Nz(DLookup("FolderFileID", "qryFoldersFiles", "FolderFileFullName1='" & Replace(FileItem.Path, "'", "") & "'"), 0) = 0 Then
            LogFilesFolders DefaultFolderNumber, FileItem.Name, FileItem.Path, FileItem.Type, FileItem.Attributes, ParentID, fft_File, FolderLevel, True
        End If
    Next FileItem
    
    For Each SubFolder In SourceFolder.SubFolders
        ParentID = GetFolderID(SourceFolder.Path) ' The record has just been added so get PK by name
    '   LogFilesFolders SubFolder.Name, SubFolder.Path, SubFolder.Type, ParentID, fft_Folder, FolderLevel
        SpanFolders SubFolder.Path, DefaultFolderNumber, ParentID, FolderLevel
    Next SubFolder

    Set FileItem = Nothing
    Set SourceFolder = Nothing

End Sub

Private Sub DesmarcarFound()
On Error GoTo err_lbl
Dim Nombre As String

    Dim TblFiles As DAO.Recordset

    Set TblFiles = CurrentDb.OpenRecordset("tblFoldersFiles")
            
    If TblFiles.EOF Then Exit Sub
            
    With TblFiles
    Do Until TblFiles.EOF
        .Edit
        Select Case !FileType
            Case "Carpeta de archivos"
                If Dir(!FolderFileFullName, vbDirectory) = "" Then
                    !Found = False
                Else
                    !Found = True
                End If
            Case "Oculto"
                !Found = False
            Case Else
                Nombre = !FolderFileFullName
                If Dir(!FolderFileFullName, vbArchive) = "" Then
                    !Found = False
                Else
                    !Found = True
                End If
        End Select
        .Update
        .MoveNext
    Loop
    End With
    
    TblFiles.Close
            
    Set TblFiles = Nothing
err_exit:
    Exit Sub
err_lbl:
    MsgBox Nombre
    Resume Next
End Sub

Private Sub SpanFolderSummations(SourceFolderFullName As String, DefaultFolderNumber As Integer, Optional ParentID As Long = 0, Optional ByVal FolderLevel = 0, Optional CountHiddenSystem As Boolean = False)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
    'Dim FSO As Object 'Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder 'Scripting.Folder
    Dim SubFolder As Scripting.Folder 'Scripting.Folder
    Dim FileItem As Scripting.File 'Scripting.File
    
    Dim FolderCount As Long
    Dim FileCount As Long
        
    FolderLevel = FolderLevel + 1
    Set SourceFolder = FSO.GetFolder(SourceFolderFullName)
    
    If CountHiddenSystem Then
      FolderCount = SourceFolder.SubFolders.Count
    Else
      FolderCount = 0
        For Each SubFolder In SourceFolder.SubFolders
           'If (SubFolder.Attributes And 2) <> 2 And (SubFolder.Attributes And 4) <> 4 Then
              FolderCount = FolderCount + 1
           'End If
        Next SubFolder
    End If
  
    If CountHiddenSystem Then
         FileCount = SubFolder.Files.Count   'This will count hidden as well
       Else
        FileCount = 0
        
        For Each FileItem In SourceFolder.Files
           If FileItem.Type <> "Acceso directo" And FileItem.Type <> "Archivo SRT" And FileItem.Type <> "Documento Adobe Acrobat" Then
             FileCount = FileCount + 1
           End If
        Next FileItem
        
    End If
    
    If Nz(DLookup("FolderID", "qryFolderSummations", "FolderFullName1='" & Replace(SourceFolder.Path, "'", "") & "'"), 0) = 0 Then
        LogFolderSummations DefaultFolderNumber, SourceFolder.Name, SourceFolder.Path, FolderCount, FileCount, ParentID, FolderLevel, SourceFolder.Attributes, True
    End If

    For Each SubFolder In SourceFolder.SubFolders
       ParentID = GetFolderSummationID(SourceFolder.Path)
      'FolderCount = SubFolder.SubFolders.Count
       'If (SubFolder.Attributes And 2) <> 2 And (SubFolder.Attributes And 4) <> 4 Then
            SpanFolderSummations SubFolder.Path, DefaultFolderNumber, ParentID, FolderLevel
       'End If
    Next SubFolder
    Set FileItem = Nothing
    Set SourceFolder = Nothing

End Sub
 
For most progress bars like this you need to know N. N is the total number of activities, and n the current number of activities. So the progress is n/N. In your case you do not know N (total files) until you span the files. So you probably can only have a hour glass not a progress bar. You might be able to try to span and do nothing but count files first. This would be faster than all the code to add to the database. Then with N you could do a progress bar and then conduct the more time intensive span and log. The only way that would make sense is if the span to count is far faster than the span to log files and directory. As we know FSO does not give a total file count only a local file count. Using window explorer when you get the total file count, you can watch as it traverses and finally gets to the total count.
 
By now you have probably guessed that there is more to a progress bar than meets the eye. To build and use a good progress bar you need to know some things about the process to be represented. Progress bars are USUALLY based on knowing EXACTLY how many steps there are between the start and the end of the process. In Access it is exceedingly rare to have a process cleanly divisible enough to allow for smooth progress display because smooth progress requires having control of the individual steps of the process.

If you have a VBA-based loop, you have some visibility of how many steps are involved and can compute a percent-complete value within each step of the loop. On the other hand, even if you know how many records are involved in a query, you don't see the "individual steps" of the query for very good technical and theoretical reasons. But if the loop is fast enough, it isn't usually worth it to show a progress bar anyway.

So how long is this taking and have you considered other ways of showing activity?
 
Let's go by parts.
MajP said:
For most progress bars like this you need to know N. N is the total number of activities, and n the current number of activities. So the progress is n/N. In your case you do not know N (total files) until you span the files. So you probably can only have a hour glass not a progress bar. You might be able to try to span and do nothing but count files first. This would be faster than all the code to add to the database. Then with N you could do a progress bar and then conduct the more time intensive span and log. The only way that would make sense is if the span to count is far faster than the span to log files and directory. As we know FSO does not give a total file count only a local file count. Using window explorer when you get the total file count, you can watch as it traverses and finally gets to the total count.
Click to expand...
I have already calculated the number of files that are in total in the folder. If 1000 files are 100%, one file would be 0.1%. That is, 10 files for each one percent. This is how I had it in mind.

But of course, I no longer know how to tell you if I really have those two values.

The_Doc_Man said:
By now you have probably guessed that there is more to a progress bar than meets the eye. To build and use a good progress bar you need to know some things about the process to be represented. Progress bars are USUALLY based on knowing EXACTLY how many steps there are between the start and the end of the process. In Access it is exceedingly rare to have a process cleanly divisible enough to allow for smooth progress display because smooth progress requires having control of the individual steps of the process.

If you have a VBA-based loop, you have some visibility of how many steps are involved and can compute a percent-complete value within each step of the loop. On the other hand, even if you know how many records are involved in a query, you don't see the "individual steps" of the query for very good technical and theoretical reasons. But if the loop is fast enough, it isn't usually worth it to show a progress bar anyway.

So how long is this taking and have you considered other ways of showing activity?
Click to expand...
That is the problem that I posed. How do I distinguish the phases to put the code to each one?

From what you are both telling me, I think this is too complex. However, I don't know of more ways than these two:

1. Use Application.Echo and a MsgBox to indicate the end, something I don't like because if it gives an error, the screen freezes and I have to close Access from the task manager.

2. Another option would be with an hourglass.

PS: @MajP, I have yet to finish the database and upload how it has finally been, because I have managed to optimize it a lot (for my knowledge), and it can help someone who wants to do the same.
 
Just to reemphasize my post https://www.access-programmers.co.uk/forums/threads/progress-bar.314397/post-1728441
and also what Doc Man said.

Are you in a position where your code can definitely know the total number of iterations prior to the start of the loop? If so, then you are in an easy position to make a nice progress bar. As I mentioned, you'd have a sub like ShowProgress that took (at minimum) two parameters: a boolean to show (or hide), and a percentage (tell it what width the top control should be as a percentage of the width of the bottom control, thus exposing a blue bar or whatever). If you are going to do a progress bar, check out my posts on https://www.access-programmers.co.uk/forums/threads/progress-bar.313403/post-1715411

If you're not in the above described position, I wouldn't attempt a progress bar. Just a modeless display of "please wait, processing.." or something.
 
Isaac said:
If you're not in the above described position, I wouldn't attempt a progress bar. Just a modeless display of "please wait, processing.." or something.
Click to expand...
I think that is going to be the best. What happens, to see if I explain myself, is how to show that during the action. That is, I have this:

Sub Macro ()
MsgBox "Loading ..."
Procedure1
Msgbox "Finished"
End sub

But in socks the MsgBox disappeared first as soon as I gave it to accept.
 
You must log in or register to reply here.

Similar threads

D
Class Factories - Parameterized Object Initialisation
Replies
9
Views
405
dalski
D
M
Showing progress window in a macro
Replies
4
Views
525
Marc Beausejour
M
riktek
Programmatically construct a constant's name and return its value
2
Replies
38
Views
946
riktek
riktek
C
VBA to Conditionally Format a Chart
2 3
Replies
49
Views
3,208
isladogs
isladogs
M
Show a Progress Bar on Form Loading ...
2 3
Replies
43
Views
8,334
Marshall Brooks
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom