By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,362 Members | 3,489 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,362 IT Pros & Developers. It's quick & easy.

HOWTO: Automate Printing of Access Reports to PDF files

P: n/a
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
automation.
INSTRUCTIONS:

1) Install Ghostscript
- An interpreter for the Postscript language and PDF
http://www.cs.wisc.edu/~ghost/

2) Install RedMon - Redirection Port Monitor
- Redirects a special printer port to a program (such as
Ghostscript)
http://www.cs.wisc.edu/~ghost/redmon/

3) Set up virtual PDF printer using Ghostscript and Redmon
- Here are two web pages that explain how to install the
above tools:
http://masterdev.dyndns.dk/know/freepdf.html
http://stat.tamu.edu/~henrik/GSWriter/GSWriter.html

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.
C:\temp\output.pdf

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 < 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.
ADDITIONAL TIPS:

- 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
URL: http://www.accesspdf.com/pdftk
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
http://support.microsoft.com/kb/q147816/

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")

[Etc...]

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!
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:\
<http://www.ghostscript.com/doc/AFPL/index.htm>

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

-Ic:\gs\gs8.11\lib;c:\gs\fonts
-sDEVICE=pdfwrite
-dNOPAUSE
-dSAFER

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)
<http://www.cs.wisc.edu/~ghost/redmon/index.htm>
* 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!

Cy******@gmail.com

Nov 13 '05 #1
Share this Question
Share on Google+
16 Replies


P: n/a
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
Nov 13 '05 #2

P: n/a
I do the same with www.pdf995.com

The VB code switches the default printer to this driver and back when
finished.
"Mike Turco" <mi*******@yahoo-nospam4me.com> wrote in message
news:aQ7Ed.26167$Q%4.22751@fed1read06...
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

Nov 13 '05 #3

P: n/a
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
install.

Darryl Kerkeslager
"Mike Turco" <mi*******@yahoo-nospam4me.com> wrote:
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

Nov 13 '05 #4

P: n/a
Tom
Could you please share the code that switches the default printer to this
driver and back when finished.

Thanks!

Tom

"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:kH*******************@wagner.videotron.net...
I do the same with www.pdf995.com

The VB code switches the default printer to this driver and back when
finished.
"Mike Turco" <mi*******@yahoo-nospam4me.com> wrote in message
news:aQ7Ed.26167$Q%4.22751@fed1read06...
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


Nov 13 '05 #5

P: n/a
Check http://www.mvps.org/access/reports/rpt0009.htm at "The Access Web", or
what Albert Kallal has at
http://www.members.shaw.ca/AlbertKal.../msaccess.html
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Tom" <ts******@nospam.please> wrote in message
news:Nw****************@newsread3.news.pas.earthli nk.net...
Could you please share the code that switches the default printer to this
driver and back when finished.

Thanks!

Tom

"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:kH*******************@wagner.videotron.net...
I do the same with www.pdf995.com

The VB code switches the default printer to this driver and back when
finished.
"Mike Turco" <mi*******@yahoo-nospam4me.com> wrote in message
news:aQ7Ed.26167$Q%4.22751@fed1read06...
> 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
>
>



Nov 13 '05 #6

P: n/a
Saintor wrote:
I do the same with www.pdf995.com

"Mike Turco" <mi*******@yahoo-nospam4me.com> wrote:
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.


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.

Nov 13 '05 #7

P: n/a
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
(ugh...no). Fortunately, Microsoft has generously provided the Windows
Script Host object model. Read about it here:

< http://msdn.microsoft.com/scripting/windowshost >

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

http://msdn.microsoft.com/library/en...ultprinter.asp

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

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

dim net
dim acc

set acc = CreateObject("Access.Application")
acc.OpenCurrentDatabase(REPORTS_MDB)
set net = CreateObject("WScript.Network")
net.SetDefaultPrinter(REMOTE_PRINTER)
acc.Docmd.OpenReport(SALES_REPORT)
net.SetDefaultPrinter(LOCAL_PRINTER)
acc.DoCmd.OpenReport(SALES_REPORT)

[etc.]

(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.

Nov 13 '05 #8

P: n/a

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.
http://www.freeware995.com/misc/vbacode.txt

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.
Nov 13 '05 #9

P: n/a
No offense, but Win32 API programming == yuck. Copy & pasting Win32
API code == double-yuck.

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"
AccApp.DoCmd.OpenReport "rptSales"
fsObj.CopyFile "C:\temp\output.pdf","N:\marketing\SalesQ1.pdf",tr ue
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.

Nov 13 '05 #10

P: n/a
Saintor wrote:

Well you don't need to.
http://www.freeware995.com/misc/vbacode.txt


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\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.

Nov 13 '05 #11

P: n/a
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

Nov 13 '05 #12

P: n/a
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

Nov 13 '05 #13

P: n/a
On 25 Feb 2005 12:30:54 -0800, "Martha" <mp************@yahoo.com> 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.

--
Regards,

Greg Strong
Nov 13 '05 #14

P: n/a
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.

Nov 13 '05 #15

P: n/a
Bri
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.

--
Bri

So******@gmail.com wrote:
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.


Nov 13 '05 #16

P: n/a
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.

On 8 Jan 2005 23:20:54 -0800, cy******@gmail.com wrote:
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
automation.
INSTRUCTIONS:

1) Install Ghostscript
- An interpreter for the Postscript language and PDF
http://www.cs.wisc.edu/~ghost/

2) Install RedMon - Redirection Port Monitor
- Redirects a special printer port to a program (such as
Ghostscript)
http://www.cs.wisc.edu/~ghost/redmon/

3) Set up virtual PDF printer using Ghostscript and Redmon
- Here are two web pages that explain how to install the
above tools:
http://masterdev.dyndns.dk/know/freepdf.html
http://stat.tamu.edu/~henrik/GSWriter/GSWriter.html

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.
C:\temp\output.pdf

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 < 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.
ADDITIONAL TIPS:

- 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
URL: http://www.accesspdf.com/pdftk
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
http://support.microsoft.com/kb/q147816/

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")

[Etc...]

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!
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:\
<http://www.ghostscript.com/doc/AFPL/index.htm>

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

-Ic:\gs\gs8.11\lib;c:\gs\fonts
-sDEVICE=pdfwrite
-dNOPAUSE
-dSAFER

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)
<http://www.cs.wisc.edu/~ghost/redmon/index.htm>
* 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!

Cy******@gmail.com


Nov 13 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.