Run & Export Access Reports - How to Automate This?

Barber_91

Board Regular
Joined
Feb 13, 2004
Messages
72
I work in a hospital, and last week I created a report (in Access) for every "specialty" in the hospital. That means I have about 25 reports that need to run pretty much once a week.

Each "report" is exactly the same but uses a different query, that's how I seperate the departments/divisions out. I know what you're thinking...can't I just run one big report and sort by specialty.....well, not really because I have to send each specialty's report to them directly, so one area cant see another's report. I'd need a way to split it up if I did that.

Anyways, is there something I can do to automate the export of the query results to rich-text. Any WORD file would work, I just thought Rich Text was easiest.

If we get that far, then I can ask the next question about automating one more little thing

Thanks!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
Your report could have breaks in it at the Dept/Div changes, causing page breaks to keep the reports seperated.


You could create one or more macros to run the individual reports/queries/macros. This will also allow you to export (TransferText), but there are limits.

You could create a form that contained one or more buttons that when clicked, would run your reports/queries/macros.

You could create a form that when opened would run your reports/queries/macros.

You could set this form (last example) in Tools/StartUp/Display Form/Page. Then, when you open the .mdb, the form would open and run the reports/queries. Put a statement in the (assuming your running under Windows) scheduler and it will run automagically. PS... you need to add something to exit the program too.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Because you want to run a separate report for each Specialty, you could create a table of Specialties, then use this table in VBA to provide the criteria to your report that you want to export. Depending on where you are exporting the reports, like to a specific folder for each Specialty, then that folder path could be stored in the Specialty table. This way, your 25 reports would be done within one VBA module, and the reports would be distributed as they are created.
Just an idea I had.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top