Count files in folder

[Rudi]

Hi,

I have the following code:

Code: Select all

   Dim objFSO As Object, objFolder As Object, objFile As Object
   Dim myFolder As Object, strFolderPath As String
   Dim i As Integer

   Set myFolder = Application.FileDialog(msoFileDialogFolderPicker)
   With myFolder
      .Title = "Choose Folder"
      .AllowMultiSelect = False
      If .Show <> -1 Then Exit Sub
      strFolderPath = .SelectedItems(1)
   End With

   Set objFSO = CreateObject("Scripting.FileSystemObject")

   'Get the folder object associated with the directory
   Set objFolder = objFSO.GetFolder(strFolderPath & "\")

   Application.ScreenUpdating = False

   'Loop through the Files collection
   For Each objFile In objFolder.Files
'....

I would like to have a progress indication in the status bar to indicate: "Processing workbook? of so many". How do I count the amount of files in a folder so I can put this in the statusbar. TX

[HansV]

Like this (assuming you're working in Excel):

Code: Select all

Sub ProcessFiles()
    Dim objFSO As Object, objFolder As Object, objFile As Object
    Dim myFolder As Object, strFolderPath As String
    Dim i As Long, n As Long

    Set myFolder = Application.FileDialog(msoFileDialogFolderPicker)
    With myFolder
        .Title = "Choose Folder"
        If Not .Show Then Exit Sub
        strFolderPath = .SelectedItems(1)
    End With

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    'Get the folder object associated with the directory
    Set objFolder = objFSO.GetFolder(strFolderPath)
    n = objFolder.Files.Count

    Application.ScreenUpdating = False

    'Loop through the Files collection
    For Each objFile In objFolder.Files
        i = i + 1
         Application.StatusBar = "Processing file " & i & " of " & n
         ' your code here
         Debug.Print objFile.Name
     Next objFile

        Application.ScreenUpdating = True
        Application.StatusBar = False
End Sub

[Rudi]

TX. Yes I am in Excel, sorry...

Its working great

One question: I see you use debug.print to update the status bar. In similar macros in the past (and because of application.screenupdating=false), I was having to force the statusbar to update by using Application.EnableEvents =True. Is debug.print better, or less resource intensive? (Besides, I thought this was only used with the immediate window). Please clarify. Many TX.

[HansV]

I used Debug.Print only as an example of doing something with the file. You should replace it with code to process the file.
The line that updates the status bar is

Code: Select all

      If objFile.Name = ThisWorkbook.Name Then GoTo nxtLoop   '"~$Template_Combine.xlsm"

This should work even if Application.ScreenUpdating = False (although Excel tends to stop updating the status bar after a while if you're processing a large number of files)

[Rudi]

The line that updates the status bar is

Code: Select all

      If objFile.Name = ThisWorkbook.Name Then GoTo nxtLoop   '"~$Template_Combine.xlsm"

You mean: Application.StatusBar = "Processing file " & wb & " of " & n

Code: Select all

   For Each objFile In objFolder.Files
      wb = wb + 1
      Application.StatusBar = "Processing file " & wb & " of " & n
      If objFile.Name = ThisWorkbook.Name Or Left(objFile.Name, 2) = "~$" Then GoTo nxtLoop  '"~$Template_Combine.xlsm"
      Workbooks.Open objFile.Path

I removed the debug.print and it is working fine over the 15 +/- files I have so far. TX. So to get it right, you used it only for your purposes. It has no role in my code?

[HansV]

Huh? Apparently Ctrl+C didn't work and the previous contents of the clipboard were pasted...

The Debug.Print line was purely an example, you don't need it.

[Rudi]

Much obliged for the assistance.
Have a GREAT day further.

[Don Wells]

Hi Rudi
    It has been my perception that the status bar is often overlooked when the user suspects that the program has hung.
     For something a little more attention demanding you might wish to consider the attached example which Hans was good enough to provide some years ago.

H.T.H.

PROGIND.XLS

[Rudi]

Thanks for digging this up Don.

I do agree that the progress on a status bar is a little "out of sight". The current macro is not going to be so big that it needs a bit more visibility progress wise, however, I am going to incorporate this into a macro i created about 2 weeks ago that runs on hundreds of records. I have a status bar setup there too, but this will definitely be more user friendly. Tx for this...

And thanks to Hans for the original code....