Prettier export from Access Report to Excel

[CoreyReynolds]

Hello (again),

I quickly want to preface this by saying you all really have no idea how much I appreciate all the help I have received from this forum. You really are all just the best kind of people and are helping me more than you'll ever realize. Thank you!

I have a report that I have tried using OutputTo to export to excel:

DoCmd.OutputTo acOutputReport, "rptHourlyPercentage", acFormatXLS, , True

Which is fine and all, but the way the report is setup it's ugly and almost un readable. These is basically going to be used by extremely computer illiterate "higher-ups" and that format just isn't going to do. In a bout of frustration I just hit Control A, Copy and pasted it into a blank excel document and WOW! Perfect! Exactly what I need.

I can't find it anywhere but... oh god... someone please tell me there is a procedural way to do this in VBA, but telling them to select all and copy paste isn't going to fly. I know I know.

Or even sending me to a place with more information.. I'm just failing at my googling here. Even if there is a way to format that "OutputTo" data before it hits the screen would be an OK last resort.

[Jim Doherty]

@CoreyReynolds
Which version Access are you using?

An Access report can have all sorts of formatting applied to it, lines,calculations,pagebreaks,groupings colours and so on. To output a sophisticated report and expect it to retain all formats in excel is a non starter. You have several options here:-

If your report is essentially just a tabular display namely columns and rows you could effectively miss out the report entirely and transfer the results of a dataset (table or a query) immediately to Excel.

The DoCmd.TransferSpreadsheet action sends table/query in a fixed format to excel.

You could get into Office automation and can control aspects of the spreadsheet layout when your dataset is sent over to excel ie: range positioning, formatting data, colouring column headers, bolding fonts, freezing panes and so on, This requires considerable effort and understanding of the mechanics of the Excel object library which you would ordinarily set a reference to within Access.

If simplicity is the aim for management you could easily output your existing report as a snapshot file. These file types have an .snp extension and are viewable using snapshot viewer which does come with Office. Be wary though, depending on the office installation it might not have been included for installation on the target PC as it is not part of the default installation process.

The upside of SNP files is that your management can view the report exactly as if they were viewing an Access report and you can also email the file itself. The downside is the rather irritating lack of not being part of the default 'Office' installation and thus some recipients inevitably say "Hey I cannot read this file!"

3) You have an option to output a report to PDF in Access 2007. (you can do this in earlier versions of Access and for free using Stephen Lebans rather excellent code module that he wrote for converting SNP files to PDF automatically. Setting this up does require some VBA knowledge but he has documented the process) or you can buy a third party piece of software to do the job. Most desktops nowadays have a PDF viewer.


Regards

[CoreyReynolds]

Thank you for the detailed reply. I had no idea about snapshots and I'll take a look at that in the future. I am using Access 2007

They do from time to time want to do things with numbers themselves... so a PDF or snapshot might not be the best possible solution. Selecting everything and copy and pasting actually leaves it in a reasonable format that I think I could just leave as is, to bad it doesn't like like there is really a way to do that (correct me if I'm wrong). Guess I'll have to reproduce it in VBA manually, which doesn't look like too much fun but - Oh Well!

Although it doesn't help me much here, there's a few other projects I'm working on where those snapshots might come in handy.

Thanks,
Corey

[Jim Doherty]

@CoreyReynolds
Yes Corey,,,I am afraid it pretty much depends on what it is you are doing your end. The snapshot and PDF thing is no good like you say if you are number crunching and passing material data to and fro heavily integrating the physical data useage between apps.

It is a matter of strategy as I am sure you already appreciate. If you are using Access 2007 then I would dump the snp thing as pdfs are much more functional nowadays

To answer your question ......You are right there is no way to just say copy, paste, hey presto exactly the same editable data report in Excel as appears graphically in the Access
report window

[CoreyReynolds]

Well, we'll just leave it at that and close the topic with hopes that some day they finally come out with the magic VBA fairies that make data input and output seamless, GUI based, flexible and simple to use.

[Jim Doherty]

@CoreyReynolds
Haha absolutely :)

[NeoPa]

Using Application Automation it's perfectly possible to duplicate the process of copying your report and pasting it into an Excel spreadsheet.

If you're interested in taking this further then give it a try and come back with specific questions if you get stuck.