The process I'm attempting:
TOAD automation script uses Run Program activity to open Workbook A.
Workbook A has a run-on-open macro that does the following: Opens Workbook B, runs a Refresh All in Workbook B, saves changes and closes Workbook B, closes itself (see VBA code below).
I can open Workbook A manually, and everything works perfectly. When I have TDP do it, I get as far as both workbooks being open, and then get stuck on the following error, coming from Excel: "This will cancel a pending data refresh. Continue? [Yes/No]"
I have tried this both with and w/o the "Wait for program to exit" box checked in the Run Program activity.
Any ideas? Thanks
Specs:
Run Program "Program": C:\Program Files (x86)\Microsoft Office\Office16\EXCEL.EXE
Run Program "Arguments": DailyTelevisitsReportMacroRunFile.xlsm
Run Program "Run directory": D:\DSS\Automated_Reports\DailyTelevisitsReport\Production Files\MacroRunFile
VBA code in Workbook A:
Private Sub Workbook_Open()
Dim Location As String
Location = "D:\DSS\Automated_Reports\DailyTelevisitsReport\Production Files\WorkingFile\Daily Televisits Report YYYYMMDD.xlsm"
DoEvents
Workbooks.Open(Location).RunAutoMacros (xlAutoOpen)
Workbooks("Daily Televisits Report YYYYMMDD.xlsm").Activate
ActiveWorkbook.RefreshAll
'Workbooks("DailyTelevisitsReportMacroRunFile.xlsm").Close
For Each w In Application.Workbooks
w.Save
Next w
Application.Quit
End Sub