Relative MS Query?

mhenk

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591
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?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
VBA code to open an excel workbook, Refresh the query (PQ), once it has finished running, save and close the workbook
Replies
2
Views
1K
hananak
H
C
  • Question Question
Converting all of my Excel VBA library standard modules to class modules?
Replies
5
Views
267
RoryA
RoryA
I
  • Question Question
Unable to Open or Copy Corrupted Excel Files on Network Drive
Replies
1
Views
306
Candyman8019
C
R
  • Question Question
Macro to print to PDF saves files to desktop
2
Replies
10
Views
298
jolivanes
jolivanes
O
  • Solved
MS Excel VBA Macro to Open a MS Word File, Save the File and then Close the File
Replies
3
Views
737
OilEconomist
O

Forum statistics

Threads
1,226,224
Messages
6,189,726
Members
453,566
Latest member
ariestattle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top