Print report in an Excel Format (1 Viewer)

access2010

Registered User.
Local time
Today, 16:24
Joined
Dec 26, 2009
Messages
1,019
Hello, can I print a Ms Access 2003 report in an Excel Format?

Thank you.
Crystal
 

plog

Banishment Pending
Local time
Today, 18:24
Joined
May 11, 2011
Messages
11,613
Layout it out that way in the report:

Field1Name | Field2Name | Field3Name | ...
___________________________________
Field1Value | Field2alue | Field3Value | ...
___________________________________
Field1Value | Field2alue | Field3Value | ...
___________________________________
Field1Value | Field2alue | Field3Value | ...
...
 

isladogs

MVP / VIP
Local time
Today, 23:24
Joined
Jan 14, 2017
Messages
18,186
Display the data in datasheet format which looks similar to an Excel worksheet.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:24
Joined
Oct 29, 2018
Messages
21,358
Hi Crystal. In case you don't want to modify the report or create a new one, you could consider exporting the report into Excel and then print it from there. Just a thought...
 

access2010

Registered User.
Local time
Today, 16:24
Joined
Dec 26, 2009
Messages
1,019
isladogs, isladogs and theDBguy

Thank you for your suggestions which we will try.

In the past Many, Many, Many +, years we could supply the Government With Paper Reports, but now we have been requested to send them the same reports previously sent in an Electronic format and preferable in Excel.

Do we have to be careful about the creation of these new Excel Reports as we prefer to create them perfectly the first time.

Any magic words of wisdom for this new venture?

Thank you,
Crystal
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:24
Joined
Feb 19, 2002
Messages
42,979
The "export to Excel" feature of Access is (and always has been) unsatisfactory to the point of being useless so, you just have to roll your own. If a simple list will suffice, then just use TransferSpreadsheet with column headings. If you want something fancier, you will need to use OLE automation or export to an Excel template and then use OLE to force the formatting macro to run. It really depends on whether you prefer to code the formatting in Access or in Excel. I generally use Access but since my Excel VBA is poor, I frequently export the data and open excel and turn on the macro recorder. After I have done all the formatting, I take the code generated by the macro recorder and bring it back into Access. Usually object references have to be adjusted because you are manipulating the cells from the perspective of Access rather than Excel but the changes are simple. The simplest solution, if the client will accept it is to print the report as a pdf. That retains all the formatting and doesn't require any coding.
 

access2010

Registered User.
Local time
Today, 16:24
Joined
Dec 26, 2009
Messages
1,019
Pat Hartman (2020_Jan_14)
We will start working on your suggestion.

Thank you.
Nicole.
 

sxschech

Registered User.
Local time
Today, 16:24
Joined
Mar 2, 2010
Messages
791
If using vba, for excel export, another option is to use a query or sql statement and then copyfromrecordset. This will paste all the data at once into excel and you can specify the start location - such as B5 rather than A1.

Example below tells it to be pasted into row 3 col 1.
Code:
.Cells(3, 1).CopyFromRecordset rs
 

access2010

Registered User.
Local time
Today, 16:24
Joined
Dec 26, 2009
Messages
1,019
sxschech and June7

Thank you both for your suggestions which we are experimenting with.

Nicole
 

zeroaccess

Active member
Local time
Today, 18:24
Joined
Jan 30, 2020
Messages
671
Very simple report might export to Excel nicely but exporting query might be simpler.
Yes - I export queries using the Export to Excel macro function and it works better than the transferspreadsheet VBA method. Maybe it's something I was doing wrong, but I couldn't get the VBA method to export the data with lookup values. It only exported the raw data, so all my names/items just had ID values and it was useless. It also keeps adding more sheets to the same Excel workbook each time you run it, so I gave up and stuck with the macro. Something to try.
 

June7

AWF VIP
Local time
Today, 15:24
Joined
Mar 9, 2014
Messages
5,423
This is another reason why building lookup fields in tables is a bad idea - they cause confusion. If you want the lookup values then build query that joins tables and pulls in descriptive info from lookup table. Export that query. http://access.mvps.org/Access/lookupfields.htm
 
Last edited:

Users who are viewing this thread

Top Bottom