automate printing of existing MS Access report (preferably with Python, but open to alternatives)

Question

I have about 30 .accdb files, all contain a report with the same name. I'd like to automate saving the reports as PDFs for subsequent printing. That's all, no queries, no modification, just print the saved report to a PDF.

I can open the db successfully, but I'm not sure how to do the rest.

import win32com.client

a = win32com.client.Dispatch("Access.Application")
a.visible = 1  
filename = r'C:\bla\Exam_1\PrintTest\db.accdb'
db = a.OpenCurrentDatabase(filename)

report_name = 'My_Report'

a.Quit()

FWIW, I'm a teacher, and this will help me with grading submissions and take the burden off stressed students printing at the end of an exam. Using Windows 10.

I'm open to using any other scripting language (powershell?) that will help me easily automate this task.

Erik A · Accepted Answer · 2018-10-16T15:13:47.250

4

You can use DoCmd.OpenReport to automatically open and print reports.

import win32com.client

a = win32com.client.Dispatch("Access.Application")
a.visible = 1  
filename = r'C:\bla\Exam_1\PrintTest\db.accdb'
db = a.OpenCurrentDatabase(filename)

report_name = 'My_Report'
a.DoCmd.OpenReport('My_Report')
a.Quit()

By default, DoCmd.OpenReport will print the report to the printer specified in the report settings. I have code lying around to open it in print preview and specify a printer as well.

If you want to export to a PDF, you can automate that as well:

import win32com.client

a = win32com.client.Dispatch("Access.Application")
a.visible = 1  
filename = r'C:\bla\Exam_1\PrintTest\db.accdb'
db = a.OpenCurrentDatabase(filename)

report_name = 'My_Report'
a.DoCmd.OutputTo(3, report_name, r'PDF Format (*.pdf)', r'C:\Path\To\file.pdf')
a.Quit()

edited Oct 16 '18 at 15:13

answered Oct 16 '18 at 14:58

Erik A

31,639
12
42
67

Wow .. this is fantastic. Can I ask what references you use for this sort of information? I have looked without luck all over. I recently had a similar query re Excel https://stackoverflow.com/questions/52601717/show-formulas-in-excel-using-python-win32com and while I was able to cobble together a workable solution, it took a long time - plus I'm not sure it was the best way. – Levon Oct 16 '18 at 15:24
1

@Levon Mostly [Microsoft Docs](https://learn.microsoft.com/en-us/office/vba/api/access.docmd.outputto) + the object browser from VBA. Having the VBA editor open with the immediate window is almost a must for working with Office COM objects, that way you can find out that the Access `acFormatPDF` constant is `'PDF Format (*.pdf)'`, for example. – Erik A Oct 16 '18 at 15:33
for instance, how could I get the names of all reports in a given database, or show the names of the queries, that sort of stuff .. love automating repetitive tasks with Python if I can - I just have found the win32com documentation needed to interact with Access/Excel difficult. – Levon Oct 16 '18 at 15:42
Ah, ok, I haven't worked with VBA too extensively, but I think I know what you are referring to. I will try to take a look and dig into this more. – Levon Oct 16 '18 at 15:43
1

Well, you can iterate over the `Application.Reports` collection, and then print the `Name` property for each report in there. Queries are in the QueryDefs collection of the database object, but be aware that contains invisible temporary queries. The VBA editor is just a really easy way to get familiar with these objects and collections since you have intellisense, the locals window which shows object properties and their values, and you can quickly test on the currently open database and application – Erik A Oct 16 '18 at 15:50
Erik A thanks for the great solution! I'm having an issue where if I use a.DoCmd.OpenReport('My_Report') it uses my default printer (which is what I want), but I have to navigate to the file path and click save for every report(not ideal). However, if I use a.DoCmd.OutputTo(3, report_name, r'PDF Format (*.pdf)', r'C:\Path\To\file.pdf') this doesn't use my default printer. Is there a way to use a.DoCmd.OpenReport and set the file path and have it save automatically. OR is there a way to use a.DoCmd.OutputTo and tell it to use the default printer? – Jenna Allen Sep 17 '20 at 00:10
@Jenna `DoCmd.OutputTo` doesn't use a printer at all, it uses built-in save as PDF functionality. As far as I know, there's no way to specify the file location a printer will use in Access. Your printer might offer the option to specify the file path ahead of printing, in which case you could modify this using Python, but the default Microsoft ones do not have such an option. – Erik A Sep 17 '20 at 06:13
@ErikA ok, thanks for that. Do you know if there is a way to automatically put in the file path and perform a button click (click save) in the python script when the 'save as' dialogue pops up? I tried using different things like DoCmd.SetProperty(10, full_path) and a.Reports(report).myButton_Click(), but that didn't work. – Jenna Allen Sep 17 '20 at 17:54
@Jenna That's an entirely separate question that's unrelated to Access, as the printer driver is popping up that dialog (and, while possible, somewhat complicated). You can read into GUI automation using Python. – Erik A Sep 17 '20 at 19:12
@ErikA I really appreciate the dialogue on this. If only `DoCmd.OutputTo` would embed fonts in the PDF, my life would be a lot easier. But alas, to google I go. THANKS! – Jenna Allen Sep 17 '20 at 21:52

automate printing of existing MS Access report (preferably with Python, but open to alternatives)

1 Answers1

Linked