HOWTO: Automate Printing of Access Reports to PDF files

Skip to first unread message

Jan 9, 2005, 2:20:54 AM1/9/05
This is the approach I used to automate printing of Microsoft Access
reports to PDF format i.e. unattended and without annoying "Save As..."
dialogs, and - more importantly - without having to use a commercial
program such as Adobe Acrobat and its associated API.

The technique uses Ghostscript and Redirection Port Monitor - two free
programs for creating PDF documents provided free by Russell Lang. The
actual automation requires VBA coding using the FileSystemObject,
WScript.Network (the Windows Script Host Network Object) and Access


1) Install Ghostscript
- An interpreter for the Postscript language and PDF

2) Install RedMon - Redirection Port Monitor
- Redirects a special printer port to a program (such as

3) Set up virtual PDF printer using Ghostscript and Redmon
- Here are two web pages that explain how to install the
above tools:

4) Configure the RedMon printer port used by the PDF virtual printer in
the following mannter:
- Output: "Program handles output"
- The new PDF file should always save to the same file i.e.

Use this for the "Program Arguments" setting:
@c:\gs\pdfconf.txt -sOutputFile="C:\temp\output.pdf" -c .setpdfwrite
-f -

(Note the literal filepath instead of the "%1")

5) Write your own Visual Basic code that prints the report to the pdf
and then uses FileSystemObject to copy it to a name/location of your
chosing. Use the WScript.Network object to change the default printer
from your usual default printer to the PDF printer and back again.

Example Code (Access Visual Basic):

Sub PrintReportToPDF(strReport as String, _
strOutputPath as String)

Const PDF_PRINTER as String = "PDF Printer"
Const ORIGINAL_PRINTER as String = "\\OFFICE\HP1320"
Const TEMP_PATH as String = "C:\temp\output.pdf"

Dim net as WScript.Network
Dim fso as Scripting.FileSystemObject

Set net = new WScript.Network
net.setDefaultPrinter PDF_PRINTER

DoCmd.OpenReport strReport

Set fso = New Scripting.FileSystemObject
fso.CopyFile TEMP_PATH, strOutputPath, True
fso.DeleteFile TEMP_PATH
Set fso = Nothing

net.setDefaultPrinter ORIGINAL_PRINTER
Set net = Nothing

End Sub

For the preceding code to work inside Access 2000, you have to add
references to Microsoft Scripting Runtime and Windows Script Host
Object Model.

My experience so far is that the subroutine DoCmd.OpenReport() finishes
outputting the report to PDF very quickly...however, if you have a very
large report the output.pdf might be there when the FileSystemObject
goes to move/rename it. Therefore, you might want to use the Windows
API Sleep() function.
See < >

Writing a routine that prints out multiple reports is left as an
exercise to the reader. Personally, I prefer to keep my reports in a
table called "Settings_Reports" rather than hardcoding the report names
into my VBA modules.


- Use DoCmd.SendObject() automate the emailing of the newly-created
reports to managers.

- Use the PDF Toolkit to merge disparate PDF reports into one file
This can also be automated using the VBA Shell() function, or in
VBScript via the WScript.Shell.Run() method

- Use VBScript to automate Access, just like Excel or Word.
See the following Microsoft KB article:
ACC: Using Microsoft Access as an Automation Server

Code fragment (VBScript):

dim acc
set acc = CreateObject("Access.Application")
with acc
.OpenCurrentDatabase "C:\Reports\Sales.mdb"

' Call the customer subroutine we defined earlier
.Run("PrintReportToPDF", "rptSalesFigures_Monthly", _
"N:\Marketing\Reports\SalesSummary" _
& Year(Date()) & Format(Month(Date()),"00")


Needless to say, there is a lot that can be done with the approach.
And the best part is that you don't have to learn another API (other
than the standard Windows Script Host and Microsoft Scripting Runtime
libraries, which you really should know anyway). And best of all it's
completely free!

Here are more detailed instructions for installing and configuring the
virtual PDF printer - cribbed from:
< >

1. Install Ghostscript to C:\

2. Create a text file (c:\gs\pdfconf.txt) and add the following text:


Note that the first line points to version 8.11 of Ghostscript. If
you have a different version or installed it to a different
location, make the appropriate changes.

3. Download, unpack, and Install RedMon (Redirection PortMonitor)
* When you run setup.exe you will only see a message box.

4. Go to Printers/Faxes menu under the Start button in Windows and
add a new printer.
* The driver for the printer must be for a /color postscript/
printer. I chose HP Color Laserjet 4550 PS
* Set the printer port to RPT1: (the redirected port)

5. Configure the port
Redirect Port to: "C:\gs\gs8.11\bin\gswin32c.exe"
(Update this line to reflect your version and location of
Ghostscript, if different)

Arguments for this program are:
@c:\gs\pdfconf.txt -sOutputFile="%1" -c .setpdfwrite -f -

Output: "Prompt for filename"
Run: "Normal"

6. Try printing something in color to this printer. If everything works
right, you will be prompted for a filename that the PDF file will be
saved under (Don't forget to add the .PDF extension).
Hope this helps someone!

Mike Turco

Jan 9, 2005, 5:37:38 AM1/9/05
I installed a free program called CutePDF. Its a printer driver, so you can
set it as the default printer for whatever you want to print. Pretty easy, I
think. You might want to check it out. (Oh, I'm not affiliated with the
company, I just think its a nice product, and its free.) -- Mike


Jan 9, 2005, 8:53:16 AM1/9/05
I do the same with

The VB code switches the default printer to this driver and back when

"Mike Turco" <> wrote in message

Darryl Kerkeslager

Jan 9, 2005, 10:06:45 AM1/9/05
According to their website, you still need to install Ghostscript as the PDF
engine. Previous versions of CutePDF included it, but it is now a separate

Darryl Kerkeslager


Jan 9, 2005, 10:58:37 AM1/9/05
Could you please share the code that switches the default printer to this

driver and back when finished.



"Saintor" <> wrote in message

Douglas J. Steele

Jan 9, 2005, 12:43:25 PM1/9/05
Check at "The Access Web", or
what Albert Kallal has at

Doug Steele, Microsoft Access MVP
(no e-mails, please!)

"Tom" <tste...@nospam.please> wrote in message

Jan 9, 2005, 12:46:34 PM1/9/05
Saintor wrote:
> I do the same with
> "Mike Turco" <> wrote:
> > I installed a free program called CutePDF. Its a printer driver, so
> > can set it as the default printer for whatever you want to print.

I also was using PDF995, which is exactly why I investigated this
solution. However, I wanted to produce my PDFs unattended. When you
have to produce dozens of reports for multiple departments, working
through 40+ "Save As..." dialogs can get a bit tedious (not to mention
error-prone). If you then have to *email* these reports out (or copy
them to shared folders...or whatever) - well, more tedium. Hence,
Ghostscript/Redmon/VBA => Less manual work.

Once you write a VBScript that creates the PDF reports, and use the
Windows Task Scheduler to make the script run overnight, you suddenly
have a lot more time for web surfing ;^)

Someone else also recommended PDFCreator
< >
However, this program - like CutePDF - uses ghostscript. Why not take a
shortcut and do it yourself?

For those who own a copy of Adobe Acrobat, you can also program the
Acrobat API to create PDFs. However, I believe the approach I posted
is easier, not to mention free.

Jan 9, 2005, 1:17:26 PM1/9/05
Tom wrote:
> Could you please share the code that switches the default printer to
> this driver and back when finished.

One approach is to use multiple Win32 API functions to accomplish this
( Fortunately, Microsoft has generously provided the Windows
Script Host object model. Read about it here:

< >

The object we are interested in is the WshNetwork object aka
"WScript.Network" and its handy SetDefaultPrinter method

Sample code (VBScript) that prints an Access report to two different

Const REPORTS_MDB = "C:\reports\Sales.mdb"
Const SALES_REPORT = "rptMonthlySalesSummary"
Const LOCAL_PRINTER = "HP1200"

dim net
dim acc

set acc = CreateObject("Access.Application")

set net = CreateObject("WScript.Network")


(The above code is untested. Read the documentation and code examples
on MSDN and elsewhere before using it.)

I suppose to make this code better, the WScript.Network object should
read the current default printer, switch to the temporary printer for
printing, and then switch back to the original printer. However, I
know my printers in advance and they have been the same for the last 3
years, so I have no problem with hard-coding them into my script.

But what if you have 20 different regional sales managers each with
their own printer? Rather than hardcoding them into my VBScript, I
could also create a table (in Access, or a CSV file, or whatever) that
contains the following fields

Database | Report Name | Printer

The VBScript code would load this table into a recordset and loop
through it to print out the reports.


Jan 9, 2005, 7:22:54 PM1/9/05

> I also was using PDF995, which is exactly why I investigated this
> solution. However, I wanted to produce my PDFs unattended. When you
> have to produce dozens of reports for multiple departments, working
> through 40+ "Save As..." dialogs can get a bit tedious (not to mention
> error-prone).

Well you don't need to.

Also, since recently, I use the sub 'OutlookSendMessage' easy to get on
internet to email my PDF reports. All automatic with Windows Task Scheduler
of course.


Jan 9, 2005, 11:16:46 PM1/9/05
No offense, but Win32 API programming == yuck. Copy & pasting Win32
API code == double-yuck.

See commandment #8
< >

Why puzzle over the inner workings of Windows API functions when
Microsoft has provided WScript.Network and FileSystemObject?
Apparently, even *they* don't like Windows API programming, or else
they wouldn't have provided these tools.

Consider the following lines of VB code:

WshNet.SetDefaultPrinter "PDF Printer"
AccApp.DoCmd.OpenReport "rptSales"
fsObj.CopyFile "C:\temp\output.pdf","N:\marketing\SalesQ1.pdf",true
WshNet.SetDefaultPrinter "HP1200"

Even someone who doesn't know about the Windows Script Host or File
System Object (and possibly someone who doesn't know how to program)
can grasp immediately what the above code does. That is its advantage.

Of course, you first have to install Ghostscript and RedMon, and then
set up a virtual PDF printer. This task consists essentially of
un-zipping files onto your hard drive, installing a local printer and
entering a single line of code into the new printer's "Configure Port"
dialog. It takes less than five minutes and no new technical knowledge
(if you code Access databases for a living, you know how to install a
windows printer). From start-to-finish: five minutes. And it's free.

Of course, everyone should find the approach that works best for them.

- CyranoVR

PS - OutlookSendMessage isn't going to work for everyone (my employer
uses Novell Groupwise). The advantage of SendObject is that it works
with any MAPI-compliant email client.


Jan 10, 2005, 10:55:20 AM1/10/05
Saintor wrote:
> >
> Well you don't need to.

Something interesting I learned about PDF995 from looking at that bit
of code:

PDF995 creates its PDFs using...GhostScript.

Contents of the file C:\PDF995\res\pdf995.bat

chdir c:\pdf995\res\convert
gswin32c.exe -sDEVICE=pdfwrite -q -dPDFSETTINGS=/prepress
-dCompatibilityLevel=1.3 -dNOPAUSE -dBATCH -sOutputFile="[SOME FILENAME
HERE]" -c save pop -f "c:\pdf995\"

If you didn't know, "gswin32c.exe" is the GhostScript executable.

Aside from using GhostScript, I noticed that PDF995 installs an
unconfigurable printer port called "PDF995 Port." This can only be
their version of RedMon. So it appears that they are using free tools
for their software and charging $9.95 a pop...except that the end user
can't configure your Redirected Monitor port like you can if you use
the pure GhostScript/RedMon solution...among other things.

We can conclude that PDF995 works by dynamically creating &/or editing
the pdf995.bat and *.ini files with each printing session. However, I
should mention that the linked VBA code, which attempts to modify the
PDF995 *.ini files itself, didn't work for me (using Windows XP/Acc2K).
This is *probably* because GetPrivateProfileString() changed for
Windows XP (see next paragraph), or perhaps because I am using the
"sponsored" version of PDF995.

Now at this point, if I wanted the to work, I would have to spend an
unknown amount of time researching GetPrivateProfileString(), trying to
figure out how it changed for Windows XP. I went through something
similar several months back, trying to get my wrapper class for
GetOpenFileName() to work after I upgraded to XP. Not a lot of fun.

So for those reading this thread who are still trying to decide which
solution to persue, I still recommend: GhostScript + RedMon +
FileScriptingObject + WScript.Network.SetDefaultPrinter()

If you use a solution like PDF995, you'll actually be doing the exact
same solution as the one I describe...except you'll be paying $10 for
it and puzzling over Win32 API calls to get it to work.

Message has been deleted

Feb 25, 2005, 2:46:53 AM2/25/05
Access 2003 has the option to send PDF to email. I don't have Outlook
installed, but when I selected the option, it created a PDF file.

How do I access that function to save as PDF programmatically? If
Access 2003 can create a pdf file for email, surely there is a way to
just create a pdf file

Message has been deleted


Feb 25, 2005, 3:30:54 PM2/25/05
I find this an interesting juxtaposition - reading this thread
via Google, I see the following note at the top:

Note: The author of this message requested that it not be archived.
This message will be removed from Groups in 6 days (Mar 4, 10:18 am).

And then in the post itself, Greg Strong wrote:
> If you do not have the original posting to this thread, then
> see Google's archive at
[snip path]

Do as I say, not as I do? Or are people not aware that they have
X-No-Archive (or whatever that flag is) set?

Martha Palotay
don't google to email

Greg Strong

Feb 25, 2005, 5:28:03 PM2/25/05
On 25 Feb 2005 12:30:54 -0800, "Martha" <> wrote:

>Do as I say, not as I do? Or are people not aware that they have
>X-No-Archive (or whatever that flag is) set?

No I wasn't aware of the "X-No-Archive: yes" header being set, or at least I
don't recall setting it. No disrespect intended.


Greg Strong

Feb 26, 2005, 7:23:55 AM2/26/05
Yes, I did read the whole thread.

My question is how to access Access 2003's ability to create pdfs.

I have none of the above tools installed in my computer, yet when I
select File | Send to | Send PDF in email, Access 2003 is able to
create a PDF file.


Feb 27, 2005, 12:47:58 PM2/27/05
You must have something extra installed. I just checked my version of
AC2003 and that option isn't there. There is an e-mail w/ attachment
option, but if I select that I get options like Text, Rich Text, Excel,
etc but no PDF.


Steve Jorgensen

Feb 27, 2005, 2:28:52 PM2/27/05
Ghostscript with Redmon works great. I've used it before, but some of my
clients have found the PDF995 product to be easier, and still vastly more
affordable than the Adobe PDF applications.

As for making sure the file exists before you try to copy it, it turns out
that placing a DoEvents call right after the DoCmd.OpenReport will cause
Access to wait until the entire report is done printing before continuing to
the next statement.

Reply all
Reply to author
0 new messages