Export Access Report to Excel w/ Formatting

[Onyxpurr]

Is there an easy way to export my access report (with totals located in report footer) into excel?

I have a offsite user that needs the report totals but wants to be able to sort the data in excel. I could manually format, but want to give the ability to the person generating the report and sending it to the offsite user "click of a button" ease.

 

[AccessAce]

Create a form for the user that has two button on it. One button will display the report to the User, the other will open (or send ot Excel) a query with the report data in it.

 

[winnechan]

i also have the same question.
can u specify the steps and the coding. im very new to VBA

 

[Onyxpurr]

Thanks anyway, but I want to export the totals to excel, not display in a seperate report. The user can't access the database.

 

[saustin]

Hey Onyxpurr, have you resolved the totals issue yet ? Have been playing around with excel macros but to no avail. Many thanks for any help ! Steve.

 

[Onyxpurr]

No I haven't but if I find out will let you know.

 

[WILLIEWANKA]

You could always create a query that has the totals and then create a command button that will export that query to excel such as :


create a command button and name it cmd_export_totals. You can name the button by clicking view>properties and then typing the name in the the NAME box of the other tab.

Then go to the event tab and click the three dots next to the on click event. Then in the choose builder box, select code builder.

Then you can type in the following code



On Error GoTo Err_cmd_export_totals

DoCmd.OutputTo acOutputQuery, "YOURQUERYNAME", acFormatXLS, , True

EXIT_cmd_export_totals_Click:
Exit Sub

Err_EXIT_Click:
MsgBox Err.Description
Resume Err_EXIT_Click



If you have any questions about it, you can always look it up in the help file under OUTPUTTO Method or Action.

Or if this doesn't help let me know and i'll see if I can give you another way of doing it.



Bill

 

[Onyxpurr]

I am looking for a way to be able to export details and totals alike easily. Not just the totals.

Thanks anyway!

 

[MichaelRed]

I know of no way to get Ms. A. to export to much of anything "easily" -and save the formatting. So, if this is your only option, STOP.


Otherwie, there are some PARTIAL soloutions.

One is to export to Word (.RTF) format. This looses only the graphics, and provides the reciepient with a manipulatable version of the info, albeit not quite the degree of flexability of Excel. The end users can, however, simply copy the resultant word 'table' into excel and 'have their way' with the data (including sorts, changing values, deleting records, ... changing formats, etc.

The resulting (.RTF) document retains much of the overall appearance of your original report, loosing only lines, boxes, shading, and (of course) any (and ALL) graphics (logos, Charts, etc.). Further, htese can actually be directly e-mailed as part of a procedure, so there is actually very little additional work during the production process, only in the development process.

Another, and a LOT more ambitious approach, would be to re-create the report appearance in Excel, using the forms capabillity, and populate the Excel data series used by the form in some vbcode / module in Ms. A.

I have never done this, so cannot give any advice on details but can say that it seems 'tedious' -at best- and the form would need to use named ranges for the data, and hte data itself would need to be exported tothe named range. I also (obviously?) do not know if Excel Forms would support all of the necessary features (sub-totals on groups?).

Finally, I would suggest some 'earnest' discussion with the 'chain of command'. If the report -as developed- is what was ordered, why does 'User X' get to have so much individual attention? Having the "source" data in Excel is ONE thing, having it in Excel AND in the same format as it appears in Ms. A. Reports WHIILE in Excel is SEVFERAL (or more) additional things. Who is this "800# APE" that they are SO (*&^(^%*^ IMPORTANT that -for them alone- you need to do the hoop jump thingggyyyyy?

Note:
[tab]Must be in Excel
[tab]Must "look Like" Ms. Access
[tab]CANNOT use Ms. Access
[tab]WILL not accept just the data in Excel (even Seperatly)

My 'knee jerk' reaction is ~~ "Who is 'the jerk'?"

(and -conversly- "who is this "jerkee"?)






MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Samulayo]

Does the output HAVE to be in access, if the guy just wants to view the output outside of access, then why dont you create a report from the required query, make a toolbar with an save as button, and save the report as a snapshot. this file can then be viewed using microsoft's snapshot viewer (check microsoft.com's download area)

Regards

Samulayo

 

[Onyxpurr]

Yes, I'm familiar with and specialize in "special" requests. If I didn't, I wouldn't have the job I do.

Anyway, little more background. The report shows the quality percentages from our audits. Each employee receives a partcular audit percentage for the month. The report includes individual, team, and office totals.

The end-user is not excel or access savy in addition to off location, so can not easily get to this access database.

This person would have no clue how to create the correct formulas for the overall quality percentage. However, I do have a temporary fix in that I output the detail to excel and then copy the formulas from an old spreadsheet into the current one (and then redo some formatting and cell references if needed).

If I have to leave it at that then so be it, but I would like to try to make this process as easy as possible because I am not the one sending the reports, our executive secretary is.

Thanks!!

 

[MichaelRed]

Well, i'll "bow out". Giving someone who is conversant with neither Access or Excel data in it's "Raw" form and attempting to create the tools (formulas) in Excel for them to calculate values which you could just as easily (?) calculate in Ms. A. (calculated fields?) and send to them seems to me to making a LOT of extra effort to go "the long way 'round the barn" - and probably ending up with more 'service calls' anyway.


My parting suggestion is to create the opportunity to do 'ye olde' face to face encounter with the individual and discuss -IN DEPTH- what their aims/goals/objectives are in the manipulation. Form the perspective of the "KNOWLEDGE" of the issue(s), you might be in a position to "Can" at least the majority of the info for them, or creqte a seperate app (in either Excel or Ms. A.) which suits their needs -w/o the etrernal necessity of service calls.


Having done a quality assessment database on personnel performance, I realize that the material is quite sensitive, so I would also offer a caution. These are often (usuall/. always?) used in personnel actions, so the data itself needs to be THOROUGHLY validated, and all employees being evaluated should be advised of the specific "business rules" of the evaluation. Whomever is using the "raw" data needs to be careful of the calculations -which are not available to the personnel being evaluated. Impropper use or disemeniation of the data -or any "ad-hoc" calculations based on the data may be grounds for legal action - particularly if some action is based on the data / calculations which the employee is not aware of PRIOR to the evaluation.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places