SELECT ... INTO ... IN ...
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? |
SELECT ... INTO ... IN ...
"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. -- |
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. -- |
All times are GMT +1. The time now is 12:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com