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 FileSystemObjec t,
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.pd f" -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 FileSystemObjec t 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 PrintReportToPD F(strReport as String, _
strOutputPath as String)
Const PDF_PRINTER as String = "PDF Printer"
Const ORIGINAL_PRINTE R as String = "\\OFFICE\HP132 0"
Const TEMP_PATH as String = "C:\temp\output .pdf"
Dim net as WScript.Network
Dim fso as Scripting.FileS ystemObject
Set net = new WScript.Network
net.setDefaultP rinter PDF_PRINTER
DoCmd.OpenRepor t strReport
Set fso = New Scripting.FileS ystemObject
fso.CopyFile TEMP_PATH, strOutputPath, True
fso.DeleteFile TEMP_PATH
Set fso = Nothing
net.setDefaultP rinter ORIGINAL_PRINTE R
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.OpenRepor t() finishes
outputting the report to PDF very quickly...howev er, if you have a very
large report the output.pdf might be there when the FileSystemObjec t
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_Repor ts" rather than hardcoding the report names
into my VBA modules.
ADDITIONAL TIPS:
- Use DoCmd.SendObjec t() 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.R un() 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("A ccess.Applicati on")
with acc
..OpenCurrentDa tabase "C:\Reports\Sal es.mdb"
' Call the customer subroutine we defined earlier
..Run("PrintRep ortToPDF", "rptSalesFigure s_Monthly", _
"N:\Marketing\R eports\SalesSum mary" _
& Year(Date()) & Format(Month(Da te()),"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\l ib;c:\gs\fonts
-sDEVICE=pdfwrit e
-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\b in\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
