Loop through folder and unhide sheets on all files

Zmanda

New Member
Joined
Mar 22, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
You appear to be missing the step where you open the iterated workbook, there should be something like this (completely untested code)

VBA Code:
Workbooks.Open fileName 
For Each WS In ActiveWorkbook.Worksheets
' do something with WS
Next WS
ActiveWorkbook.Close True ' close and save
 
Upvote 0
You appear to be missing the step where you open the iterated workbook, there should be something like this (completely untested code)

VBA 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
 
Upvote 0
Not sure what you mean, can you state the result more clearly?
 
Upvote 0
A common mistake with the Dir function - it returns the file name without the folder path, so you need to prepend the folder path:
VBA Code:
Workbooks.Open "C:\Users\uploadedfiles\" & fileName
 
Upvote 0
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 o_O maybe I need to start again, wasn't expecting such a simple task to be so difficult! Thank you for your help
 
Upvote 0
Does this work?
VBA Code:
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
 
Upvote 0
Solution
Does this work?
VBA Code:
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!
 
Upvote 0
Does this work?
VBA Code:
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!
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

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
Back
Top