[dba-Tech] VBS question

[Susan Harkins]

Also -- yesterday, I turned off macro control while testing -- I thought
perhaps that might be a problem -- made no difference. 


Susan H. 

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. 

_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com