Using Microsoft Query

[Paul Johnson]

I'm am using Microsoft query to return data from a combination of 2 sheets
within the workbook and into a new sheet.
How can I control the sql to always find the spreadsheets in the workbook in
the current directory? So that if I save the workbook to different location
the queries work for the workbook in the new location. At present if I save
it back it looks to the old location for the tables.

Thanks

Paul

 

[Jamie Collins]

How can I control the sql to always find the spreadsheets in the workbook in
the current directory? So that if I save the workbook to different location
the queries work for the workbook in the new location. At present if I save
it back it looks to the old location for the tables.

Simply omit the path in the connection string e.g.

instead of

SELECT * FROM [Excel 8.0;Database=C:\Tempo\Book1.xls;].[A:A];

use

SELECT * FROM [Excel 8.0;Database=Book1.xls;].[A:A];

Jamie.

--