Solved Exporting a access report to excel

[Snappy1263]

I have reports with headers and grouping. I would like to export this to a excel template. I know how to send to excel the data from the query to a template and export the report directly to excel. But I don't know how to do it so when it gets to excel it looks exactly like the report. I have attached the report for your viewing. On the report you see a model and underneath part numbers. When I do the export to excel you get the models and the part numbers for each line. I just want the model once and all part number following. Hope this is possible. Thanks all

 

[CJ_London]

The only way you can do that is once exported to excel is to the format excel the way you want

Alternative is to export to pdf which should preserve your report format.

What is the benefit for you or your users of exporting to excel in a format that looks like the report?

 

[Snappy1263]

Yes, I already use a PDF but our customers now want excel spreadsheets instead. They upload the pricing in their systems. I realize they can convert the PDF to excel themselves but they are requesting us to send it in excel. I was trying to simplify it and eliminate some steps I have.

 

[Uncle Gizmo]

This excellent VBA code provided by BTAB Developments will allow you to format the export to excel. Unfortunately, it doesn't do it for you, you need to alter the code to suit.

There's an example on my website here:-

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

Bob Larson's Code here:-

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

Another example of the code which demonstrates some formatting (I think)

Show-Hide-Export to Excel - Nifty Access

Show/Hide & Export to Excel What started out as an answer to a question on Expert Exchange, took on a life of its own and became a pop-up form which allows you to select the columns you want to display in a datasheet. Now, this is not at all a groundbreaking technology! It's actually already

www.niftyaccess.com

 

[Snappy1263]

This excellent VBA code provided by BTAB Developments will allow you to format the export to excel. Unfortunately, it doesn't do it for you, you need to alter the code to suit.

There's an example on my website here:-

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

Bob Larson's Code here:-

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

Another example of the code which demonstrates some formatting (I think)

Show-Hide-Export to Excel - Nifty Access

Show/Hide & Export to Excel What started out as an answer to a question on Expert Exchange, took on a life of its own and became a pop-up form which allows you to select the columns you want to display in a datasheet. Now, this is not at all a groundbreaking technology! It's actually already

www.niftyaccess.com

That is a great code. Is there a way to only put the model once instead of each line in your example. This is my major issue. My example snip.

 

[bastanu]

You can use Excel automation to go through each cell on the Model column starting from last row going up, compare its value to the one just above and delete it if the same.
Can you provide a small sample with your report and some "dummy" data to illustrate the issue?
Cheers,

 

[theDBguy]

They upload the pricing in their systems.

Is this what they need to do with the Excel file you're sending them? If so, are you sure they'll be able to do that if you remove the duplicate rows?

 

[Uncle Gizmo]

Is there a way to only put the model once instead of each line in your example.

If you look at this video:-

at this time index 418

it Copies from the RecordSet "CopyFromRecordSet" into a range, so it's an existing recordset, it just copies what it sees without the ability to make any adjustment. You'd have to modify a recordset sent through to the VBA code, not sure if that's feasible, or change the the Excel sheet as described in a previous post by @bastanu , that's probably the simplest...

 

[Snappy1263]

You can use Excel automation to go through each cell on the Model column starting from last row going up, compare its value to the one just above and delete it if the same.
Can you provide a small sample with your report and some "dummy" data to illustrate the issue?
Cheers,

The PDF BW report is the report and how I want it to look in the excel template. The PDF BW Price is what happens now when I send it to excel

 

[theDBguy]

The PDF BW report is the report and how I want it to look in the excel template. The PDF BW Price is what happens now when I send it to excel

Hi. Just curious... How exactly are you sending the report to Excel?

 

[Gasman]

I would have thought it would be harder for the other party to process the excel sheet like that? Reports are for display, not input?

 

[bastanu]

Agree with post # 7 and 11, removing the model from all rows but the first one in the group will make the file harder to use as an automated input\upload to an external system. Unless you use some kind of automation I'm afraid you won't get the desired output. The reason I asked for a sample is that it makes it easier for us to help you if we don't have to build the entire interface from scratch and not knowing what\how you have it setup (see the Db Guy question "how do you send it to Excel?").
Cheers,

 

[Pat Hartman]

If the client wants to use the export as an input to another system, forget all the formatting. Don't hide duplicates, etc. This is no longer a report, it is a data file. You can still send them the pretty PDF. The use of the spreadsheet is as a table. Do NOT make the table hard for them to use or require them to manipulate it in any way to import it.

 

[Snappy1263]

All, I do not know how my customers uses the spreadsheet for their system. I upload to my system and I have a template for that and use a text file, so I am sure it is something like that. It was just requested to be an excel price sheet. The price sheet must be formal with our logo etc. I have the template set up with this information. Thank you for all this input it is really great. On my end I am trying to save time as I have many price sheets to do. My boms feed the report I put in the pricing which then feeds into the report along with the other info per line as you can see. I have a separate database linked to my boms to streamline the data. I have a command button to run the code. Here is my code very simple. I have an area named to start the placement of the data in the template. Thanks again everyone.


Private Sub Command0_Click()
Dim filepath As String
Dim sSheetName As String
filepath = "S:\Allfiles\GLBT\BW\2022\BW PRICE.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ETS EXPORT EXCEL COM", filepath, True, "BIMINIPLUS"

MsgBox ("The Excel has been Successfully exported")
End Sub

 

[oleronesoftwares]

Export data to Excel - Microsoft Support

Export data from Access to Excel to take advantage of Excel's charting and analysis features. You can export report data with or without formatting into Excel.

support.microsoft.com

This article might offer solution.

 

[arnelgp]

you can also create a User-defined function to create the worksheet with your
"own" style.

 

[arnelgp]

here is a demo.

 

[Snappy1263]

here is a demo.

Can this go to an excel template??

 

[arnelgp]

upload your template and i will work on it.

 

[Snappy1263]

upload your template and i will work on it.

Thank you sooooo much. I will get it to you shortly, my brain is fried LOL