Progress Bar

[MelG]

Hi, the code below is to import file names into excel from any given folder. I'd like to have a progress bar but I'm not sure how it would work. I thought if I could work out how many file names are going to be imported before the Macro does it's work, I could use it to do a progress bar showing percentage complete as every file name and path is loaded into the array. Any ideas? The code was not written by me so please go back to basics when replying.

Option Explicit
Dim cnt As Long

Sub ListFiles()
Dim objFSO As FileSystemObject
Dim MyPath As String
Dim MyArray() As String

cnt = 0

Set objFSO = CreateObject("Scripting.FileSystemObject")

Sheets.Add

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Select a Folder"
.Show
'Call UpdateProgressIndicator
If .SelectedItems.Count > 0 Then
MyPath = .SelectedItems(1)
Call ProcessFolders(objFSO, MyPath, MyArray)
Else
Exit Sub
End If
End With
Cells.Clear
If cnt > 0 Then
Range("A1:B1").Value = Array("File Path", "File Name")
Range("A2").Resize(UBound(MyArray, 2), UBound(MyArray, 1)).Value = WorksheetFunction.Transpose(MyArray)
Else
MsgBox "No files were found...", vbExclamation

End If


End Sub

Sub ProcessFolders(ByRef f, ByVal p, ByRef arr)
Dim objFolder As Folder
Dim objSubFolder As Folder
Dim objFile As File
Set objFolder = f.GetFolder(p)
For Each objFile In objFolder.Files
cnt = cnt + 1
ReDim Preserve arr(1 To 2, 1 To cnt)
arr(1, cnt) = objFolder.path
arr(2, cnt) = objFile.Name
Next objFile
For Each objSubFolder In objFolder.SubFolders
Call ProcessFolders(f, objSubFolder, arr)
Next objSubFolder
End Sub

 

[MelG]

I've now created my own progress bar by following the link from John W. The form loads but nothing happens (stops at UserForm1.show). I close the form and the code continues and when it comes back, the form loads and stops in the same place but this time, it will display the % but the LabelProgress will not have moved. So Basically I have to manually close the form each time it gets stuck at UserForm1.show and after the percentage has updated untill it reaches 100% and the macro is complete. The red bar never moves. I've checked the background colour. I don't get any error messages. I've also taken out the code in blue and the macro runs fine without the progress bar. It would'nt bother me so much, but the macro will be used by others and I know it can take quite a while, so really need to show progress bar and/or %.


Sub ProcessFolders(ByRef f, ByVal p, ByRef arr)
Dim objFolder As Folder
Dim objSubFolder As Folder
Dim objFile As File
Set objFolder = f.GetFolder(p)
Dim Percentage As Long


For Each objFile In objFolder.Files
cnt = cnt + 1

Percentage = CLng((cnt / Filestotal) * 100)

UserForm1.LabelProgress.Width = 0
UserForm1.Show

With UserForm1
.FrameProgress.Caption = Str(Percentage) & "%"
.LabelProgress.Width = Percentage * 4.62 'LabelProgress has width of 462
End With
DoEvents

ReDim Preserve arr(1 To 2, 1 To cnt)
arr(1, cnt) = objFolder.path
arr(2, cnt) = objFile.Name

Next objFile
Unload UserForm1
For Each objSubFolder In objFolder.SubFolders
Call ProcessFolders(f, objSubFolder, arr)
Next objSubFolder

End Sub

 

[Johnny C]

May or may not work... try
Load Userform1
before the .width or .Show statements

Also, this is not necessary but good practice, move the
UserForm1.LabelProgress.Width = 0
to 'inside' the userform, i.e. double click on the userform in the editor window, create a 'userform1_Initialize' sub and put the .width code there.

 

[MelG]

The loaduserform1 does not appear to do anything with or without the userform1.show . Moving the labelprogress width has helped. The red bar is now visible and increments in line with the % displayed everytime I manually close the form.

 

[Johnny C]

Try

[COLOR=black]With UserForm1[/COLOR]
[COLOR=black].FrameProgress.Caption = Format(cnt / Filestotal ,"0%")[/COLOR]
[COLOR=black].LabelProgress.Width = CInt((cnt / Filestotal * 462)    'LabelProgress has width of 462[/COLOR]
[COLOR=black].Repaint[/COLOR]
[COLOR=black]End With[/COLOR]

 

[MelG]

Sorry, no change. I'm gona finish work now and sleep on it.

 

[MelG]

Thought you'd be intrested to know I've sorted it. It's to do with something called modal where the default value is set to true (VBModal) when a form is displayed. This stops the user doing anything else in excel and also stops code running until the form is closed. To change it to false all you to do is add the line VBModeless.

Form1.show vbModless

 

[Johnny C]

I understand now.

vbmodeless isn't meant for that particularly, it hadn't occurred to me to use it that way. I will explain.

Normally when you use a form, your macro calls loads/shows the form. You are correct now in that unless the form is modeless, no code is executed until the form is closed down.

However, the normal way to do it is the you build the code into the form. Just open up the form, double click, and the VBA editor opens up. This gives you access to all the form event controls, which means you control for example a cancel button, any input fields or user controls such as combo/list boxes. So you'll have a module that is apparently hidden within the userform, which has all the code in that deals with the form events/control.

You can call public sub/functions outside the userform module, which is what I do; call a sub which 'shows' the form (in default modal state). The form then has a single sub attached to the 'activate' event.

Private Sub UserForm_Activate()

This then calls another sub in a separate module, which means I don't need to double click the userform each time I want to edit it, I can edit it in a module in the normal way. Here's an example.

A button on a worksheet calls the following macro:

Sub ValidateDataFromEachFile()
    Load QMSValidationProgressForm
    QMSValidationProgressForm.Show
    Unload QMSValidationProgressForm
End Sub

'Inside' the form is the following (i.e. when I dbl click on the form this is what appears in a code window):

Private Sub UserForm_Activate()
    Call ValidateQueriesNormal
    Me.Hide
End Sub

(The Me in Me.Hide is a way of a userform referencing itself, you can use the userform name but Me is safer in case you later on change the userform name)
The 'activate' event is triggered when the '.show' command is run above, so the code in the userform_activate module runs automatically.


Then the ValidateQueriesNormal sub is in a separate module. In this case it's processing hundreds of files each with thousands of rows so there are multiple 'running totals' and two progress bars (the macro can take several hours to complete). The multiple progress bars/counters are just updated with the code that you have from the MS site and others and a lot of 'DoEvent' commands.

The MS site and I both assumed you realised this - sorry!

Note on DoEvents: it's preferable to repaint. DoEvents is something you should put at the end of loops where lots of processing has gone on. The reason is that these days XL2007 uses multithreading, so if you have a dual core PC it will give tasks to each CPU core. When you have several large spreadsheets open that have lots of calculations in, and you've worked on them and then save or close one, XL can get it's knickers in a twist as the bits of processing assigned to different CPUS get out of sync. A DoEvents forces all processing to get uptodate before continuing.

I had a macro processig lots of files with some chunky countifs workings on thousands of rows. I never figured out what exactly was going on, but I worked on the sheets, the countif's were recalculating and then the sheet was saved and shut down. Without the DoEvents, at some stage a different sheet in the workbook with all the code in got totally cleared, every cell. This sheet was never activated, there was no cells.select of clear all command in the code, so there was no reason for this to happen; in fact I'd say it was impossible. But it happened. I realised it might be a syncing issue, sprinkled DoEvents liberallly around and it's never happened since.

 

[nigelh]

JohnnyC

Have followed this thread with interest: I, too, made it work using Modeless. I can understand the point about calling the code from within the form, but the problem I have is that I am reporting on a process that involves uploading / retrieval from an Oracle database, and so make all of the connections prior to invoking the retrieval. I cannot seem to pass in ADODB command and parameters as properties, and so I would appear to have to have the retrieval in the main macro, and then call the progress form as I go through the retrieval loop. (It may help to explain that all of the retrieval code was written first: the need to add the progress indicator became apparent once the speed of the connection was understood to be worse than anticipated!)

Is there a way around this dilemma?

Thanks
nigelh

 

[Johnny C]

Yes Nigel.

Firstly, define your adodb.connection/command variables as public. You can set them up in code before the form, they will still be active and available when the userform runs.

When you use public vars though, make sure you use Option explicit at the top (to enforce variable type declaration) just to make sure you know what type your variables are in different modules/useforms.

If you want to manipulate command strings/variable within the form, wwhat you could do is have those strings/commands as values in cells in a hidden sheet. You can make that sheet VeryHidden using VBA so other users can't unhide it. (do a search on sheet.visible if you don't know what I mean). Anyway, once you've got those strings/values in cells, as part of the userform code, you can still acccess those cells (even though the sheet is veryhidden). presumably you have a loop (or you wouldn't be using a progress bar) so you can loop down the cells on a sheet with values to use as parameters for your retrieves.

it does work, I just had a look and I wrote a spreadhseet a few years back to pull a lot of data from Access using queries in a loop which looked at cells in a range, that was in a progress bar form. The initial connections were made outside the userform, then the userform was called and the retrieve string was built dynamically in the userform VBA.

There is another option, there's always the ActiveX progress bar which doesn't need a form at all. It doees however need ActiveX controls enabling so whoever uses the spreadsheet needs to either permanently enable ActiveX's or remember to enable them each time the sheet is opened. It's obvious in Excel2003 as a dialog asks you but it's a pain in XL2007+ as it's just a small bar at the top which people tend not to notice.

Remember, the question with Excel VBA isn't usually 'Can you' it's 'what's the best way to' )