External Data Query

[Guest]

We've just moved our SQL databases and I have a spreadsheet that pulls in
external data from the old database. I've reset the ODBC links on my PC, but
the spreadsheet is still trying to look at the old server, so the link must
be somehow hard coded into the spreadsheet. I can't 'edit the query' from
the Data/Import External Data menu, so do you know how I can get into the
query to edit it? Thank you.

 

[Carim]

Bonjour Marie,

Take a look in VBA at the QueryTable Object which has Connection as a
property you can modify ...

HTH
Cheers
Carim

 

[Guest]

Thanks Carim, not sure where I'm looking for this, i've gone to View Code
from the tab, but can't find anything there (unless I'm looking in the wrong
place), do you have anymore suggestions? Thank you.

 

[Carim]

Marie,

You are right ... View Code and then the icon Object Browser ...

Meanwhile, found the following VBA code to ChangeServer

Sub ChangeServer()
'Declare your variables.
Dim ptc As PivotCache, oldSrv As String, newSrv As String

'Request the name of the old server/file name.
oldSrv = InputBox("Input the name of the old server or file path as

listed in the Pivot Tables SQL string.")

'Request the name of the new server/file name.
newSrv = InputBox("Input the name of the new server or file path
which
you want the Pivot Table to point to.")

'Replace the ODBC information of whatever PivotTable is currently
active.
Set ptc = ActiveCell.PivotTable.PivotCache
ptc.Connection = Application.Substitute(ptc.Connection, oldSrv,
newSrv)
ptc.Sql = Application.Substitute(ptc.Sql, oldSrv, newSrv)
End Sub


HTH
Cheers
Carim