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

    Question

  • 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
     Worksheets("Drilldown").Rows("5:65536").Delete
     Worksheets("Drilldown").Select
      
     
     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

Answers

All replies