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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Celly

Board Regular
Joined
Jan 29, 2015
Messages
81
Office Version
  1. 2016
Platform
  1. Windows
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
 

Zmanda

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

Celly

Board Regular
Joined
Jan 29, 2015
Messages
81
Office Version
  1. 2016
Platform
  1. Windows
Not sure what you mean, can you state the result more clearly?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751

ADVERTISEMENT

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
 

Zmanda

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

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751
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
 
Solution

Zmanda

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

Watch MrExcel Video

Forum statistics

Threads
1,129,490
Messages
5,636,630
Members
416,932
Latest member
mm07

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
Top