Run-Time Error 1004 in Windows 7/Excel2003/SQL 2005 on .CreatePivotTable


  • When I am running this Excel spreadsheet with drilldown functionality on Windows 7 Professional (64 bit) OS, it presents me with error:

    Run-Time Error 1004. Application-defined or object-defined error.

    Initially I thought we have some problem with installations for cubes (because this report is working fine on XP Pro), but I already installed all necessary components for 64 bit and some drilldown reports worked. The problematic report however, contains pivot table creation part and fails.

    Sub DeleteAndRecreatePivot()
     SetUpdate True
     Dim initialCatalog As String
     initialCatalog = Worksheets("LUCube").[Q6].Value
     Dim cubeName As String
     cubeName = Worksheets("LUCube").[Q4].Value
     With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
      .Connection = "OLEDB;Provider=MSOLAP;Location=SRV-06;Initial Catalog=" & initialCatalog
      .CommandType = xlCmdCube
      .CommandText = Array(cubeName)
      .MaintainConnection = True
    'Fails on the following line of the code!!!
      .CreatePivotTable TableDestination:=Range("A10"), TableName:="PivotTable1"
    <br/> End With
     ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
     SetUpdate False
    End Sub

    I have no idea where to look further - any help will be welcomed.

    I am running Windows 7 Pro/Excel2003/SQL server 2005 .

    Thank you.

    Friday, May 06, 2011 2:31 AM


All replies