I am trying to loop through a directory, "U:\bsides\Quotes\BUYERS GUIDE\", and as it stands, I have to specify the subfolder and since there are 5 that contain "*.xlsx" file extensions, I have to this long list of code, 5X as long as it needs to be, referencing each different subfolder as the path. I have essentially just copy and pasted the same code 5 times, changing the path for each. In the future, there may be more than 5 subfolders, so I was wondering what do I need to add to include the search of subfolders.
Here is the code:
Sub DirLoop()
'Setting up variables
Const sPathC As String = "U:\bsides\Quotes\BUYERS GUIDE\Contactors-Regens\"
Const sPathF As String = "U:\bsides\Quotes\BUYERS GUIDE\Filter Separators\"
Const sPathH As String = "U:\bsides\Quotes\BUYERS GUIDE\Heaters\"
Const sPathS As String = "U:\bsides\Quotes\BUYERS GUIDE\Separators\"
Const sPathT As String = "U:\bsides\Quotes\BUYERS GUIDE\Treaters\"
Dim wkb As Workbook
Dim sFile As String
Dim CountC As Integer
Dim CountF As Integer
Dim CountH As Integer
Dim CountS As Integer
Dim CountT As Integer
Dim rngFind As Range, firstAddress As String
Dim n As Integer - For that random formatting
Dim sCellmat As String - For that random formatting
Dim sCelllab As String - For that random formatting
Dim sCellacc As String - For that random formatting
'Disables Alerts
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
'Set up path and directory
sFile = Dir(sPathC & "*.xlsx")
'Set up Count
CountC = 0
'Starts the loop
Do While sFile <> ""
Set wkb = Workbooks.Open(sPathC & sFile)
Bunch of random formatting and search code
'Change sheet name to default
ActiveSheet.Name = "MasterReference"
'Save and close workbook
wkb.Close SaveChanges:=True
'Adding to count
CountC = CountC + 1
sFile = Dir()
Loop
'Set up path and directory
sFile = Dir(sPathF & "*.xlsx")
'Set up Count
CountF = 0
'Starts the loop
Do While sFile <> ""
Set wkb = Workbooks.Open(sPathF & sFile)
Bunch of random formatting and search code
'Change sheet name to default
ActiveSheet.Name = "MasterReference"
'Save and close workbook
wkb.Close SaveChanges:=True
'Adding to count
CountF = CountF + 1
sFile = Dir()
Loop
'Set up path and directory
sFile = Dir(sPathH & "*.xlsx")
'Set up Count
CountH = 0
'Starts the loop
Do While sFile <> ""
Set wkb = Workbooks.Open(sPathH & sFile)
Bunch of random formatting and search code
'Change sheet name to default
ActiveSheet.Name = "MasterReference"
'Save and close workbook
wkb.Close SaveChanges:=True
'Adding to count
CountH = CountH + 1
sFile = Dir()
Loop
'Set up path and directory
sFile = Dir(sPathS & "*.xlsx")
'Set up Count
CountS = 0
'Starts the loop
Do While sFile <> ""
Set wkb = Workbooks.Open(sPathS & sFile)
Bunch of random formatting and search code
'Change sheet name to default
ActiveSheet.Name = "MasterReference"
'Save and close workbook
wkb.Close SaveChanges:=True
'Adding to count
CountS = CountS + 1
sFile = Dir()
Loop
'Set up path and directory
sFile = Dir(sPathT & "*.xlsx")
'Set up Count
CountT = 0
'Starts the loop
Do While sFile <> ""
Set wkb = Workbooks.Open(sPathT & sFile)
Bunch of random formatting and search code
'Change sheet name to default
ActiveSheet.Name = "MasterReference"
'Save and close workbook
wkb.Close SaveChanges:=True
'Adding to count
CountT = CountT + 1
sFile = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox "FillIn is complete." & vbCrLf & CountC & " Contactor files processed." _
& vbCrLf & CountF & " Filter Separator files processed." _
& vbCrLf & CountH & " Heater files processed." _
& vbCrLf & CountS & " Separator files processed." _
& vbCrLf & CountT & " Treater files processed.", _
vbInformation + vbOKOnly + vbMsgBoxHelpButton, "Execution of FillIn"
Count = 0
End Sub
This works fine for now, just if I add subfolders, I have to copy and paste more code, and I figured there is probably an easier way to do this.
Bookmarks