Open workbooks in folder and run Macro in each book

[glynn1969]

Hello grateful for any help here.

I have a folder ("Workbooks") which contains 5 xlsm files (this number will increase to about 62).
i have a second folder called "Master" in which i have a workbook called "Master Macro"

I have tried to create a macro in the "Master Macro" workbook which will open each file in the "Workbooks" folder then run a macro which is contained in each workbook called "refreshWS" (although refresWS is in all workbooks it is bespoke to each workbook itself so i can't have this outside of each workbook) and then close the workbook and save it - then move onto the next workbook.

However it is falling over because i am trying to run the macro in the 5 workbooks and it is saying that the macros are not enabled in the workbook- how would i get past this.

My only other thought was to open all the workbooks in one phase one of a macro and so enabling macros in each book and then once all opened run a second macro to select each open workbook and run the required "refreshWS" macro and close and save - then move onto the next open workbook (but i would somehow need to omit my "Master Macro" workbook.

Any help from you wizards with either solution would be much appreciated.

Copy of my macro below:-

Sub OpenAndRunMacroInAllWorkbooksIncludingNew()
Dim folderPath As String
Dim fileName As String
Dim wb As Workbook

' Specify the folder path where your workbooks are located
folderPath = "C:\Users\1\Workbooks\"

' Get the first file name in the folder
fileName = Dir(folderPath & "*.xls*")

' Loop through all files in the folder
Do While fileName <> ""
' Open the workbook
Set wb = Workbooks.Open(folderPath & fileName)


' Run your macro (replace "YourMacroName" with the actual macro name)
Application.Run wb.Name & "!refreshWS"

' Close the workbook (you can choose to save changes or not)
'wb.Close SaveChanges:=True

' Get the next file name
fileName = Dir
Loop

End Sub

 

[rlv01]

However it is falling over because i am trying to run the macro in the 5 workbooks and it is saying that the macros are not enabled in the workbook- how would i get past this.

Try making each of the 5 workbooks a 'Trusted Document'

Microsoft Office Trusted Documents and Trusted Locations

Exploring how we can manage Microsoft Office Trusted Documents and Trusted Location both manually and through automation.

www.devhut.net

Add, remove, or change a trusted location in Microsoft Office - Microsoft Support

A trusted location is a folder that contains files you're confident are safe. See how to add, remove, or change a trusted location in Microsoft Office.

support.microsoft.com

Trusted documents - Microsoft Support

Learn what trusted documents are and how to change the options.

support.microsoft.com

How to enable or disable Trusted Documents in Word, Excel, PowerPoint

You can enable or disable Trusted Documents in Word, Excel, PowerPoint using Office settings, Registry or Group Policy Editor. Learn how!

www.thewindowsclub.com

 

[glynn1969]

Try making each of the 5 workbooks a 'Trusted Document'

Microsoft Office Trusted Documents and Trusted Locations

Exploring how we can manage Microsoft Office Trusted Documents and Trusted Location both manually and through automation.

www.devhut.net

Add, remove, or change a trusted location in Microsoft Office - Microsoft Support

A trusted location is a folder that contains files you're confident are safe. See how to add, remove, or change a trusted location in Microsoft Office.

support.microsoft.com

Trusted documents - Microsoft Support

Learn what trusted documents are and how to change the options.

support.microsoft.com

How to enable or disable Trusted Documents in Word, Excel, PowerPoint

You can enable or disable Trusted Documents in Word, Excel, PowerPoint using Office settings, Registry or Group Policy Editor. Learn how!

www.thewindowsclub.com

Thank you for your reply - unfotunately this is a work computer and thay have locked down this access

 

[rlv01]

Thank you for your reply - unfotunately this is a work computer and thay have locked down this access

If so my friend, you have problems. But most corporations don't lock that down for internal users so I would double check if I were you. People at work still need to designate trusted documents or folders.

 

[glynn1969]

If so my friend, you have problems. But most corporations don't lock that down for internal users so I would double check if I were you. People at work still need to designate trusted documents or folders

 

[glynn1969]

Ok....il have another look on monday.

Is it not posibble a second macro to pick up the open books and then apply their own internal macro?

 

[rlv01]

Ok....il have another look on monday.

Is it not posibble a second macro to pick up the open books and then apply their own internal macro?

Not if macro execution is disabled. You would be better off pulling all macros in to your master workbook.

 

[glynn1969]

Apologies, i may have misled you, i can open the 5 workbooks and select to enable the macros from the top bar, and then can run macros. My issue is that my vba is trying to run my macro before i have been able to allow macros manually. Whuch is why i thought that with the books opened manually then there coukd be a macro to select each open workbook, run my refreshWS macro and close.