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

Coyotex3

Active Member
Joined
Dec 12, 2021
Messages
312
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
3,958
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
To run a macro contained in another workbook:

VBA Code:
Application.Run "'Coyote.xlsm'!Speed of Light"

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

Coyotex3

Active Member
Joined
Dec 12, 2021
Messages
312
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
To run a macro contained in another workbook:

VBA Code:
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

Well-known Member
Joined
Nov 7, 2011
Messages
3,958
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Here is more complete sample:

VBA Code:
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
 
Solution

Coyotex3

Active Member
Joined
Dec 12, 2021
Messages
312
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Here is more complete sample:

VBA Code:
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

Well-known Member
Joined
Nov 7, 2011
Messages
3,958
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

Active Member
Joined
Dec 12, 2021
Messages
312
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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

VBA Code:
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

Well-known Member
Joined
Nov 7, 2011
Messages
3,958
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Of course. You can't run a macro in a non macro enabled workbook.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,089
Office Version
  1. 365
Platform
  1. Windows
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:
 

Coyotex3

Active Member
Joined
Dec 12, 2021
Messages
312
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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:
Thank you Joe.

Will look into this link!
 

Forum statistics

Threads
1,181,212
Messages
5,928,722
Members
436,625
Latest member
agholson81

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