Export a report to excel

[Chimp8471]

I have designed a report in Access, which i am happy with, one of the next requirements it to Analyze it in excel...

is there a way to do the following :

automatically export the report into excel without having to click the button on the toolbar,

example:

click button on my form to run the report ----> report opens ----> Analyze in Excel starts automatically.

when i anaylzing the report in excel the formatting of the report is all over the place, can i set this up to auto tidy on opening..

 

[cstahlberg]

If the DoCmd.OutputTo isn't working, I think you need to create an aplication object, open it and then begin writing your records in a do loop:

You might need to check your references to use this code, along with others I have Microsoft Office DAO 3.6 selected...

example code:

Public Sub writeexcelreport()
Dim recset As Recordset
Dim fieldcount As Integer
Dim i As Integer
Dim j As Integer

Set objXLApp = CreateObject("excel.application")
Set objxlbook = objXLApp.Workbooks.Add
Set recset = CurrentDb().OpenRecordset("myreporttable")
'myreporttable is the table that holds the data for your report
objXLApp.Visible = True
fieldcount = recset.Fields.Count
recset.MoveFirst
j = 0
With objXLApp
Do
j = j + 1
for i = 0 to fieldcount - 1
.sheets(1).cells(j + 1, i + 1).value = recset.Fields(i).value
next i
recset.movenext
Loop until recset.eof
End With
Set objXLApp = Nothing
Set objxlbook = Nothing
set recset = nothing
Exit Sub