Query Table in Excel

[chungtinhlak]

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

[CreganTur]

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.

[chungtinhlak]

i think you're talking about access now, this is in excel. I can't find Database Utilities under tools in excel.

[geekgirlau]

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]