"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.

--