471,310 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,310 software developers and data experts.

Outputting Access Report to Excel-Formatting problems

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.
Expand|Select|Wrap|Line Numbers
  1. Sub MakeReport()
  2. On Error GoTo Err_MakeReport
  3.  
  4. Dim rptName As String
  5.  
  6.     'Open report in design view to write properties to objects
  7.     DoCmd.OpenReport "rptCustom", acDesign
  8.  
  9.     'Read combo box selections and use subroutine to set report object properties
  10.     SetReportControls Forms!frmChooseFields.cboField1.Value, _
  11.       Reports!rptCustom.lblField1, Reports!rptCustom.tbField1
  12.     SetReportControls Forms!frmChooseFields.cboField2.Value, _
  13.       Reports!rptCustom.lblField2, Reports!rptCustom.tbField2
  14.     SetReportControls Forms!frmChooseFields.cboField3.Value, _
  15.       Reports!rptCustom.lblField3, Reports!rptCustom.tbField3
  16.     SetReportControls Forms!frmChooseFields.cboField4.Value, _
  17.       Reports!rptCustom.lblField4, Reports!rptCustom.tbField4
  18.     SetReportControls Forms!frmChooseFields.cboField5.Value, _
  19.       Reports!rptCustom.lblField5, Reports!rptCustom.tbField5
  20.  
  21.  
  22.     'Close design view without prompting to save changes
  23.     DoCmd.Close acReport, "rptCustom", acSaveYes
  24.  
  25.     'Open finished report in preview view
  26.    rptName = "rptCustom"
  27.  
  28.     DoCmd.OutputTo acOutputReport, "rptCustom", acFormatXLS, , True
  29.  
  30. Exit_MakeReport:
  31.     Exit Sub
  32.  
  33. Err_MakeReport:
  34.     MsgBox Err.Description
  35.     Resume Exit_MakeReport
  36.  
  37. End Sub
Thanks for any help you can provide.
Mar 15 '07 #1
6 5031
nico5038
3,080 Expert 2GB
The export to excel of a report is a real pain, but why not use the report query and a docmd.transferspreadsheet ?

Nic;o)
Mar 17 '07 #2
Denburt
1,356 Expert 1GB
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
Mar 20 '07 #3
NeoPa
32,404 Expert Mod 16PB
(Application Automation) might help to enable you to run stuff in Excel from Access if this is how you want to go.
Mar 20 '07 #4
Denburt
1,356 Expert 1GB
Very nice and consice Neo I like the way you put that together.
Mar 20 '07 #5
NeoPa
32,404 Expert Mod 16PB
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.
Mar 20 '07 #6
Denburt
1,356 Expert 1GB
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. :)
Mar 20 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Jeff Harbin | last post: by
18 posts views Thread by Andre Laplume via AccessMonster.com | last post: by
1 post views Thread by musicloverlch | last post: by
5 posts views Thread by phong.lee | last post: by
5 posts views Thread by LadyIlsebet | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.