VBA to run through multiple files in a folder and perform an action

[amitojsd]

Hi everyone, I am trying to write a VBA code that runs through a directory of excel files of the same format, that unhides and unlinks all the sheets.
I was able to make the unhide and unlink portion work, but running it through the directory is where I am getting stuck.

I open the first file in the directory, try to run the macro and excel shuts down. I have 90p files in the directory, and each file is about 2MB

Here is the code I have written till now -


Sub DirectoryFileLoopUnhideAndUnlink()

Dim fileDirectory As String

Dim fileCriteria As String

Dim fileName As String

Dim fileToOpen As Workbook


Application.ScreenUpdating = False


fileDirectory = "C:\Users\Downloads\TEST Loop\"

fileName = Dir(fileDirectory)

Do While Len(fileName) > 0


For Each Sheet In Sheets

Sheet.Visible = True

Next Sheet

Sheets("ABBrand").Select

Range("D17").Select

ActiveSheet.ListObjects("Brand").Unlink

Sheets("ABItem").Select

Range("J11").Select

ActiveSheet.ListObjects("Item").Unlink


Loop

Application.ScreenUpdating = True

End Sub


Can you please let me know what I am doing wrong?

 

[Logit]

Untested here. Let me know if it works for you.

Sub DirectoryFileLoopUnhideAndUnlink()

    Dim fileDirectory As String
    Dim fileName As String
    Dim fileToOpen As Workbook
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' Define the directory path
    fileDirectory = "C:\Users\Downloads\TEST Loop\"
    fileName = Dir(fileDirectory & "*.*") ' Adjusted to pick up any file type
    
    ' Loop through all files in the directory
    Do While fileName <> ""
        ' Open the workbook
        On Error Resume Next
        Set fileToOpen = Workbooks.Open(fileDirectory & fileName)
        On Error GoTo 0

        If Not fileToOpen Is Nothing Then
            ' Unhide all sheets
            For Each ws In fileToOpen.Sheets
                ws.Visible = True
            Next ws

            ' Perform actions on specific sheets
            On Error Resume Next
            With fileToOpen
                .Sheets("ABBrand").Activate
                .Sheets("ABBrand").ListObjects("Brand").Unlink
                
                .Sheets("ABItem").Activate
                .Sheets("ABItem").ListObjects("Item").Unlink
            End With
            On Error GoTo 0
            
            ' Save and close the workbook
            fileToOpen.Save
            fileToOpen.Close
        End If
        
        ' Move to the next file
        fileName = Dir
    Loop

    ' Restore settings
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    MsgBox "All files have been processed.", vbInformation
End Sub

 

[amitojsd]

It actually worked perfectly! Thank you so much!

 

[Logit]

Great ! You are welcome.