Outputting Access Report to Excel-Formatting problems

[DeniseY]

I have an Access report that is created on the fly by the user selecting the fields to be included. The Access report comes out fine, but I want it to automatically output to an Excel spreadsheet. Again, I have this part working. But the fields in the resulting spreadsheet are in a different order than the Access report. (Example: The fields in the Access report might go FirstName, LastName,Address,City--in the Excel spreadsheet, they come up FirstName,City,LastName,Address). I have output reports to Excel before and they work fine. I know the problem is that since users select what fields go into the report, that the underlying fields in the report design change. How can I fix it so the fields in both the report and the spreadsheet are in the same order?
This is the code I am using to create the report.

Sub MakeReport()
On Error GoTo Err_MakeReport
 
Dim rptName As String
 
    'Open report in design view to write properties to objects
    DoCmd.OpenReport "rptCustom", acDesign
 
    'Read combo box selections and use subroutine to set report object properties
    SetReportControls Forms!frmChooseFields.cboField1.Value, _
      Reports!rptCustom.lblField1, Reports!rptCustom.tbField1
    SetReportControls Forms!frmChooseFields.cboField2.Value, _
      Reports!rptCustom.lblField2, Reports!rptCustom.tbField2
    SetReportControls Forms!frmChooseFields.cboField3.Value, _
      Reports!rptCustom.lblField3, Reports!rptCustom.tbField3
    SetReportControls Forms!frmChooseFields.cboField4.Value, _
      Reports!rptCustom.lblField4, Reports!rptCustom.tbField4
    SetReportControls Forms!frmChooseFields.cboField5.Value, _
      Reports!rptCustom.lblField5, Reports!rptCustom.tbField5
 
 
    'Close design view without prompting to save changes
    DoCmd.Close acReport, "rptCustom", acSaveYes
 
    'Open finished report in preview view
   rptName = "rptCustom"
 
    DoCmd.OutputTo acOutputReport, "rptCustom", acFormatXLS, , True
 
Exit_MakeReport:
    Exit Sub
 
Err_MakeReport:
    MsgBox Err.Description
    Resume Exit_MakeReport
 
End Sub

Thanks for any help you can provide.

[nico5038]

The export to excel of a report is a real pain, but why not use the report query and a docmd.transferspreadsheet ?

Nic;o)

[Denburt]

I agree with nico however if you really wanted to you could manipulate the data in Excel but your need to set a reference (tools-References) to Excel then some of the following may point you in that direction if you choose.

Dim ObjXL As Excel.Application
Dim ObjXLBook As Excel.Workbook
Dim ObjXLSheet As Excel.Worksheet


DoCmd.OutputTo acOutputReport, "rptCustom", acFormatXLS, , True
Set objXL = GetObject(, "Excel.Application")

objXL.DisplayAlerts = False

objXL.Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

objXL.Application.Save
objXL.Application.DisplayAlerts = True




Excellent article a must read if you are taking this route:
Microsoft KB

[NeoPa]

(Application Automation) might help to enable you to run stuff in Excel from Access if this is how you want to go.

[Denburt]

Very nice and consice Neo I like the way you put that together.

[NeoPa]

Very nice and concise Neo I like the way you put that together.

Thanks Denburt.
I like a lot of your posts too. You're quickly becoming a valuable resource around here. I think the Admins have you tagged already though ;)
Nice work, I look forward to seeing more of you.

[Denburt]

Thanks, I enjoy helping when and where I can. I have visited this site before and picked up tips so I thought it was time to return the favor. I am still rooting around and finding stuff all in all its setup is pretty nice and easy to navigate.

All in all I am doing this and working (yes on Access projects) lol so a lot of the time I just throw something out there and hope they get it. :)