By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,009 Members | 2,821 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,009 IT Pros & Developers. It's quick & easy.

Outputting Access Report to Excel-Formatting problems

P: 1
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
Share this Question
Share on Google+
6 Replies


nico5038
Expert 2.5K+
P: 3,072
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
Expert 100+
P: 1,356
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
Expert Mod 15k+
P: 31,492
(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
Expert 100+
P: 1,356
Very nice and consice Neo I like the way you put that together.
Mar 20 '07 #5

NeoPa
Expert Mod 15k+
P: 31,492
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
Expert 100+
P: 1,356
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.