Access Reports to Excel

[BamaColtsFan]

I'm just wondering if anyone has figured a good way to send an Access report to Excel and hold all the formatting? I know that feature was nerfed by Microsoft in Access 2007 but I'm trying find some way to take all the lovely formatting I do for my reports in Access and send them out to a file that I can distribute. I have several reports that do fairly complex calculations that I really can't do in a query format (dynamic totals, groupings, etc.) but I really need to send to other people. As it is, I can output the report as a pfd file and it retains formatting but that means I have to send several large files. What I would like to do is get all the reports to go into a single Excel file using VBA (with all the cool formatting) and just send that one.

Any ideas are appreciated!

THNX

 

[gfultz]

I typically setup excel templates and then send the data to excel and fill in the template. You can use the excel vba references to do just about anything with data in access. In one db, I have a form setup where the user can pick the report they want to run and populate criteria to have it kick to excel in the background and then appear when it is done.

You can do pretty much everything with vba, including setting print areas to be sure you have it print out your whole report. This process takes some trial and error to work, but it can be done efficiently.

Additionally, a lot of the things you want excel to do can be figured out by recording a macro in excel and then editing it to see the code.

Other than that, I haven't been able to kick anything to excel that looked pretty.

 

[boblarson]

Keeping a report's formatting while going to Excel is just not something that works. The problem lies in that Excel is not geared to handle the formatted output of a report and when exporting a report to Excel, it has to try to use cells where it doesn't have them in the report.

That is why a save to RTF or PDF works much better for having the format of the report.

As gfultz has said, using a template can be one option but then you need to use some code to save it to the locations you want as it isn't likely going to be in just the right place. Getting a good, formatted report in Excel is going to take some coding anyway. I have done some up that take a lot of code to format it just the way they wanted it. But it works. Using Excel objects to do all of this is really not that hard and as noted again by gfultz you can record a macro in Excel for something you don't know how to do and then you can pretty much modify it to work in Access. But you should read this first if you are going to be doing any coding to Excel from Access.

 

[gfultz]

Good point on the instances reference Bob! My first endeavor lead me down that lonely road...

Like Bob said, be prepared to code this. For the templates, I wrote code that runs a batch file when the app is first opened and copies the templates to the directory I have installed the app to. That way, the user always has the latest templates and they are always in the same place as the user has no control of the installation directory.