Toad World® Forums

Automation: Issue Running Macro

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 :slight_smile:

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