Export report to excel (1 Viewer)

tmyers

Well-known member
Local time
Yesterday, 21:24
Joined
Sep 8, 2020
Messages
1,090
I have worked with importing an Excel sheet into an Access table before, but never with trying to output a report into Excel. I have a single report whose data is determined with a button click from a form, so this report is used a lot. Is it possible to export selected fields from that report into an excel sheet in a certain format?

I am sure I can export the report in its entirety by changing the format I currently use to XLS rather than PDF. If I do that however, it wont be in the correct format I would need it to be. Would it be possible to export to XLS then run code that takes the newly created workbook to either put that data into another workbook, or reconfigure the sheet to be how I need it to?

Long story short, I am trying to save people time doing data entry in our companies primary program by having my app output to Excel so it can be turned around and imported into our companies program (which supports excel imports, but in specific formats). This would make me the new favorite in the office since it would mean project managers no longer have to painstakingly entire line after line (some projects well and exceed 200 lines with 10+ columns each).

Could someone point me in the right direction?
 

Isaac

Lifelong Learner
Local time
Yesterday, 18:24
Joined
Mar 14, 2017
Messages
8,738
Excel is made for simple data grids. So you would export a query, not a report. Is this OK for you?

Continuing along those lines, I like to use the range CopyFromRecordset method. There are "simpler" ways, but you get what you pay for - they come with weaknesses, and CopyfromRecordset only involves a few lines of code.
 

tmyers

Well-known member
Local time
Yesterday, 21:24
Joined
Sep 8, 2020
Messages
1,090
Excel is made for simple data grids. So you would export a query, not a report. Is this OK for you?

Continuing along those lines, I like to use the range CopyFromRecordset method. There are "simpler" ways, but you get what you pay for - they come with weaknesses, and CopyfromRecordset only involves a few lines of code.
I suppose I could just as easily use the reports underlying query. My thought was I already have done code to export as PDF, so reworking that to XLS wouldn't be hard. Where I get hung up how to reformat the newly created excel file.
 

Minty

AWF VIP
Local time
Today, 01:24
Joined
Jul 26, 2013
Messages
10,355
You can pretty much apply any formatting to an excel file from Access, as usual, it's just a case of getting the methods and syntax correct.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 01:24
Joined
Feb 19, 2013
Messages
16,553
Something I do all the time. You might also want to look at whether your corporate system will accept text files, they are less prone to formatting issues you can get with excel.

import files do not normally need any formatting - just column headers and the data below. But sometimes the source system requires the file to be 'topped and tailed' with file information/check values in which case you are into more vba code. So you need to be clear what you mean by 'formatting'

you would normally export the underlying report query rather than the report itself - various ways but the transferspreadsheet (or transfertext) method is probably the easiest, particularly if a specific layout is required for importing to another system. If you only require some of the columns and they need to be in a specific order, create a new query using your report query as its source.

Sometimes the other system requires several files e.g. new customers, customer changes, invoices etc, in which case each would need to be exported separately, but can still be done with the press of one button.
 

tmyers

Well-known member
Local time
Yesterday, 21:24
Joined
Sep 8, 2020
Messages
1,090
Something I do all the time. You might also want to look at whether your corporate system will accept text files, they are less prone to formatting issues you can get with excel.

import files do not normally need any formatting - just column headers and the data below. But sometimes the source system requires the file to be 'topped and tailed' with file information/check values in which case you are into more vba code. So you need to be clear what you mean by 'formatting'

you would normally export the underlying report query rather than the report itself - various ways but the transferspreadsheet (or transfertext) method is probably the easiest, particularly if a specific layout is required for importing to another system. If you only require some of the columns and they need to be in a specific order, create a new query using your report query as its source.

Sometimes the other system requires several files e.g. new customers, customer changes, invoices etc, in which case each would need to be exported separately, but can still be done with the press of one button.
I use bad terminology, which is my fault, but yes, by "formatting" I do mean get the data into specific columns within another excel sheet. The columns are in a specific order and need to remain that way. Data doesnt have to be in every column however, but I do need all the data existing within the report/query I export.

As far using text files, the only two formats our system accepts for import is .XLS and .CSV
 

tmyers

Well-known member
Local time
Yesterday, 21:24
Joined
Sep 8, 2020
Messages
1,090
I made a new query and structured it as close as I could to how it needed to be. I got it exporting into Excel correctly. Now I just need to figure out how to get the data from that sheet either into a template or to restructure the newly created sheet into the right column structure.

The next fun part is one of the fields has a character limit of 60. That will be exceeded frequently. So I will also have to write out code to determine if the text exceeds that, and if it does, cut the characters over 60 out and put them into the next column.

EDIT:
Over complicated it yet again. I just made a temp table with all the columns needed. Turned my query into an append query then exported the table.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 01:24
Joined
Feb 19, 2013
Messages
16,553
As far using text files, the only two formats our system accepts for import is .XLS and .CSV
so look at using .csv which is a text file (comma separated variable). But up to you

Now I just need to figure out how to get the data from that sheet either into a template or to restructure the newly created sheet into the right column structure.
really don't see what the issue is - just present your query with the columns in the required order.

The next fun part is one of the fields has a character limit of 60. That will be exceeded frequently. So I will also have to write out code to determine if the text exceeds that, and if it does, cut the characters over 60 out and put them into the next column.
new one on me - I presume the next column is an 'overflow' without a character limit. But easy to fix in your query using an iif function

iif(len(myfield)>60, left(myfield,60),myfield)

and for the other column

iif(len(myfield)>60,(myfield,60))

waste of time and effort creating a table to export - just more to go wrong and will cause FE bloat
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 01:24
Joined
Jul 9, 2003
Messages
16,245
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​

 

tmyers

Well-known member
Local time
Yesterday, 21:24
Joined
Sep 8, 2020
Messages
1,090
CJ, I tried using your IIF formula, but I don't seem to be getting the desired results. I have never used LEN or any of the LEFT/RIGHT functions, so am unsure where I went wrong.
I used:
Prt: IIf(Len([Part])>60,Left([Part],60),[Part]) for the primary column and
Part2: IIf(Len([Prt])>60,[Prt],60) for the overflow
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:24
Joined
Sep 21, 2011
Messages
14,048
The overflow column should likely be
Code:
iif(len(myfield)>60,mid(myfield,61))
 

CJ_London

Super Moderator
Staff member
Local time
Today, 01:24
Joined
Feb 19, 2013
Messages
16,553
what does 'don't seem to be getting the desired results' mean?

Looks like I missed a function name out for the second column

iif(len(myfield)>60,mid(myfield,60))
 

tmyers

Well-known member
Local time
Yesterday, 21:24
Joined
Sep 8, 2020
Messages
1,090
I am so bad lately at accurately relaying my thoughts.
It did not seem to be limiting/splitting. I will make that change and see what happens.
 

Users who are viewing this thread

Top Bottom