Please help if you can

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 and

(2) if the destination worksheet doesn't yet exist.

Is this correct?

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

--

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.

--