Is there a way of opening a specific workbook, running a macro, an saving the workbook automatically at certain times?

[Coyotex3]

Wondering if Excel can do this. Let's say I have a workbook named "Coyote" on my donwloads folder. Could I tell Excel through VBA to open that workbook run the macro "Speed of Light" and then save the workbook like every monday at 9:30am for example?

Or is excel able to do anything remotely close to this?

 

[johnnyL]

To run a macro contained in another workbook:

Application.Run "'Coyote.xlsm'!Speed of Light"

As far as saving that workbook on a schedule, Look into Application.OnTime

 

[Coyotex3]

To run a macro contained in another workbook:

Application.Run "'Coyote.xlsm'!Speed of Light"

As far as saving that workbook on a schedule, Look into Application.OnTime

Hmmm it is telling me that it can't find the file. It is looking in sharepoint for some reason. After that I get 1004 error Application defined or Object-defined error.

I will look into Application.OnTime. THANK YOU!

 

[johnnyL]

Here is more complete sample:

Sub OpenTest()
'
    DownloadFolderPath = Environ$("USERPROFILE") & "\Downloads\"        ' Get Path to Downloads folder
'
    Workbooks.Open DownloadFolderPath & "Coyote.xlsm"                   ' Open desired workbook in that folder path
'
    Application.Run "'Coyote.xlsm'!Speed of Light"                      ' Run macro from within that workbook
End Sub

 

[Coyotex3]

Here is more complete sample:

Sub OpenTest()
'
    DownloadFolderPath = Environ$("USERPROFILE") & "\Downloads\"        ' Get Path to Downloads folder
'
    Workbooks.Open DownloadFolderPath & "Coyote.xlsm"                   ' Open desired workbook in that folder path
'
    Application.Run "'Coyote.xlsm'!Speed of Light"                      ' Run macro from within that workbook
End Sub

Almost there. I was able to open the workbook but still getting runtime 1004 error.

 

[johnnyL]

Worked fine for me.

You do realize that you can't have spaces in a Macro name right? You would have to name the macro, for example, SpeedOfLight

 

[Coyotex3]

Worked fine for me.

You do realize that you can't have spaces in a Macro name right? You would have to name the macro, for example, SpeedOfLight

This is what I have

Sub OpenTest()
'
    DownloadFolderPath = Environ$("USERPROFILE") & "\Downloads\"        ' Get Path to Downloads folder
'
    Workbooks.Open DownloadFolderPath & "SpeedTest.xlsx"                   ' Open desired workbook in that folder path
'
    Application.Run "'SpeedTest.xlsx'!SpeedOfLight"                      ' Run macro from within that workbook
End Sub

Does it have to be a macro enabled workbook?

 

[johnnyL]

Of course. You can't run a macro in a non macro enabled workbook.

 

[Joe4]

Wondering if Excel can do this. Let's say I have a workbook named "Coyote" on my donwloads folder. Could I tell Excel through VBA to open that workbook run the macro "Speed of Light" and then save the workbook like every monday at 9:30am for example?

Or is excel able to do anything remotely close to this?

One thing I have often done is to either place/or call my code to run in the "Workbook_Open" event procedure in VBA. This will cause the macro to run automatically upon opening the file.

Then, I will use something like Windows Scheduler to automatically open the file at my desired time and day, which will open the file, and then the Workbook_Open event will cause my code to run.

If you use this method, just be sure to add a line of code at the bottom of the "Workbook_Open" event to automatically close the workbook too.

There are other similar methods you can find with a simple Google search, like this one:

How To Automatically Run Excel VBA Macros Daily

Learn how to utilize a few programs you already have to create automation rules that will automatically open up your Excel files, run VBA code, save the changes, and then close the file. These automations can even be setup to run while you sleep!

www.thespreadsheetguru.com

 

[Coyotex3]

Of course. You can't run a macro in a non macro enabled workbook.

Basics.. Completely overthought it.

One thing I have often done is to either place/or call my code to run in the "Workbook_Open" event procedure in VBA. This will cause the macro to run automatically upon opening the file.

Then, I will use something like Windows Scheduler to automatically open the file at my desired time and day, which will open the file, and then the Workbook_Open event will cause my code to run.

If you use this method, just be sure to add a line of code at the bottom of the "Workbook_Open" event to automatically close the workbook too.

There are other similar methods you can find with a simple Google search, like this one:

How To Automatically Run Excel VBA Macros Daily

Learn how to utilize a few programs you already have to create automation rules that will automatically open up your Excel files, run VBA code, save the changes, and then close the file. These automations can even be setup to run while you sleep!

www.thespreadsheetguru.com

Thank you Joe.

Will look into this link!