SELECT * INTO - XL workbook Multiple sheet SQL error 
Author Message
 SELECT * INTO - XL workbook Multiple sheet SQL error

I am experiencing one of those great MicroSoft anomalies.
I am trying to use the "SELECT * INTO " SQL to export several queries
from MS Access into separate worksheets within an Excel 97 workbook.
Sample code shown below.

Set db = CurrentDb
db.Execute "SELECT * INTO [Excel 8.0;DATABASE=c:\temp\Code].[Code]
FROM Qry_CodeRteOfr"
db.Execute "SELECT * INTO [Excel 8.0;DATABASE=c:\temp\Code].[Rte] FROM

This code works fine when exporting into separate workbooks, but when
adding to the same workbook (in separate sheets) the export is getting
corrupted.  The correct number of fields and rows are created however
they are populated with the field names of the previous worksheet.

Should be
dmikey  dmidata
0       #
1       (REM 3) (INC 300) $
2       (CL BEG) (REM 11) (INC 563115) $
3       (CL BEG) (REM 7) (INC 901215002699) $
4       (CL BEG) (REM 11) (INC 791365) $
5       (CL BEG) (REM 4) (INC 901223353906) $
6       (CL BEG) (REM 4) (INC 90122353) $
7       (CL BEG) (REM 11) (INC 901603205888) $
8       (CL BEG) (REM 11) (INC 901235765900) $

But is
system  Code_xlaname
fromdigs        todigs
xlasel  xlasel_opt
destname        cc
npa     Rte_tbl
Rte_xlaname     Rte_rte_idx
Rte_rtelist_1   Rte_rtelist_2
Rte_rtelist_3   Rte_rtelist_4
Rte_rtelist_5   Rte_rtelist_6
Rte_rtelist_7   Rte_rtelist_8

Has anyone else come across problems like this?

Sun, 26 Sep 2004 22:33:24 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Selecting multiple sheets in an Excel workbook

2. SQL; Select Into Xl-File From XL-File

3. H2 insert new XL worksheet incl data into existing XL workbook

4. Read XL-sheet with VB4 without XL

5. Read XL-sheet with VB4 without XL

6. Read XL-sheet with VB4 without XL

7. Word2000 Mail merge from selected sheet in Excel workbook

8. Excel VBA: search multiple-sheets in a workbook

9. Convert XL sheet to SQL table

10. combine multiple excel workbooks into one workbook

11. Utility: Compare two XL workbooks

12. Creation of an organisation chart based on an XL sheet - VBA problem


Powered by phpBB® Forum Software