Exporting to Excel

[PSURAM]

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.

 

[isladogs]

Crossposted at UtterAccess.com with replies

@PSURAM Please read this article about crossposting: https://www.excelguru.ca/content.php?184

 

[Pat Hartman]

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.

 

[Gasman]

Crossposted at UtterAccess.com with replies

@PSURAM Please read this article about crossposting: https://www.excelguru.ca/content.php?184

Crossposted here as well I believe? https://www.accessforums.net/showthread.php?t=82652&highlight=exporting+excel

 

[Uncle Gizmo]

I use the excellent code provided by BTAB Developments to send to, and format data in Excel:-

Export A Table Or Query To Excel | BTAB Development

Copy this code into a standard (not form or report) module. Name the module something other than this function name and make sure to set a reference to DAO if you don't already have one. Public Function SendTQ2Excel(strTQName As String, Optional strSheetName As String) ' strTQName is the name of...

btabdevelopment.com

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​

Make Excel Sheets From Access Table - Nifty Access

Excel Sheets From Access Table If you've ever faced the challenge of exporting data from Microsoft Access into Excel, you’re not alone! One of the most common requests is to organize data by categories, like exporting car makes into separate Excel sheets or even separate Excel files. I’ve...

www.niftyaccess.com

 

[shadow9449]

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:

    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

 

[Pat Hartman]

The visuals are not transferred well

 

[onur_can]

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)

 

[isladogs]

@onur_can
I agree with you about exporting to a prepared Excel template.
For info, your 'shortcut' didn't work.

 

[onur_can]

Thank you. isladogs for the information. I updated my message.