0

I have a list of excel files (some hundreds), and every files contains a macro to update some data.

Now I want to create a script that opens every single file and call the macro inside the opened file so these data are updated in every file.

So I created a new excel file called "call_all_macros.xlsm" and I created the script to iterate through all the files and call the macro. Unfortunatelly it is not working, it iterates correctly, it seems that it calls the macro and saves it, but in fact the data are not updated (althought the file is beeing saved).

This is the code I use to do the call of the macro, sometimes it is not working here:

For Each fileName In fileCollection
       Set wb = eApp.Workbooks.Open(fileName)
       wb.Application.Run macro_to_be_called
       
       wb.Save
       wb.Close
       Debug.Print "Processed " & fileName  'Print progress on Immediate window
   Next fileName

Everything is working except the macro that is not being called. Am I doing something wrong?

The called macro take the ActiveWorkbook and write some data in some cell in the ActiveWorkbook. Maybe it has a conflict in figuring out what the Activeworkbook is?

Andrea
  • 35
  • 7
  • It's probably the active workbook, either make the opened ```wb``` the active workbook or change the code in all the workbooks to be ```thisworkbook```. – Warcupine Dec 28 '20 at 14:25
  • Where is macro_to_be_called defined? Is it in the original Workbook or is it in wb, or is it somewhere else, e.g. the personal macro workbook? – z32a7ul Dec 28 '20 at 14:46
  • So if your data isn't updating how do you know the file's macro is running? I suspect the files are opening, saving, and closing without the macro being run. Using the comments in the thread below, the OPs line ```wb.Application.Run macro_to_be_called``` should be changed to ```wb.application.run("'fileName'!macro_to_be_called")``` or something like that. May not need the single quotes since ```fileName``` is a variable. – Chris H. Dec 28 '20 at 15:12
  • macro_to_be_called is in the ThisWorkbook of every single file (wb) – Andrea Dec 28 '20 at 17:37
  • It's in ThisWorkbook and not a regular Module? When you call a macro Excel is looking for it in a Module. That's why it's not running. – Chris H. Dec 28 '20 at 17:44
  • Thank you, i didn't know it. is there a way to make it work with ThisWorkbook? – Andrea Dec 28 '20 at 19:27

0 Answers0