Pivot Table version (xlPivotTableVersion12)

A

Adriana

Hi, I'm trying to create a macro that will create a pivot table connected to
an OLAP cube (ie foodmart).

Very simple steps, record a macro that inserts a pivot table in a Sheet and
connects to an external data source, ie food mart. Drop some fields into the
report filter, stop recording and run it. The VBA code looks like this:

With ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
.Connection = Array( _
"OLEDB;Provider=MSOLAP.2;Persist Security Info=True;User
ID=abanica;Data Source=banica;Initial Catalog=FoodMart 2000;Client Cache
Size=" _
, "25;Auto Synch Period=10000;MDX Compatibility=1")
.CommandType = xlCmdCube
.CommandText = Array("Budget")
.MaintainConnection = True
.CreatePivotTable TableDestination:="Sheet5!R3C1", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion12
End With

I will receive : "Run-time error '1004'. The parameter is incorrect."

Now, programatically I'm creating a pivot table : (please change the
connection string...)

Microsoft.Office.Interop.Excel.PivotCache pv =
wb.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlExternal, Type.Missing);
pv.Connection = "OLEDB;Provider=MSOLAP;" + loc[0].ToString()
+ ";Initial Catalog=" + result.Catalog;
pv.CommandType =
Microsoft.Office.Interop.Excel.XlCmdType.xlCmdCube;
pv.CommandText = result.Cube;
pv.MaintainConnection = true;

Microsoft.Office.Interop.Excel.PivotTable pt =
(PivotTable)pv.CreatePivotTable(ws.get_Range("B1, "B2"), "MainPivot", true,
XlPivotTableVersionList.xlPivotTableVersionCurrent);


This code works but the pivot table version will be "xlPivotTableVersion10".
As soon as I'm trying to change the XlPivotTableVersionList to
xlPivotTableVersion12 an error occurs.

Anyone has any ideas?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Pivot Table Error 1
Pivot table and it's macro 1
Programming a macro in Excel Office 2007 to create Pivot Table 1
PivotFilters in pivottable 0
Pivot Table Macro with dynamic source data 1
macro creating new pivot table 2
Create a pivot table with a local connection 0
syntax in VBA to create a pivot table 2

Top