Absolute verses Relative file path in SQL

[Guest]

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.

 

[SteveM]

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

 

[SteveM]

Correction:

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

End Sub

 

[Guest]

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.

 

[Guest]

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.