I Created pivot tables and pivot charts in excel getting data from an external source (SQL Server 2000) using an ODBC Connection. The problem is when i want to use the same spreadsheet acceess the server from outside through the internet using an ODBC connection.
The odbc which access the sql server remotely uses as server name the ip address of the server than sql server running to.
The pivot tables when it was created it used the odbc connection accessed from with in the LAN (Server name /SERVERGROUP/SQLSERVER).
I was just wondering if there is an easy way to modify the code that the pivot tables uses to make the connection to the SQL Server with out
recreating all those pivot tables and pivot charts.
When I try to open the file remotely it fails to make a connection of course.
If you are using an Excel Macro to do your queries, you are in luck. All you need to do is open the VB Editor and edit the connection string.
Otherwise, you will most likely have to recreate your queries (but if you do so, create a Macro, so you can edit it moreeasily in the future). All you need to do it turn on the Macro Recorder, build your queries as you did before, even perform any formatting, then stop the recorder. Now, if you ever need to tweek the query or change the datasource, it's simply a matter of editing the Marco code.
well thanks i knew that I could do that but I have over 20 graphs and 10 reports so it would be nice if there is a way for a quick and dirty solution. Saying all that if there is not a way to access the code that excel creates on the background when you use those pivot table wizards so I could manually modify the connection string then I guess I need to redo it from scrach and use macros this time.