Exporting to Excel (1 Viewer)

PSURAM

New member
Local time
Today, 08:52
Joined
Jan 13, 2021
Messages
1
Hi,

I have a nicely formatted report in Access (with headers, shading, column widths, etc.) and the customer would like to be able to export the report into a similarly formatted Excel file. Is this possible? I can export the data via the ExportWithFormatting macro but it's not formatted (missing headers, etc.). Is there a way to do this? Keep in mind I'm a novice.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:52
Joined
Feb 19, 2002
Messages
42,970
No. The Access export to Excel is pretty much useless.

You can export the data using TransferSpreadsheet and then using OLE automation, format the export to be a nice report. OR, create macros in Excel to format sheet1 from data in sheet2. Save the macro enabled workbook and export to sheet2 and then run the macro.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:52
Joined
Jul 9, 2003
Messages
16,244
I use the excellent code provided by BTAB Developments to send to, and format data in Excel:-


I believe the code is something to do with Bob Larson, formerly a very active and respected member here on access world forums.

I've used the code in a couple of products and in particular to answer a question from an access World Forum member.

This is a video of the answer I provided:-

Export Query into Separate Excel Sheets - Nifty Access​


You can see more about the question and my my modifications to the code on my website here:-

Excel Sheets From Access Table​

 

shadow9449

Registered User.
Local time
Today, 08:52
Joined
Mar 5, 2004
Messages
1,037
No. The Access export to Excel is pretty much useless.

You can export the data using TransferSpreadsheet and then using OLE automation, format the export to be a nice report. OR, create macros in Excel to format sheet1 from data in sheet2. Save the macro enabled workbook and export to sheet2 and then run the macro.

In what sense is the Access export to Excel useless? Do you mean that it doesn't maintain the visuals of the report you are exporting?

Just to be clear that we are talking about the same thing, I have a module in my project with the following:
Code:
    DoCmd.OutputTo acOutputReport, Screen.ActiveReport.Name, _
        acSpreadsheetTypeExcel12, , True

Overall it works well because many reports have totals and subtotals that carry over to the outputted excel, whereas exporting the query itself loses that totaling.

So, please clarify if we are referring to the same thing and what you meant.

Thank you
 

onur_can

Active member
Local time
Today, 05:52
Joined
Oct 4, 2015
Messages
180
First, make your formatting in the excel table you use as a template and save it, then transfer your data to this excel template file. You can go the shortest way this way. (Excele Data Transfer from Access)
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 12:52
Joined
Jan 14, 2017
Messages
18,186
@onur_can
I agree with you about exporting to a prepared Excel template.
For info, your 'shortcut' didn't work.
 

onur_can

Active member
Local time
Today, 05:52
Joined
Oct 4, 2015
Messages
180
Thank you. isladogs for the information. I updated my message.
 

Users who are viewing this thread

Top Bottom