Print report in an Excel Format

[access2010]

Hello, can I print a Ms Access 2003 report in an Excel Format?

Thank you.
Crystal

 

[plog]

Layout it out that way in the report:

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

 

[isladogs]

Display the data in datasheet format which looks similar to an Excel worksheet.

 

[theDBguy]

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]

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]

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]

Pat Hartman (2020_Jan_14)
We will start working on your suggestion.

Thank you.
Nicole.

 

[sxschech]

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.

.Cells(3, 1).CopyFromRecordset rs

 

[June7]

Very simple report might export to Excel nicely but exporting query might be simpler.

Review http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

 

[access2010]

sxschech and June7

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

Nicole

 

[zeroaccess]

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]

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