
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
Qry_Rte"
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.
Eg
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?