How to apply a vba macro to all excel files in a folder?

Question

I would like to delete a worksheet on multiple excel workbooks. I already have some VBA code to delete a worksheet in a single file:

Sub delete_sheet()
Application.DisplayAlerts = False
For Each aSheet In Worksheets
    Select Case aSheet.Name
        Case "INV."
            aSheet.Delete
    End Select

    Next aSheet
    Application.DisplayAlerts = True
End Sub

I have 500 files in my folder all needed to delete a tab named "INV.". How do I apply this operation to all the files within my folder? I have tried the example provided from this website: https://analystcave.com/vba-run-macro-on-all-files-in-a-folder-all-worksheets-in-a-workbook/:

Sub RunOnAllFilesInFolder()
    Dim folderName As String, eApp As Excel.Application, fileName As String
    Dim wb As Workbook, ws As Worksheet, currWs As Worksheet, currWb As Workbook
    Dim fDialog As Object: Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    Set currWb = ActiveWorkbook: Set currWs = ActiveSheet

    'Select folder in which all files are stored
    fDialog.Title = "Select a folder"
    fDialog.InitialFileName = currWb.Path
    If fDialog.Show = -1 Then
      folderName = fDialog.SelectedItems(1)
    End If


    'Create a separate Excel process that is invisibile
    Set eApp = New Excel.Application:  eApp.Visible = False

    'Search for all files in folder [replace *.* with your pattern e.g. *.xlsx]
    fileName = Dir(folderName & "\*.*")
    Do While fileName <> ""
        'Update status bar to indicate progress
        Application.StatusBar = "Processing " & folderName & "\" & fileName

        Set wb = eApp.Workbooks.Open(folderName & "\" & fileName)

'I tried placing my code here
Sub delete_sheet()
Application.DisplayAlerts = False
For Each aSheet In Worksheets
    Select Case aSheet.Name
        Case "INV."
            aSheet.Delete
    End Select

    Next aSheet
    Application.DisplayAlerts = True
End Sub

        wb.Close SaveChanges:=True 'Close opened worbook w/o saving, change as needed
        Debug.Print "Processed " & folderName & "\" & fileName 
        fileName = Dir()
    Loop
    eApp.Quit
    Set eApp = Nothing
    'Clear statusbar and notify of macro completion
    Application.StatusBar = ""
    MsgBox "Completed executing macro on all workbooks"
End Sub

It crashed my excel when I try to execute it. Any suggestions?

and https://stackoverflow.com/q/21031047/62576 – Ken White Oct 30 '19 at 00:29 — Ken White, Oct 30 '19 at 00:29

Tim Williams · Answer 1 · 2019-10-30T06:23:24.560

You can do something like this:

'...
fileName = Dir(folderName & "\*.*")
Do While fileName <> ""
    'Update status bar to indicate progress
    Application.StatusBar = "Processing " & folderName & "\" & fileName

    Set wb = Workbooks.Open(folderName & "\" & fileName)
    Application.DisplayAlerts = False
    On Error Resume Next
    wb.Worksheets("INV.").Delete
    On Error Goto 0
    Application.DisplayAlerts = True
    wb.Close True 'save changes

    fileName = Dir()
Loop

...or if you want to keep your original separate sub:

Sub RunOnAllFilesInFolder
 '...
fileName = Dir(folderName & "\*.*")
Do While fileName <> ""
    'Update status bar to indicate progress
    Application.StatusBar = "Processing " & folderName & "\" & fileName

    Set wb = Workbooks.Open(folderName & "\" & fileName)

    delete_sheet wb, "INV."  '<< call your Sub and pass the workbook & sheet name

    wb.Close True 'save changes

    fileName = Dir()
Loop
'...
End Sub


Sub delete_sheet(wb As Workbook, shtName as String)
    Dim aSheet As Worksheet
    For Each aSheet In wb.Worksheets
        If aSheet.Name = shtName Then
            Application.DisplayAlerts = False
            aSheet.Delete
            Application.DisplayAlerts = True
            Exit For 'done looking...
        End If
    Next aSheet
End Sub

How to apply a vba macro to all excel files in a folder?

1 Answers1