0

I have an Excel macro that I'm automating to run at 4am each day as it's a long running macro that can take up to 2 hours due to the amount of data that has to be parsed and can stop the user that's running the process from doing any other work. To do this, I have a task set up in the Task Scheduler that runs a vbs file to load Excel, run the macro, then save a copy ready for users to come in to at the start of the business day. The vbs script is successfully running, however when I open the file afterwards, it appears as though the macro hasn't completed as there isn't a separate file that's been saved and data tables appear blank. I think this is due to the vbs not allowing enough time to complete the macro before it closes Excel. Here's my script:

ExcelFilPath = "...\Documents\TestBook.xlsm"
MacroPath = "Module1.Macro1"
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Displayalerts = False
Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)
ExcelApp.Run MacroPath
wb.SaveAs = "...\Documents\TestBookComplete.xlsm"
ExcelApp.DisplayAlerts = True
wb.Close
ExcelApp.Quit
MsgBox "Task successfully run at " & TimeValue(Now), vbInformation
GetWhimsical
  • 69
  • 8
  • Do you see the msgbox **Task successfully run at** before you open the wb? – Zac Oct 07 '20 at 10:17
  • No, the test workbook I have closes first – GetWhimsical Oct 07 '20 at 10:27
  • The code that you have shared, is that in your VBscript? – Zac Oct 07 '20 at 10:37
  • That's right yeah - it's the full vbscript file I have - the test macro I have in the excel file just highlights a bunch of cells and changes their colour and adds some random numbers in so I can test to see if it's working – GetWhimsical Oct 07 '20 at 10:41
  • So when the vbscript completes, you should see a msgbox popup saying: **Task successfully run at...**. Do you see this msgbox appear? – Zac Oct 07 '20 at 10:45
  • No, the message box doesn't appear at all, but Excel closes – GetWhimsical Oct 07 '20 at 11:09
  • @GetWhimsical Why set `DisplayAlerts = True` before closing isn't that going to potentially allow dialogs to appear that stops the workbook from closing and blocks the script? – user692942 Oct 07 '20 at 11:09
  • @GetWhimsical Think your problem is permission related which is why the file is being saved and the `MsgBox()` not being displayed. – user692942 Oct 07 '20 at 11:16

0 Answers0