Run-time error '1004' Application-defined or object-defined error in Excel 2010 when pulling data from SSAS2005 cube

[Lanscaping]

When attempting to pull data out of SSAS2005 data cube on server into a pivot table on Windows desktop, I got this "Run-time error '1004' Application-defined or object-defined error" message. It happens everytime when I run this Macros. The section in red is the one the debug complaints. Any comment or feedback is welcomed.

Sub Macro1()
'
' Macro1 Macro
'
Workbooks("Book1").Connections.Add _
"Server-name Cube-name", "", Array( _
"OLEDB;Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Data Source=Server-name;Initial Catalog= Data Catalog"), Array("Store"), 1
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("Server-name Store"), _
Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:= _
"Sheet1!R1C1", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion14
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable1").CubeFields( _
"[Store].[Store By Location]")
.Orientation = xlRowField
.Position = 1
End With
End Sub

 

[Jerry Sullivan]

Hi and Welcome to the Board,

It's hard to replicate this without having access to a data cube.

On possible reason for the error is if you already have a "PivotTable1" in your Worksheet, it won't let you use that name for a new object.

 

[Lanscaping]

It worked after changing the sheet and table names to something else.
Thanks Jerry for your hint. Much appreciated!

 

[Jerry Sullivan]

It worked after changing the sheet and table names to something else.
Thanks Jerry for your hint. Much appreciated!

You're welcome.
Thanks for providing the feedback that a naming conflict appears to have been the cause.