VBScript to create pivot table in new excel document

[olivetwist]

Has anyone figured out a way to export data to an excel doc and then create a pivot table within that doc using Qlikview VBScript? I am trying to avoid creating a separate workbook to house the pivot table macro.

I have tried recording the macro in Excel and using that code which has syntax errors (Expected ')'):

  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Name!R1C1:R1048576C5", Version:=6).CreatePivotTable _
    TableDestination:="Summary!R3C1", TableName:="SummaryPivot", DefaultVersion:=6

As well as trying to convert that recorded VBA to VBScript, like the line below, which returns as an invalid procedure call or argument error:

objWorkbook.PivotCaches.Create(xlDatabase, "Name!R1C1:R1048576C5", 6).CreatePivotTable "Summary!R3C1", "SummaryPivot", 6

Any advice is appreciated. 

[olivetwist · Accepted solution]

I think I have answered my own question:

The issue was "xlDatabase". use the reference numbers instead.

objWorkbook.PivotCaches.Create(1, "YTD Net Cases By Manager!R1C1:R1048576C5", 6).CreatePivotTable objWorkbook.Sheets(1).Range("A3"), "SummaryPivot"