473,375 Members | 1,300 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,375 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 5202
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,556 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,556 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

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

Similar topics

4
by: Jeff Harbin | last post by:
I've got an ACCESS 2000 application that I'm developing. One of the outputs of this app will be to generate a series of 'jobs' which corresponds to a record in the database. Each record will be...
11
by: Mr. Smith | last post by:
Hello all, My code can successfully open, write to, format and save several worksheets in a workbook then save it by a given name, close and quit excel. My problem is that if I try and do it...
18
by: Andre Laplume via AccessMonster.com | last post by:
I have inherited a bunch of dbs which are are shared among a small group in my dept. We typically use the dbs to write queries to extract data, usually dumping it into Excel. Most dbs originated...
1
by: musicloverlch | last post by:
I have a problem. I have an Access 2003 report that contains a number of Word and Excel objects with fancy formatting and spreadsheets, etc. I have about 20 records that I am having the database...
4
by: somanyusernamesaretakenal | last post by:
What I am trying to achieve: Basically I have generated a report in access. This report needs to be updated using excel. (Updating the new data, not changing existing data) What I did was I...
7
by: ddecoste | last post by:
I have a need to add a visual representation to some data in Access. I need to draw a matix of squares inside another square. I have all the data that I need in a record in Access. The data...
5
by: phong.lee | last post by:
Hello all, I was wondering if someone can assist me in outputting 6 reports into a pdf file? I created a macro that generates the 6 reports and right now it's saved as a snapshot on my drive. ...
1
by: anon3803 | last post by:
I have a report in Access that has a lot of grouping (sometimes over 5 group headers). When I export it to Excel where more people can use it and are familiar it creates a lot of blank rows when...
5
by: LadyIlsebet | last post by:
I'm not a fantastic Access developer, but I'm trying to help get Inventory and whatnot organized at work. They are used to 5 year budget plans that list out exactly what has to be purchased what...
23
by: helm | last post by:
Folks, could anyone advise ... Is there a significant difference in crosstab capabilities in Access and Excel? Using Office XP 2002 ... to produce a crosstab report I developed it in Excel from...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.