Posted to microsoft.public.excel.programming
|
|
SELECT ... INTO ... IN ...
Hi Jamie
Thanks very much
Best regards
Loane
"Jamie Collins" wrote in message
om...
"Loane Sharp" wrote ...
Using the "SELECT ... INTO ... IN ..." statement to import Access data
to an
Excel workbook, I only seem to be able to do this
(1) if the Excel workbook is closed
Correct. Anyhow, using SQL on an open workbook is never a good idea
e.g. with ADO your app will experience a memory leak:
BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using
ADO
http://support.microsoft.com/default...;en-us;Q319998
(2) if the destination worksheet doesn't yet exist.
Not quite correct. The 'destination' cannot be a worksheet. Rather,
SELECT..INTO (and INSERT INTO..SELECT) creates a workbook-level
defined Name. For example, if my target workbook has only one
worksheet named Sheet2 hosting a workbook-level defined Name named
Sheet1 then
SELECT * INTO [Excel 8.0;Database=C:\MyWorkbook.xls;].Sheet1
will fail because the defined Name Sheet1 already exists. Conversely:
SELECT * INTO [Excel 8.0;Database=C:\MyWorkbook.xls;].Sheet2
will succeed, even though the worksheet Sheet2 exists, because there
is no defined Name named Sheet2. HOWEVER, because a worksheet named
Sheet2 already exists and is not empty, the driver creates a new
worksheet named Sheet21 (following the Excel convention) to host the
new Sheet2 Name.
Jamie.
--
|