PDA

View Full Version : Query Table in Excel



chungtinhlak
09-29-2009, 06:46 AM
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
09-29-2009, 07:06 AM
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
09-29-2009, 08:12 AM
i think you're talking about access now, this is in excel. I can't find Database Utilities under tools in excel.

geekgirlau
10-04-2009, 09:49 PM
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.


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