Refresh PowerPivot with vbs script

[orlinskipawel]

Hello everyone,

I have an excel spreeadsheet with powerpivot data, which needs to be copied and refreshed twice a week.
I managed to write a script that makes a copy of the spreadsheet, but I don't know how to refresh powerpivot data.
I googled the problem and found method QueryTable.Refresh, but it doesn't work.
Here is my code:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("my_spreadsheet.xlsx")
objExcel.Application.Visible = False
objExcel.Application.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs "my_spreadsheet_" & Date & ".xlsx",51
objExcel.QueryTable.Refresh
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
WScript.Quit

Can anyone help me?
Thanks in advance!

 

[Topkapi]

Have you tried pivot cache refresh e.g. something like objworksheet.PivotTables("PivotTable1").PivotCache.Refresh ?