Prev: ChangeLink Command Not Working
Next: Excel sorting in VBScript
From: Adriana on 24 Feb 2006 13:56
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?
 | 
Pages: 1
Prev: ChangeLink Command Not Working
Next: Excel sorting in VBScript