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