-
Query Table in Excel
Hello all, i have excel file that i have tons of query table link from an access database. Now that the database location have change, this there a way in vba to change the datasource for all these query tables or i just have to do it one by one?
thanks
-
If they are truly linked tables, then you can click Tools-> Database Utilities-> Linked Table Manager. It will allow you to update them faster than relinking them by hand.
-
i think you're talking about access now, this is in excel. I can't find Database Utilities under tools in excel.
-
I've moved this thread to the Excel board.
You'll need to enter the full paths for the original database and the new database.
[vba]
Sub RelinkQueries()
Dim qt As QueryTable
Dim sh As Worksheet
Dim strConnect As String
Const cstrDB_Old As String = "C:\Folder\OLD.mdb"
Const cstrDB_New As String = "C:\Folder\NEW.mdb"
For Each sh In ActiveWorkbook.Worksheets
For Each qt In sh.QueryTables
strConnect = qt.Connection
strConnect = Replace(strConnect, cstrDB_Old, cstrDB_New)
qt.Connection = strConnect
qt.Refresh
Next qt
Next sh
End Sub
[/vba]