Complete code to Execute Macro in all Excel files in folders and subfolders

spawnjfk

New Member
Joined
Feb 11, 2020
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
Hello,
is it possible to complete this code for MS Excel 2013, so when macro runs, it actually asks for folder, but when selected it would be good that when looping through main folder (that was chosen) it loops in all folders and subfolders and execute simple macro (that is in the code already)

VBA Code:
Sub LoopThroughFiles()
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xls*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
                'your code here
                    Columns("B:B").Select
    Selection.EntireColumn.Hidden = True
    Rows("1:1").Select
    Range("C1").Activate
    Selection.EntireRow.Hidden = True
    Range("C14").Select
    ActiveWorkbook.Save
            End With
            xFileName = Dir
        Loop
    End If
End Sub

Just tell me where to put what (or edit the code and embedd it) so it works. Waiting for any ideas. Thanks in advance :)
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,608
Welcome to MrExcel forums.

See if you can adapt the code at


for your situation.

The main procedure was later modified in post #4 to allow the user to pick the starting folder using Application.FileDialog(msoFileDialogFolderPicker), as in your code.
 

spawnjfk

New Member
Joined
Feb 11, 2020
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
Unfortunately, i am not able to adapt the code since i have called several other strings, so if i add something it would prolong the code.
If any of devs present on forum could tell me / by editing my code, and copy paste edited code here, i would be very gratefull.

Thanks
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,608
Try this:
VBA Code:
Public Sub Change_All_Workbooks_In_Folders_LB()
    
    Dim mainFolder As String
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select the main folder"
        If Not .Show Then Exit Sub
        mainFolder = .SelectedItems(1)
    End With
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Change_Workbooks_In_Folder mainFolder
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    MsgBox "Done"
    
End Sub


Private Sub Change_Workbooks_In_Folder(folderPath As String)
   
    Static FSO As Object
    Dim Folder As Object, Subfolder As Object, File As Object
    Dim wb As Workbook
    
    If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
    
    'Process files in this folder
    
    Set Folder = FSO.GetFolder(folderPath)
    
    For Each File In Folder.Files
        If File.Name Like "*.xls*" Then
            Set wb = Workbooks.Open(File.Path)
            With wb.ActiveSheet
                .Columns("B").EntireColumn.Hidden = True
                .Rows("1").EntireRow.Hidden = True
            End With
            wb.Close SaveChanges:=True
        End If
    Next
    
    'Process files in subfolders
    
    For Each Subfolder In Folder.SubFolders
        Change_Workbooks_In_Folder Subfolder.Path
    Next

End Sub
 

spawnjfk

New Member
Joined
Feb 11, 2020
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
Unfortunately, i would like to reuse my first code, but my macro has to use another macro code inside itself, as you can see from the code.
So, basicly when macro is running, it chooses folder and continues searching through subfolders also, and it executes macro that is inside the code.

I have created a macro that goes only for one folder and executes
VBA Code:
Columns("B:B").Select
    Selection.EntireColumn.Hidden = True
    Rows("1:1").Select
    Range("C1").Activate
    Selection.EntireRow.Hidden = True
    Range("C14").Select
    ActiveWorkbook.Save
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,608
That's exactly what my code does here:
VBA Code:
            With wb.ActiveSheet
                .Columns("B").EntireColumn.Hidden = True
                .Rows("1").EntireRow.Hidden = True
            End With
but without the Selects and Activates, which are unnecessary.

Have you run my macro?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,437
Messages
5,596,113
Members
414,043
Latest member
thomas Stein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top