Changing External Data Location

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff Harbin - 30 Mar 2008 12:54 GMT

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.

Reply to this Message

Bill Manville - 31 Mar 2008 07:50 GMT

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

Reply to this Message

Rate this thread: