Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#3
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: Column Select then Data Select then return to cell A1 | Excel Discussion (Misc queries) | |||
Macro to select cells without a certain value and select a menu it | Excel Worksheet Functions | |||
Using formulas to select cells (Ex: Select every nth cell in a col | Excel Discussion (Misc queries) | |||
In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) | |||
In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) |