Progress Status in a Loop Macro

[Tarkin]

OK guys, here is what I am looking for. The code below works fine and I am happy with it. The macro simply opens each file in a directory and runs a basic routine. Each file is then closed and a time stamp of the routine for each file is posted to the workbook when the macro is running from.

What I need is a very simple progress status which typically displays the number of files being processed and the progress so far. I am not concerned if it is via Status bar, Progress bar or displayed on the master workbook.

Any help would be much appreciated! Also, any tweaks to make the code run faster would be also welcome

Sub LoopWIP()
Application.ScreenUpdating = False
Dim Fname As String
Dim Pth As String
Dim Wbk As Workbook
Dim i As Integer

Pth = "C:\My Files\"
Fname = Dir(Pth)
'Initialise Time Stamp
Cells(1, 1) = "Refreshed started @" & Date + Time

Do While Len(Fname) > 0
      Set Wbk = Workbooks.Open(Pth & Fname)
     'Run code on each file

Range("A1").Select
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "Freddy"

'Enter each file name and time of refresh on master workbook

ThisWorkbook.Activate

 
Cells(i + 4, 1) = Fname & " - Refreshed"
Cells(i + 4, 2).Value = Date + Time


i = i + 1

Wbk.Close True
    Fname = Dir


Loop
'Close Time stamp
     Cells(2, 1) = "Refreshed finished @" & Date + Time
End Sub

 

[mse330]

Hey Tarkin,

You can use Application.StatusBar like I have embed in your code below

Sub LoopWIP()

Application.ScreenUpdating = False
Dim Fname As String, Pth As String, Wbk As Workbook, i As Integer

Pth = "C:\My Files\"
Fname = Dir(Pth)

'Initialise Time Stamp
Cells(1, 1) = "Refreshed started @" & Date + Time

Do While Len(Fname) > 0
    Set Wbk = Workbooks.Open(Pth & Fname)
  
    'Run code on each file
    Range("A1").Font.Bold = True
    ActiveCell.FormulaR1C1 = "Freddy"
    Wbk.Close True

    'Enter each file name and time of refresh on master workbook
    Cells(i + 4, 1) = Fname & " - Refreshed"
    Cells(i + 4, 2).Value = Now
    i = i + 1
    Fname = Dir
    Application.StatusBar = i & " files completed"
Loop

'Close Time stamp
Cells(2, 1) = "Refreshed finished @" & Now
Application.StatusBar = ""
   
End Sub

 

[Tarkin]

Fantastic...Many thanks! Much appreciated!

 

[mse330]

Glad to help … In VBA, you don't need to select the cell/range to do an action as I have changed it in your code but just noticed a small issue that might does something wronge

Range("A1").Font.Bold = True    

'You were selecting cell A1 in your initial code but I changed the above line so you should change the below
' line accordingly as the ActiveCell may not be A1 anymore
ActiveCell.FormulaR1C1 = "Freddy"  '<--- This should be Range("A1") = "Freddy"

'