Loop through folder and unhide sheets on all files

[Zmanda]

Hello!
I am trying to write a macro that will loop through all files in a folder, unhide all tabs, and then save and close - i expected this to be simple but i am struggling to find anything that i can get to run! I'm not sure if the files being downloaded from sharepoint is making a difference and so i have tried putting in a line to remove the protected view but nothing seems to work. I have tried lots of examples from various places (not enough experience to write from scratch yet!) this is the latest code....any ideas?

Sub LoopAllFilesInAFolder()

'Loop through all files in a folder
Dim fileName As Variant
Dim WS As Worksheet
fileName = Dir("C:\Users\uploadedfiles\*.xlsm")
WS.ActiveProtectedViewWindow.Edit

While fileName <> ""

'Insert the actions to be performed on each file
'This example will print the file name to the immediate window
For Each WS In Worksheets
WS.Visible = True
Next

'Set the fileName to the next file
fileName = Dir
Wend

End Sub

 

[Celly]

You appear to be missing the step where you open the iterated workbook, there should be something like this (completely untested code)

Workbooks.Open fileName 
For Each WS In ActiveWorkbook.Worksheets
' do something with WS
Next WS
ActiveWorkbook.Close True ' close and save

 

[Zmanda]

You appear to be missing the step where you open the iterated workbook, there should be something like this (completely untested code)

Workbooks.Open fileName
For Each WS In ActiveWorkbook.Worksheets
' do something with WS
Next WS
ActiveWorkbook.Close True ' close and save

Hi Celly, thank you for your response!
That definitely helps, it is finding the first file in the folder - it just now says the file doesn't exist! Am i missing something from the 'fileName = Dir' path? I've tried different file type suffix and it just does the same thing

 

[Celly]

Not sure what you mean, can you state the result more clearly?

 

[John_w]

A common mistake with the Dir function - it returns the file name without the folder path, so you need to prepend the folder path:

Workbooks.Open "C:\Users\uploadedfiles\" & fileName

 

[Zmanda]

Thank you both, it is progressing - it seems to open the file now but immediately closes it, I moved the steps around a bit but now it's just saving blank files maybe I need to start again, wasn't expecting such a simple task to be so difficult! Thank you for your help

 

[John_w]

Does this work?

Public Sub Unhide_Sheets_in_Workbooks()

    Dim matchFiles As String
    Dim folderPath As String
    Dim fileName As String
    Dim wb As Workbook, ws As Worksheet
    
    matchFiles = "C:\Users\uploadedfiles\*.xlsm"
    
    folderPath = Left(matchFiles, InStrRev(matchFiles, "\"))
    fileName = Dir(matchFiles)
    Do While fileName <> vbNullString
        Set wb = Workbooks.Open(folderPath & fileName)
        For Each ws In wb.Worksheets
            ws.Visible = xlSheetVisible
        Next
        wb.Close True
        fileName = Dir
    Loop
    
    MsgBox "Finished"
    
End Sub

 

[Zmanda]

Does this work?

Public Sub Unhide_Sheets_in_Workbooks()

    Dim matchFiles As String
    Dim folderPath As String
    Dim fileName As String
    Dim wb As Workbook, ws As Worksheet
   
    matchFiles = "C:\Users\uploadedfiles\*.xlsm"
   
    folderPath = Left(matchFiles, InStrRev(matchFiles, "\"))
    fileName = Dir(matchFiles)
    Do While fileName <> vbNullString
        Set wb = Workbooks.Open(folderPath & fileName)
        For Each ws In wb.Worksheets
            ws.Visible = xlSheetVisible
        Next
        wb.Close True
        fileName = Dir
    Loop
   
    MsgBox "Finished"
   
End Sub

YES IT DOES!! THANK YOU THANK YOU! I am now going to study this, I clearly have a lot to learn, maybe I need something more formal than google university!

 

[Oppenheimer]

Does this work?

Public Sub Unhide_Sheets_in_Workbooks()

    Dim matchFiles As String
    Dim folderPath As String
    Dim fileName As String
    Dim wb As Workbook, ws As Worksheet
   
    matchFiles = "C:\Users\uploadedfiles\*.xlsm"
   
    folderPath = Left(matchFiles, InStrRev(matchFiles, "\"))
    fileName = Dir(matchFiles)
    Do While fileName <> vbNullString
        Set wb = Workbooks.Open(folderPath & fileName)
        For Each ws In wb.Worksheets
            ws.Visible = xlSheetVisible
        Next
        wb.Close True
        fileName = Dir
    Loop
   
    MsgBox "Finished"
   
End Sub

Although an older thread, this absolutely saved me literally hours of tedious work-Bravo Zulu!