November 30, 2008 at 11:16 pm
I set up Excel pivot tables based on OLAP cubes. I have them running on my network and now I want to set them up at another site. I thought I could simply edit the .oqy file to have the cube point to the database on the new server. I am able to edit the .oqy file but the changes do not take effect. This link states that once the cube has been created it is bound to
that data source: http://www.mydatabasesupport.com/forums/sqlserver-datawarehouse/167089-utility-change-data-source-cube.html
Is my only option to re-create the cube and pivot tables at the other site? There must be a better way...
Thanks for any advice you can provide.
December 2, 2008 at 4:41 am
Have you tried going into Tools >Macro > Microsoft Script Editor, finding the connection string and then change then source server.
December 2, 2008 at 12:11 pm
Thanks for the suggestion. I gave it a try and changed the source server in the connection string but I still get the same connection error.
December 8, 2008 at 8:29 am
You should be able to change the connectionsettings using VBA.
Below is an example if your workbook has only one pivotcache; you probably want to work out a better way to alter the string (newConn = ...)
Sub changeOLAPsource()
Dim pvt As PivotCache
Dim oldConn As String
Dim newConn As String
'get the pivotcache
Set pvt = ActiveWorkbook.PivotCaches(1)
'get the current connection
oldConn = pvt.Connection
'alter to the connection needed
newConn = Replace(oldConn, "OLD_Servername", "NEW_Servername")
'set pivotcache to the new connection
pvt.Connection = newConn
'clean up
Set pvt = Nothing
End Sub
I have no possibility to test this now, but I think I used this in the past.
Peter Rijs
BI Consultant, The Netherlands
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply