Excel data directly to queries

M

mtress

Can I link Excel data directly into a query? Must I use
SQL? Do the SourceConnectStr and SourceDatabase
properties have anything to do with this? One more...Is
is true I can't do this (if I can) with a union query?
Thanks alot!
 
J

Jamie Richards

Hi

I think the best way to emulate a query (without writing too much code) is
to use the File --> Get External Data -->Link Tables feature. This will
build a link to the Excel file you nominate (follow prompts) and will read
the results in the specified sheet each time the link is opened (like a
query). Alternatively you could import the data using the same method (but
use Import instead of Link), but this would not give you new data as it is
added to the spreadsheet, if that is what you're after.

There are "pros" and "cons" doing this. The major pro is the ease and speed
with which it can be accomplished. One of the big cons is that the Excel
file may be moved, deleted or otherwise modified. If you can keep the xls
file under control it is an attractive option (IMHO).


Jamie
 
J

Jamie Collins

mtress said:
Can I link Excel data directly into a query?
Click to expand...

Sure can e.g.

SELECT MyExcelCol
FROM [Excel 8.0;Database=C:\MyWorkbook.xls;].[Sheet3$]
;
Must I use SQL?
Click to expand...

It helps if you do said:
Is is true I can't do this (if I can) with a union query?
Click to expand...

It can be done and is easily proven by first exporting to Excel then
querying back in a UNION ALL e.g.

SELECT MyTextCol, MyIntCol
INTO [Excel 8.0;Database=C:\mtress.xls;].[Sheet1]
FROM MyJetTable
;

SELECT MyTextCol, MyIntCol
FROM MyJetTable
UNION ALL
SELECT MyTextCol, MyIntCol
FROM [Excel 8.0;Database=C:\mtress.xls;].[Sheet1]
;

Jamie.

--
 
Top