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.

Answer 1

Hi,

 

I would like to know Excel version on your XP machine, Excel 2003 or other versions?

Please see the CreatePivotTable Method [Excel 2003 VBA Language Reference]

The forth variant named DefaultVersion:  The default version of the PivotTable report.

 

Also see the documents below:

XlPivotTableVersionList Enumeration (2003)

XlPivotTableVersionList Enumeration (2007)

XlPivotTableVersionList Enumeration (2010)

 

It is easily to discover that the same member in different version has different reference. You can try to assign the version value manually to see if it will fix the problem.

 

I hope this helps.

---

Best Regards, Calvin Gao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Excel 2003, SQL Server 2005 and OS Windows 7. It's olap connection and works fine in Windows XP Pro.

Answer 3

 

Excel 2003, SQL Server 2005 and OS Windows 7. It's olap connection and works fine in Windows XP Pro.

Do you mean that on both XP and Windows 7 is Office 2003?

Have you try my suggestion above?

 You can try to assign the version value manually to see if it will fix the problem.

 

Please let me know if it can fix the issue.

---

Best Regards, Calvin Gao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

Thank you Calvin.