vba 将报告保存为 pdf

saving reports as pdf

提问by Pedro

Currently I am using pdfcreator to convert reports into pdf format. As this has to be done manually I am looking for a solution to automatically save reports as pdf files. The code should be working in access 2000 and newer versions.

目前我正在使用 pdfcreator 将报告转换为 pdf 格式。由于这必须手动完成，我正在寻找一种解决方案来自动将报告保存为 pdf 文件。该代码应该在 access 2000 和更新版本中工作。

回答by Christian Specht

We are using Stephen Lebans' ReportToPDFto generate PDF files.
We use Access 2003, and it works for A2000, 2002 and 2003 (A2007 has PDF support out-of-the-box).

我们正在使用Stephen Lebans 的 ReportToPDF来生成 PDF 文件。
我们使用 Access 2003，它适用于 A2000、2002 和 2003（A2007 具有开箱即用的 PDF 支持）。

It consists of just two DLLs that need to be in your application folder or the %windir%\system32folder, and they don't need to be registered.

它只包含两个 DLL，它们需要位于您的应用程序文件夹或%windir%\system32文件夹中，并且不需要注册。

We preferred using this solution instead of a PDF printer because just copying two files is easier than installing a PDF printer on every machine.

我们更喜欢使用此解决方案而不是 PDF 打印机，因为仅复制两个文件比在每台机器上安装 PDF 打印机更容易。

回答by Code Magician

I've used this approach before: http://bytes.com/topic/access/answers/204362-howto-automate-printing-access-reports-pdf-files

我以前使用过这种方法：http: //bytes.com/topic/access/answers/204362-howto-automate-printing-access-reports-pdf-files

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.

这是我用来将 Microsoft Access 报告自动打印为 PDF 格式的方法，即无人值守且没有烦人的“另存为...”对话框，而且 - 更重要的是 - 无需使用商业程序，例如 Adob​​e Acrobat 及其相关 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.

该技术使用了 Ghostscript 和重定向端口监视器——两个由 Russell Lang 免费提供的用于创建 PDF 文档的免费程序。实际的自动化需要使用 FileSystemObject、WScript.Network（Windows 脚本主机网络对象）和 Access 自动化进行 VBA 编码。

INSTRUCTIONS:

指示：

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

1) 安装 Ghostscript - Postscript 语言和 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/

2) 安装 RedMon - 重定向端口监视器 - 将一个特殊的打印机端口重定向到一个程序（例如 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.htmlhttp://stat.tamu.edu/~henrik/GSWriter/GSWriter.html

3) 使用 Ghostscript 和 Redmon 设置虚拟 PDF 打印机 - 这里有两个网页解释了如何安装上述工具：http: //masterdev.dyndns.dk/know/freepdf.htmlhttp://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

4) 按以下方式配置 PDF 虚拟打印机使用的 RedMon 打印机端口： - 输出：“程序处理输出” - 新的 PDF 文件应始终保存到同一文件，即 C:\temp\output.pdf

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

将此用于“程序参数”设置：@c:\gs\pdfconf.txt -sOutputFile="C:\temp\output.pdf" -c .setpdfwrite -f -

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

（注意文字文件路径而不是“%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.

5) 编写您自己的 Visual Basic 代码，将报告打印为 pdf，然后使用 FileSystemObject 将其复制到您选择的名称/位置。使用 WScript.Network 对象将默认打印机从通常的默认打印机更改为 PDF 打印机，然后再返回。

Example Code (Access Visual Basic):

示例代码（访问 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.

为了使上述代码在 Access 2000 中工作，您必须添加对 Microsoft Scripting Runtime 和 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/>

到目前为止，我的经验是子程序 DoCmd.OpenReport() 完成将报告输出到 PDF 的速度非常快……但是，如果您有一个非常大的报告，那么当 FileSystemObject 移动/重命名它时，output.pdf 可能会在那里。因此，您可能希望使用 Windows API Sleep() 函数。请参阅 < 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.

编写一个打印出多个报告的例程作为练习留给读者。就我个人而言，我更喜欢将我的报告保存在一个名为“Settings_Reports”的表中，而不是将报告名称硬编码到我的 VBA 模块中。

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/pdftkThis 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/

• 使用DoCmd.SendObject()自动将新创建的报告通过电子邮件发送给经理。

• 使用 PDF Toolkit 将不同的 PDF 报告合并为一个文件 URL：http: //www.accesspdf.com/pdftk这也可以使用 VBA Shell() 函数或在 VBScript 中通过该WScript.Shell.Run()方法自动化

• 使用 VBScript 自动执行 Access，就像 Excel 或 Word 一样。请参阅以下 Microsoft 知识库文章：ACC：使用 Microsoft Access 作为自动化服务器 http://support.microsoft.com/kb/q147816/

Code fragment (VBScript):

代码片段（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!

不用说，这种方法可以做很多事情。最好的部分是您不必学习其他 API（除了标准的 Windows Script Host 和 Microsoft Scripting Runtime 库，无论如何您真的应该知道）。最重要的是它是完全免费的！

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>

设置 PDF 打印机：以下是安装和配置虚拟 PDF 打印机的更详细说明 - 来自：< 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

1. 将 Ghostscript 安装到 C:\ http://www.ghostscript.com/doc/AFPL/index.htm

2. 创建一个文本文件 (c:\gs\pdfconf.txt) 并添加以下文本：

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

请注意，第一行指向 Ghostscript 8.11 版。如果您有不同的版本或将其安装到不同的位置，请进行适当的更改。

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

2. 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)

3. 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)

1. 下载、解压并安装 RedMon（重定向端口监视器） http://www.cs.wisc.edu/~ghost/redmon/index.htm

   • 当您运行 setup.exe 时，您只会看到一个消息框。

2. 转到 Windows 中开始按钮下的打印机/传真菜单并添加新打印机。

   • 打印机的驱动程序必须用于 /color postscript/ 打印机。我选择了 HP Color Laserjet 4550 PS
   • 将打印机端口设置为 RPT1：（重定向端口）

3. 将端口重定向端口配置为：“C:\gs\gs8.11\bin\gswin32c.exe”（更新此行以反映您的 Ghostscript 版本和位置，如果不同）

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

该程序的参数是：@c:\gs\pdfconf.txt -sOutputFile="%1" -c .setpdfwrite -f -

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

输出：“提示输入文件名”运行：“正常”

1. 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!

1. 尝试在这台打印机上打印彩色内容。如果一切正常，系统将提示您输入保存 PDF 文件的文件名（不要忘记添加 .PDF 扩展名）。'希望这对某人有所帮助！

[email protected]

[email protected]