External Data Query


G

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.
 
Ad

Advertisements

C

Carim

Bonjour Marie,

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

HTH
Cheers
Carim
 
G

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.
 
Ad

Advertisements

C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top