Experts,

I've created a Excel Add-In using Excel 2007 and created a OLEDB connection with this Add-In to create pivot table with that conenction. I've added that OLEDB connection to Add-In (not for activeworkbook) for security resons (If I add the connection to the activeworkbook with help of Add-In my username& password will be displayed in active workbook connection properties).

I'm trying to create pivotCache to create pivot table with Add-In's OLEDB connection, it is raising error(1004: Application-defined or object-defined error).

The following code used to created pivot table cache
ThisWorkbook.Connections.Add "OlapConnection", "", Array("OLEDB;Provider=MSOLAP.3; Persist Security Info=True;Data Source=datasource;Initial Catalog=dbname;roles=Role2005;TimeOut=5000;User ID=UserID;Password=password;"), Array("SampleCube"), 1
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=ThisWorkbook.Connections(OlapConnection), Version:=xlPivotTableVersion12)


Can anyone help me to resolve this issue?

Thanks in advance!
Venkat