Create a macro to run from a master workbook but program it to run on another workbook that gets overwritten daily and cannot store a macro.

[abishop2022]

I have a workbook that is updated daily (deleted and replaced with new workbook). I would like to be able to create a macro that runs on a schedule to format this new workbook after it is created. I looked into this and I started by creating a "MacroWorkbook.xlsm" and copying the sheet that I want to format from the new workbook over to the MacroWorkbook.xlsm and then created a macro with the specific formatting I need.
How do I manage to schedule this macro to run on the new workbook?

 

[Joe4]

Welcome to the Board!

If you use the "Workbook_Open" event procedure, you can make the VBA code run upon the opening of the workbook (see: Automatically run a macro when opening a workbook).

Then, you can use something like Windows Scheduler (or any other scheduling software) to open this workbook at your designated day/time, which then should automatically kick off your code to run.

Just be sure that to close the Excel file when done.
One way to do that is to add a line like this to the end of your "Workbook_Open" code:

Application.Quit

which will close Excel and the workbook entirely.

 

[abishop2022]

Joe4,
Thank you for the quick reply!
Admittedly, I have very little or no experience writing macros or scheduling them. So far, I have a macro called "WorflowAnalysisReportMacro" stored in the "MarcroWorkbook.xlsm". How would I get this to open and run on the brand new workbook? I have to mention that the new workbook is a .xls... will this work?

 

[Joe4]

I have to mention that the new workbook is a .xls... will this work?

Absolutely.

How would I get this to open and run on the brand new workbook?

Just have your code open the other workbook. Is it named the same thing every time?
If not, what is the naming convention?

Note that you can get a lot of VBA code to do things like this by returning on your Macro Recorder, and recording yourself performing these steps manually.
So, if you turn on the Macro Recorder, open the other file, and then turn off the Macro Recorder, you can look at the code you just recorded, which will be the code you need to open that file.

Sometimes, you may want to clean-up the recorded code a little (it is a bit literal, and records unnecessary things like scrolling and cell selection), or edit it to make it a bit more dynamic.
But it can be a great tool to get snippets of code which you can use in the you VBA code.

 

[abishop2022]

Absolutely.

Just have your code open the other workbook. Is it named the same thing every time?
If not, what is the naming convention?

Note that you can get a lot of VBA code to do things like this by returning on your Macro Recorder, and recording yourself performing these steps manually.
So, if you turn on the Macro Recorder, open the other file, and then turn off the Macro Recorder, you can look at the code you just recorded, which will be the code you need to open that file.

Sometimes, you may want to clean-up the recorded code a little (it is a bit literal, and records unnecessary things like scrolling and cell selection), or edit it to make it a bit more dynamic.
But it can be a great tool to get snippets of code which you can use in the you VBA code.

Yes, the workbook is called "WorkflowAnalysisReport.xls" and it is named that every time. I would just need to build the macro from the main workboook "MacroWorkbook.xlsm" to execute and run on "WorkflowAnalysisReport.xls".

 

[Joe4]

Like I said, that is not a problem. Just record a macro from your macro file opening the other file.
Note that once you open a new file, that file becomes the active workbook, by default. So any VBA code you run after that will be against that opened workbook, unless you re-activate the original macro workbook. I use VBA code like this to run on other workbooks all the time!

 

[abishop2022]

Like I said, that is not a problem. Just record a macro from your macro file opening the other file.
Note that once you open a new file, that file becomes the active workbook, by default. So any VBA code you run after that will be against that opened workbook, unless you re-activate the original macro workbook. I use VBA code like this to run on other workbooks all the time!

OK great! I'll give it a try. Thank you!

 

[Joe4]

You are welcome!

Here is a little trick I do to make moving back and forth between my two files easier. I create two workbook objects, one for the Macro workbook, and one for the data file I am opening.
Then, at the very beginning of my VBA code, I set the the macro workbook object, and then right after the part of the code where I open the new file, I set the data file workbook object, something like this:

Sub MyMacroName()

    Dim mcrWb As Workbook
    Dim datWb As Workbook

'   Capture this macro workbook as a Workbook object
    Set mcrWb = ActiveWorkbook
   
'   Open new workbook
    Workbooks.Open Filename:="excel file name and path"

'   Capture this new data file as a Workbook object
    Set datWb = ActiveWorkbook

Then, after that, you can easily bounce back and forth between workbooks (if needed) in your code by doing this:

mcrWb.Activate

to select the Macro Workbook
and this:

datWb.Activate

to select the data workbook.

 

[abishop2022]

You are welcome!

Here is a little trick I do to make moving back and forth between my two files easier. I create two workbook objects, one for the Macro workbook, and one for the data file I am opening.
Then, at the very beginning of my VBA code, I set the the macro workbook object, and then right after the part of the code where I open the new file, I set the data file workbook object, something like this:

Sub MyMacroName()

    Dim mcrWb As Workbook
    Dim datWb As Workbook

'   Capture this macro workbook as a Workbook object
    Set mcrWb = ActiveWorkbook
  
'   Open new workbook
    Workbooks.Open Filename:="excel file name and path"

'   Capture this new data file as a Workbook object
    Set datWb = ActiveWorkbook

Then, after that, you can easily bounce back and forth between workbooks (if needed) in your code by doing this:

mcrWb.Activate

to select the Macro Workbook
and this:

datWb.Activate

to select the data workbook.

Hi Joe4,
I tried what you said and recorded my macro in the "MacroWorkbook" then opened the "WorkflowAnalysis.xls" to do the formatting. If I run the macro manually from the workbook, it works fine, but when I create a .vbs file to run the macro, I get an error with a message to debug. I tried to attached the .vbs file and .bas file to this message but it won't let me. HELP!!!

 

[Joe4]

Sorry, I really cannot help you with .vbs script.
That is really out-of-scope for this question (you did not mention wanting to do anything like that until now, and VBS script in NOT the same as VBA).