VBS Script to Open Up Excel every Morning and Run Macro called "Save"

Question

VBS Script to Open Up Excel every Morning and Run Macro called "Save"

Anonymous

I have a file that I have to run every morning. It is a giant file that uses PowerQuery to pull data and refresh chart. Is this a good way to open up the file every morning refresh it and call the macro called Save?

Can Anyone walk me through this to see if I am on the right track?

'Input Excel File's Full Path

ExcelFilePath = "J:\Daily Visit Reports Combined.xlsm"

'Input Module/Macro name within the Excel File

MacroPath = "Module1.Save"

'Create an instance of Excel

Set ExcelApp = CreateObject("Excel.Application")

'Do you want this Excel instance to be visible?

ExcelApp.Visible = True 'or "False"

'Prevent any App Launch Alerts (ie Update External Links)

ExcelApp.DisplayAlerts = False

'Open Excel File

Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)

'Execute Macro Code

ExcelApp.Run MacroPath

'Save Excel File (if applicable)

wb.Save

'Reset Display Alerts Before Closing

ExcelApp.DisplayAlerts = True

'Close Excel File

wb.Close

'End instance of Excel

ExcelApp.Quit

'Leaves an onscreen message!

MsgBox "Your Automated Task successfully ran at " & TimeValue(Now), vbInformation

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Answer 1

Hey I'm your man! I have about 10 files that do exactly this every single morning for me. Because I don't want to have to open an Excel file and click refresh every day just for my team to see some updated reports.

The way I did it was to create an "OnOpen" macro that runs every time the file is opened, then I scheduled a task to open that Excel file at 4am every day.

The Excel file that opens is really just the macro brains, then it in turn opens the 2nd file, refreshes it, closes it, then closes itself.

You're VBA is already pretty good progress!

Share via

VBS Script to Open Up Excel every Morning and Run Macro called "Save"

1 answer