Help! Excel multiple worksheet populate from recordset
 

Using Access2000, the sample code below is what I have been modifying and
working on since the past week and I could not get it to work properly.

What I wanted to accomplish:
1) read from a recordset and export to Excel
2) Excel is populated based from an ID (may possible be one or multiple) and
renames the worksheet based from the ID
3) the code also format the fields

The sample database may be downloaded at:
http://www.geocities.com/mgtulips/sample_db.mdb

TIA!
Bob

Help! Excel multiple worksheet populate from recordset
 

Put following in a module in your database..


Sub CreateXLS()
Dim oRS As ADODB.Recordset
Dim oCN As ADODB.Connection
Const sXL = "d:\myexport.xls"

If Dir(sXL) < "" Then Kill sXL

Set oCN = New Connection
oCN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
CurrentDb.Name

Set oRS = oCN.Execute("SELECT DISTINCT t.ID from tmp_result_all as T")
While Not oRS.EOF
oCN.Execute " SELECT * INTO " & _
" [Excel 8.0;Database=" & sXL & "].[" & oRS(0) & "]" & _
" FROM tmp_result_all AS t" & _
" WHERE t.ID = '" & oRS(0) & "';"
oRS.MoveNext
Wend
oRS.Close
oCN.Close


End Sub



ammend & improve where needed... :)



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"B" wrote :

Using Access2000, the sample code below is what I have been modifying
and working on since the past week and I could not get it to work
properly.

What I wanted to accomplish:
1) read from a recordset and export to Excel
2) Excel is populated based from an ID (may possible be one or
multiple) and renames the worksheet based from the ID
3) the code also format the fields

The sample database may be downloaded at:
http://www.geocities.com/mgtulips/sample_db.mdb

TIA!
Bob