SQL script to select another file in same folder
 

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.

SQL script to select another file in same folder
 

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)

"RiverGully" wrote in message
...
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.

SQL script to select another file in same folder
 

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.


"Bob Phillips" wrote:

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)

"RiverGully" wrote in message
...
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.