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

[spawnjfk]

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)

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

 

[John_w]

Welcome to MrExcel forums.

See if you can adapt the code at

Macro to Update a common value in all excel files in a folder including sub folders

Hi Friends, I need to update a common value i.e. "123456" in Cell H1 of Worksheet name "Index" of all excel files which are stored in a folder. The folder also has sub folders. I tried using below macro, however it is not updating files in folder and sub folder Sub Example() Dim MyPath...

www.mrexcel.com

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]

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]

Try this:

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]

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

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]

That's exactly what my code does here:

            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?