Relative MS Query?

mhenk · Nov 10, 2008

I've got a few connections made to various datasources via MS Query (using XL 2007). I'd like to make these connections relative.

So, instead of pointing to a specific path and filename, I'd like the connections to be to a specific filename in the same directory as the excel file.

Is this possible?

Fazza · Nov 10, 2008

Hi.

There are two properties of each query table that need to be set for this - the connection and the SQL (aka command text).

There is no built in functionality to do what you want (though I am unfamiliar with Excel 2007 I assume this is still the case in that version). To do so is best done via VBA - though it is not strictly required, it is best.

Both the connection and SQL are read/write properties and are purely text strings that can be easily manipulated.

By using the macro recorder whilst making a query table, you can obtain some code for Excel 2007 query tables. Then either post that here (between code tags) or use that as a basis for writing your own code to do what you want.

Along the lines.

Code:

'pseudo code, NOT Excel 2007 specific
dim qt as querytable
dim wks as worksheet
 
for each wks in activeworkbook.worksheets
for each qt in wks.querytables
 
'set connection, maybe using REPLACE$
qt.connection = ...
 
'and SQL
'maybe just a text manipulation to remove file path?
qt.SQL = ...
 
qt.refresh backgroundquery:=false
 
next qt
next wks
 
set qt=nothing
set wks=nothing

Relative MS Query?

mhenk

Well-known Member

Excel Facts

Fazza

MrExcel MVP

Similar threads

Forum statistics

Share this page

Relative MS Query?

mhenk

Well-known Member

Excel Facts

Fazza

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock