CreatePivotTable fails with Error 1004 on excel 2010

[hsenul]

I have following macro code that works fine on excel 2007 however when I try this on excel 2010 it fails with error 1004 - Application-defined or object-defined error. This seems to be excel version specific but I am not sure what is the cause of this error.

Has anyone faced similar issues or any suggestions how to work around this problem?

<code>
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "OLEDB;Provider=MSOLAP; Initial Catalog=[OCWCube]; CreateCube=CREATE CUBE [OCWCube] ( DIMENSION [sp3Code], LEVEL [All] TYPE ALL," _
& " LEVEL [sp3Code], LEVEL [Book], LEVEL [PL Cat], LEVEL [Sec], LEVEL [Nominal], DIMENSION [Source], L" _
& "EVEL [All] TYPE ALL, LEVEL [Source], LEVEL [Daz Item], MEASURE [DailyUSD] FUNCTION SUM ); InsertInto=INSERT INTO O" _
& "CWCube([Source].[Source], [sp3Code].[sp3Code], [Book], [Sec], [Nominal], [PL Cat], [Daz Item], SKIPONECOLUMN, [DailyUS" _
& "D]) OPTIONS ATTEMPT_ANALYSIS " _
& strSQL _
& "; Source_DSN=""DSN=MS Access Database;DBQ=" _
& shtStat.Range("statDB") _
& ";DefaultDir=c:\temp;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"";"

.CommandType = xlCmdCube
.CommandText = Array("OCWCube")
.MaintainConnection = True
.CreatePivotTable TableDestination:=ActiveWorkbook.Worksheets("Dunzler").Cells(7, 2), TableName:="ptDazzler", DefaultVersion:=xlPivotTableVersion10
End With
</code>

 

[hsenul]

1st bump