Sub SetDirectoryPath()
' Get current directory to equal that of the active workbook
ChDir ActiveWorkbook.Path
Calculate
ActiveWorkbook.Save

End Sub

Then my SQL code can use the relative reference syntax '.\filename.xls'

Problem is after moving spreadsheet to a new folder, the first time the SQL
code is called upon it gives an error and only works after first opening and
closing the SQL code.



"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.