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
|