Thank you .. this is of use to me.

But, how can I blend this VBA code in with my SQL code. If a variable
(str) is defined in VBA, can I use (str) in my SQL code and continue to use
an absolute reference? I'm working of menu Data - Import External Data -
MS Query (in Excel 2003)

What I was thinking was to switch my SQL code to use a relative reference
(eg .\filename.xls), but first using VBA to establish the current directory
to that of the active workbook.

When I do this, I get an error message the first time the SQL runs after
moving the workbooks to a new folder. When I open and close the SQL code it
then works fine. I wouldn't want the users of my spreadsheet to have to do
this everytime they move the spreadsheets to a new folder.

Please assist some more.... many thanks.



"SteveM" wrote:

Correction:

Sub ShowPath()
Dim str As String
str = ActiveWorkbook.Path
str = str & "\" & ActiveWorkbook.Name
MsgBox str

End Sub


On Oct 20, 10:19 pm, SteveM wrote:
Use the Object Path and Name properties. E.g.,

Dim str as String
str = ActiveWorkbook.Path
str = str & AcvtiveWorkbook.Name

That will give you both the path and file name of the workbook. Then
replace any absolute references with the String (str) value using the
"&" to properly concatenate.

SteveM

On Oct 19, 6:10 pm, RiverGully
wrote:

Hi,

I have several spreadsheets in the same folder that are consolidated into
one spreadsheet using the MS Query feature in Excel. I have reproduced a
part of the query below. The 'FROM' line states an absolute reference:
J:\Budget\filename.xls; is there a way to tell SQL that the file it is
looking for is in the base (current) folder so I can simply state the file
name without the path.

SELECT tblOwnTax.CODE, LEFT(Description,18), MID(DESCRIPTION,30,30),
tblOwnTax.PYR3, tblOwnTax.PYR2, tblOwnTax.PYR1, tblOwnTax.CYR,
tblOwnTax.CYR1, tblOwnTax.CYR2, tblOwnTax.CYR3, tblOwnTax.CYR4,
tblOwnTax.CYR5 FROM `J:\Budget\GOR_Revenue`.tblOwnTax tblOwnTax
WHERE (tblOwnTax.CODE Like '%-GO%')

Union SELECT tblPIPS.CODE, LEFT(Description,18),
MID(DESCRIPTION,30,30), tblPIPS.PYR3, tblPIPS.PYR2, tblPIPS.PYR1,
tblPIPS.CYR, tblPIPS.CYR1, tblPIPS.CYR2, tblPIPS.CYR3, tblPIPS.CYR4,
tblPIPS.CYR5 FROM `J:\Budget\GOR_Revenue`.tblPIPS tblPIPS WHERE
(tblPIPS.CODE Like '%-GO%')

Many thanks in advance.