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
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
(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
Set fso = New Scripting.FileSystemObject
fso.CopyFile TEMP_PATH, strOutputPath, True
Set fso = Nothing
Set net = Nothing
For the preceding code to work inside Access 2000, you have to add
references to Microsoft Scripting Runtime and Windows Script Host
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 < http://support.microsoft.com/kb/q162150/ >
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):
set acc = CreateObject("Access.Application")
' Call the customer subroutine we defined earlier
.Run("PrintReportToPDF", "rptSalesFigures_Monthly", _
& 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
SETTING UP THE PDF PRINTER:
Here are more detailed instructions for installing and configuring the
virtual PDF printer - cribbed from:
< http://masterdev.dyndns.dk/know/freepdf.html >
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"
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!
"Saintor" <sain...@REMOVETHIShotmail.com> wrote in message
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
"Tom" <tste...@nospam.please> wrote in message
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
< http://sector7g.wurzel6.de/pdfcreator/index_en.htm >
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.
One approach is to use multiple Win32 API functions to accomplish this
(ugh...no). 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"
Const REMOTE_PRINTER = "\\OFFICE\HP1320"
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.
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
See commandment #8
< http://www.mvps.org/access/tencommandments.htm >
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"
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.
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.
Something interesting I learned about PDF995 from looking at that bit
PDF995 creates its PDFs using...GhostScript.
Contents of the file C:\PDF995\res\pdf995.bat
gswin32c.exe -sDEVICE=pdfwrite -q -dPDFSETTINGS=/prepress
-dCompatibilityLevel=1.3 -dNOPAUSE -dBATCH -sOutputFile="[SOME FILENAME
HERE]" -c save pop -f "c:\pdf995\temp.ps"
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.
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
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
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?
don't google to email
>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.
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.
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.