SQL script to select another file in same folder

[Guest]

My code below needs another line to identify the workbook path is where the
folder where the workbook resides.

SELECT data.heading1, data.heading2
FROM [Excel 8.0;HDR=Yes;database=.\book1.xls";].[Data]

I would like this MS Query in Excel to link with the file book1.xls, which
is located in the same folder as the open (active) workbook. However, the
system thinks the activeworkbook path is that shown in my default settings
(Tools-Options-General-Default Path).

Thank you for your help.

Thank ytou.

 

[Bob Phillips]

Can't you just build it up

sFile = Activeworkbook.Path & Application.PathSeparator & "Book1.xls"

sSQL = "SELECT data.heading1, data.heading2 FROM [Excel
8.0;HDR=Yes;database=." & sFile & "";].[Data]"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Bob, I copy and pasted the new statements into the Microsoft Query (Excel's
Data - Import External Data -..Database Query) and got the message "Invalid
SQL statement; expected Delete, Insert, Procedure, Select or Update".

Is there another way? Thank you.

Can't you just build it up

sFile = Activeworkbook.Path & Application.PathSeparator & "Book1.xls"

sSQL = "SELECT data.heading1, data.heading2 FROM [Excel
8.0;HDR=Yes;database=." & sFile & "";].[Data]"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

My code below needs another line to identify the workbook path is where
the
folder where the workbook resides.

SELECT data.heading1, data.heading2
FROM [Excel 8.0;HDR=Yes;database=.\book1.xls";].[Data]

I would like this MS Query in Excel to link with the file book1.xls, which
is located in the same folder as the open (active) workbook. However, the
system thinks the activeworkbook path is that shown in my default settings
(Tools-Options-General-Default Path).

Thank you for your help.

Thank ytou.