Help! Excel multiple worksheet populate from recordset

[B]

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

 

[keepITcool]

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 >