Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Option Explicit
- Sub DoSomething(strFile As String)
- Dim wb As Workbook
- Dim myPath As String
- Dim myFile As String
- Dim myExtension As String
- Dim FldrPicker As FileDialog
- 'Optimize Macro Speed
- Application.ScreenUpdating = False
- Application.EnableEvents = False
- Application.Calculation = xlCalculationManual
- Set wb = Workbooks.Open(strFile)
- 'Ensure Workbook has opened before moving on to next line of code
- DoEvents
- 'Change First Worksheet's Background Fill Blue
- Application.Calculate
- ActiveWorkbook.RefreshAll
- Application.Wait (Now + TimeValue("0:00:10"))
- 'Save and Close Workbook
- wb.Close SaveChanges:=True
- 'Reset Macro Optimization Settings
- Application.EnableEvents = True
- Application.Calculation = xlCalculationAutomatic
- Application.ScreenUpdating = True
- End Sub
- Function GetFiles(ByVal Folder As String) As Collection
- Dim strFile As String
- Set GetFiles = New Collection
- strFile = Dir(Folder & "\*")
- Do While strFile <> ""
- GetFiles.Add strFile
- strFile = Dir
- Loop
- End Function
- Function GetFolders(ByVal Folder As String) As Collection
- Dim strFile As String
- Set GetFolders = New Collection
- strFile = Dir(Folder & "\*", vbDirectory)
- Do While strFile <> ""
- If GetAttr(Folder & "\" & strFile) And vbDirectory Then GetFolders.Add strFile
- strFile = Dir
- Loop
- End Function
- Sub LoopThroughSubfoldersAsWell()
- Dim colFoFi As Collection
- Dim varEl01 As Variant
- Dim varEl02 As Variant
- Dim varEl03 As Variant
- Dim strLine As String: strLine = "--------------------------"
- Dim strAddress As String: strAddress = "C:\Users\UserName\Desktop\Testing01\"
- Debug.Print strAddress
- Set colFoFi = GetFiles(strAddress)
- For Each varEl01 In colFoFi
- Debug.Print varEl01
- Next varEl01
- Debug.Print strLine
- Set colFoFi = GetFolders(strAddress)
- For Each varEl01 In colFoFi
- If Len(varEl01) > 2 Then 'to avoid some hidden stuff
- Set varEl02 = GetFiles(strAddress & varEl01)
- Debug.Print (strAddress & varEl01)
- For Each varEl03 In varEl02
- Debug.Print varEl03
- DoSomething (varEl03)
- Next varEl03
- Debug.Print strLine
- End If
- Next varEl01
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement