<dl class="userinfo_extra" style="margin: 5px 0px; float: left; width: 180px; height: auto !important;"><dt style="margin: 0px 10px 0px 0px; padding: 0px; float: left; min-width: 60px; width: auto !important;">Join Date</dt><dd style="margin: 0px; padding: 0px 0px 3px;">Apr 2016</dd><dt style="margin: 0px 10px 0px 0px; padding: 0px; float: left; min-width: 60px; width: auto !important;">Posts</dt><dd style="margin: 0px; padding: 0px 0px 3px;">1</dd></dl>
Progress in Status Bar For Macro That Loops Through All Excel Files in a Folder
<ins class="adsbygoogle" data-ad-client="ca-pub-2974935598394359" data-ad-slot="8003857391" data-adsbygoogle-status="done" style="text-decoration: none; display: inline-block; width: 300px; height: 250px;"><ins id="aswift_0_expand" style="text-decoration: none; display: inline-table; border: none; height: 250px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 300px; background-color: transparent;"><ins id="aswift_0_anchor" style="text-decoration: none; display: block; border: none; height: 250px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 300px; background-color: transparent;">******** width="300" height="250" frameborder="0" marginwidth="0" marginheight="0" vspace="0" hspace="0" allowtransparency="true" scrolling="no" allowfullscreen="true" id="aswift_0" name="aswift_0" style="left: 0px; position: absolute; top: 0px;">*********></ins></ins></ins>
<ins class="adsbygoogle" data-ad-client="ca-pub-2974935598394359" data-ad-slot="8003857391" data-adsbygoogle-status="done" style="text-decoration: none; display: inline-block; width: 300px; height: 250px;"><ins id="aswift_0_expand" style="text-decoration: none; display: inline-table; border: none; height: 250px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 300px; background-color: transparent;"><ins id="aswift_0_anchor" style="text-decoration: none; display: block; border: none; height: 250px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 300px; background-color: transparent;">******** width="300" height="250" frameborder="0" marginwidth="0" marginheight="0" vspace="0" hspace="0" allowtransparency="true" scrolling="no" allowfullscreen="true" id="aswift_0" name="aswift_0" style="left: 0px; position: absolute; top: 0px;">*********></ins></ins></ins>
Dear Board,
I have written a code (below) that loops through all Excel files ("wbT") in a folder and performs a few tasks (using a "Main Code").
The "Main Code" is performed on each file in the folder one at a time. In other words, a file is opened, tasks are performed, the file is saved/closed, then the next file is opened and so on, until there are no more files in the folder.
The files in the folder can change quantity depending on if a new file is manually added or an old file is manually removed.
Because the quantity of files is not fixed, a "count" has been added. Its value can be found in "A5" of "Sheet1" of the main workbook ("wbM" houses the code).
I want to know the progress of this macro in the Status Bar. For example, "Processed x of y files. z % complete."
I figure if there are 5 files ("y") in the folder, for example, then every iteration through the "Main Loop" could be 1/5 or 20% (z) progress. If 4 files are complete, then the Status Bar could read, "Processed 4 of 5 files. 80% complete."
I've researched the internet for similar cases, but cannot figure this out.
I believe the solution is related to a "For/Next" loop, but I'm not sure how to write it or where it is to be located within (or around) the "Main Loop."
Any help or guidance is much appreciated!
Sub STATUSBARPROGRESS_LOOPINDEFINITEFILESINFOLDER()
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.DisplayStatusBar = True
'Setup Variables
Dim folderPathP As String
Dim filenameP As String
Dim wbT As Workbook
Dim wbM As ThisWorkbook
Set wbM = Workbooks("MacroStatusBarProgress.xlsm")
'Define Folder
folderPathP = "C:\Users\Daniel\Desktop\Templates\" 'change to suit
If Right(folderPathP, 1) <> "\" Then folderPathP = folderPathP + "\"
'Define Filename
filenameP = Dir(folderPathP & "*.xlsm")
'Count Files for StatusBar Progress
Do While filenameP <> ""
Dim Count As Integer
Count = Count + 1
filenameP = Dir()
Loop
wbM.Sheets("sheet1").Range("A5").Value = Count
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Create Loop for multiple Files
Do While filenameP <> ""
'Open Each File in Folder
Set wbT = Workbooks.Open(folderPathP & filenameP)
'MAIN CODE GOES HERE
'Close File
wbT.Close True
'Complete/End Loop
filenameP = Dir
Loop
End Sub
I have written a code (below) that loops through all Excel files ("wbT") in a folder and performs a few tasks (using a "Main Code").
The "Main Code" is performed on each file in the folder one at a time. In other words, a file is opened, tasks are performed, the file is saved/closed, then the next file is opened and so on, until there are no more files in the folder.
The files in the folder can change quantity depending on if a new file is manually added or an old file is manually removed.
Because the quantity of files is not fixed, a "count" has been added. Its value can be found in "A5" of "Sheet1" of the main workbook ("wbM" houses the code).
I want to know the progress of this macro in the Status Bar. For example, "Processed x of y files. z % complete."
I figure if there are 5 files ("y") in the folder, for example, then every iteration through the "Main Loop" could be 1/5 or 20% (z) progress. If 4 files are complete, then the Status Bar could read, "Processed 4 of 5 files. 80% complete."
I've researched the internet for similar cases, but cannot figure this out.
I believe the solution is related to a "For/Next" loop, but I'm not sure how to write it or where it is to be located within (or around) the "Main Loop."
Any help or guidance is much appreciated!
Sub STATUSBARPROGRESS_LOOPINDEFINITEFILESINFOLDER()
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.DisplayStatusBar = True
'Setup Variables
Dim folderPathP As String
Dim filenameP As String
Dim wbT As Workbook
Dim wbM As ThisWorkbook
Set wbM = Workbooks("MacroStatusBarProgress.xlsm")
'Define Folder
folderPathP = "C:\Users\Daniel\Desktop\Templates\" 'change to suit
If Right(folderPathP, 1) <> "\" Then folderPathP = folderPathP + "\"
'Define Filename
filenameP = Dir(folderPathP & "*.xlsm")
'Count Files for StatusBar Progress
Do While filenameP <> ""
Dim Count As Integer
Count = Count + 1
filenameP = Dir()
Loop
wbM.Sheets("sheet1").Range("A5").Value = Count
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Create Loop for multiple Files
Do While filenameP <> ""
'Open Each File in Folder
Set wbT = Workbooks.Open(folderPathP & filenameP)
'MAIN CODE GOES HERE
'Close File
wbT.Close True
'Complete/End Loop
filenameP = Dir
Loop
End Sub
