VBScript to create pivot table in new excel document

olivetwist — Thu, 18 Feb 2021 19:47:39 GMT

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.

Re: VBScript to create pivot table in new excel document

olivetwist — Thu, 18 Feb 2021 20:02:30 GMT

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"

topic Re: VBScript to create pivot table in new excel document in QlikView

VBScript to create pivot table in new excel document

Re: VBScript to create pivot table in new excel document