How to export a report to Excel

[greaseman]

I've been asked to export an MS Access report to Excel. I'm having trouble with including subtotals, totals, etc. in the Excel sheet. I've enclosed a picture of how my report looks in Access, and was wondering if there's a way to export everything the report has into Excel......

Can anyone maybe point me in a good direction, or to a link that has info re: what I want to do?

And before anyone asks, it's for a manager who insists that everything be done in Excel, since that is what he's comfy with. Outside of that, I wouldn't be giving myself headaches, since my Access report contains everything needed.

Thanks in advance!

 

[Pat Hartman]

There is no easy way to do this. I have had no success with using the export to Excel tool and it sounds like it is not producing what you expected either.

Since your boss probably just wants to play with the data, why not offer him a pivot table view from Access? That may satisfy him and it won't require tons of coding. Another alternative is to simply use the TransferSpreadsheet Method to export the underlying recordSource for the report rather than trying to reproduce the sums and recaps. And finally, you can export the data and then using OLE automate Excel to create the sums and recaps for you. Since I am not all that comfortable with the Excel object model, I would start with an Excel sheet that contains the data only. I would then turn the macro recorder on and try to produce the necessary subtotals. You may have to do this several times before getting a "clean" recording. I would then copy the VBA generated by the macro recorder and paste it into an Access procedure.

 

[greaseman]

Hi, Pat and thanks for responding! The funny thing is, they originally had this little thingie in Excel and asked that it be put into Access, but now the new kid on the block wants it in Excel! What goes 'round, comes 'round.

The hardest part is that the amount of data and number of rows "floats" from month to month, so that aso adds to the mess. In other words, I will never have a fixed number of lines per onth of data.

Again, thanks for your reply. If I get something figured out, I'll post it back to the forum.

 

[Pat Hartman]

You shouldn't need a fixed number of rows to work with OLE since you can always find the end of the data section and that should give you a landmark for adding the calculated cells.

The new pointy haired boss didn't like the pivot table option?

 

[Jigs]

It is possible if u r using CFML

Hi,

Well It is possible to export the report of MS-ACCESS to MS-Excel if u r using Cold Fusion. Check the following sample code:

<cfquery name="qry" datasource="mydsn">
	SELECT * FROM MYTABLE ORDER BY ID
</cfquery>

<cfheader name="Content-Disposition" value="inline; filename=MYEXCEL.xls">
<cfcontent type="application/msexcel" >

<TABLE WIDTH="100%"  BORDER="1" CELLSPACING="0" CELLPADDING="2" ALIGN="CENTER">
<TR>
	<TH SCOPE="col">ID</TH>
	<TH SCOPE="col">MYCOL1</TH>
	<TH SCOPE="col">MYCOL2</TH>
</TR>
<CFOUTPUT QUERY="GETCARS">
	<TR>
		<TD>#ID#</TD>
		<TD>#MYCOL1#</TD>
		<TD>#MYCOL2#</TD>
	</TR>
</CFOUTPUT>
</TABLE>

When this code will be executed from browser, it will automatically export the report to the excel and ask to download the excel file or open it directly from source.

I hope this will help u.

Jigs

 

[greaseman]

And hello again, Pat.....you are right, regarding finding the number of rows and end of the data. What I'm endeavoring to do, is a loop within a loop, so to speak. I short, a do while nnot EOF, and within that, for each month's worth of data, a do while it's the same month. Then, when the month changes, do my subtotaling stuff, and at EOF, do my grand totaling stuff.

And yeah, bosses are kinda strange, aren't they? But, they probably think the same about us "geeks."

Thanks for your help..... I appreciate it.

 

[greaseman]

Jig, good morning! Thanks for your response, however, we are not usig Cold Fusion where I work and therefore, I cannot put your suggestion to use. I appreciate your willingness to help, though. Have a great day!