[dba-Tech] VBS question

[Gustav Brock]

Hi Susan

Excel is very picky with its objects. So, try:

'.vbs file to print Excel PivoTable1.xlsm at 8am daily.

'Path to workbook.
strPath = "C:\Documents\PivotTable1.xlsm" 

'Macro name.
strMacro = "PrintUpdatedPivotTable" 

'Create an Excel instance.
Set objApp = CreateObject("Excel.Application") 
Set objwb = objExcel.Workbooks.Open(strPath) 
objApp.Visible = True  ' Should not be needed.

objApp.Run strMacro

objwb.Close
Set objwb = Nothing

objApp.Quit 

Set objApp = Nothing

'Scheduled task completed. 
MsgBox strPath & " " & strMacro & " PivotTable printed successfully", vbInformation


Also, check that no Excel instance is left open after running the script.

-----Oprindelig meddelelse-----
Fra: dba-Tech <dba-tech-bounces at databaseadvisors.com> På vegne af Susan Harkins
Sendt: 21. juni 2019 15:08
Til: Discussion of Hardware and Software issues <dba-tech at databaseadvisors.com>
Emne: [dba-Tech] VBS question


Do any of you use .vbs scripts to interact with an Excel workbook? I've written one to run from Windows Task Scheduler. It simply triggers a print macro (prints a PivotTable) daily. 

The macro in Excel works. The task is running, but the PivotTable never prints. This is rather new to me, so I'm sure it's a simple fix, probably in one of the referencing statements. Can someone take a look? I've tried several variations of referencing the macro. 

'.vbs file to print Excel PivoTable1.xlsm at 8am daily.

'Path to workbook.
strPath = "C:\Documents\PivotTable1.xlsm" 

'Macro name.
strMacro = "PrintUpdatedPivotTable" 

'Create an Excel instance.
Set objApp = CreateObject("Excel.Application") Set objwb =
objExcel.Workbooks.Open(strPath) objApp.Visible = True

objApp.Run strMacro
objwb.Close
objApp.Quit 

'Scheduled task completed. 
MsgBox strPath & " " & strMacro & " PivotTable printed successfully", vbInformation

Susan H.