Changing External Data Location
 

I have a fairly complex workbook that I used on a regular basis. The
first thing it does is get a data extraction from an ACCESS database
that is located on a different computer over the LAN using MSQuery.

Recently, my IT department swapped out the computer that stored the
ACCESS database with a different computer (new name, new IP address,
etc.). So now even though the database structure is the same it's
stored on a new machine.

How can I change the location that the link uses to retrieve the data.

Changing External Data Location
 

Try this macro.
Open your workbook and have it active when you run the macro.

Sub ChangeQuerys()
Dim stFrom As String
Dim stTo As String
Dim QT As QueryTable
Dim WS As Worksheet
stFrom = InputBox("Old database path (excluding \filename.mdb)?")
stTo = InputBox("New database Path (excluding \filename.mdb)?")
For Each WS In ActiveWorkbook.Worksheets
For Each QT In WS.QueryTables
QT.Connection = Application.Substitute(UCase(QT.Connection),
UCase(stFrom), stTo)
QT.Sql = Application.Substitute(UCase(QT.Sql), UCase(stFrom),
stTo)
QT.Refresh
Next
Next
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup