Loane Sharp said:
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.aspx?scid=kb;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.
--
