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,
cyranoVR@gmail.com wrote:
[color=blue]
>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!
>
>CyranoVR@gmail.com[/color]
