Changing Cube source in a EXCEL pivot table

Hi

I have developed a cube on a "test" server - using Excel 2003 as teh front end. All is well with the cube an dI now want to point Excel to the "production" server. I have modified the .oqy file to point to teh new server but Excel does not seem to use this - unless I start over and import data.

is there anyway of pointing to a different server (teh database/cube names are the same - I did an Archive and Restore to move teh database over)

thanks

Steve

By the way - this is SQL Analysis Server 2000 and Excel 2003|||

There are 2 techniques I know of to change the data source for an Excel pivot table.

The "low tech" one is to shut down the Analysis Services instance on your test server. Then when you open up the Excel file, Excel will prompt you for a server.

The "hight tech" option is to write some VBA code to change the connection string. The trick to getting at the connection string is that it is stored on the connection property of the PivotCache object. To change a single pivot table on the current sheet you would write something like the following:

thisWorkbook.ActiveSheet.PivotTables(1).PivotCache.Connection = "Provider-MSOLAP.2;Data Source=<Server>;Initial Catalog=<Database>"

|||Another technique, which was mentioned on the public SQL OLAP Newsgroup, is to save the pivot table as an XML file. Then you can find and edit the connection string (search for the server/DB/cube names), and re-open and save the XML back as .XLS.|||

Thanks to all.

I checked out the XML and the VBA solutions - at least I have options now.