vba 将 Excel 文件另存为 PDF 到特定路径
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3882773/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Save an Excel file as PDF to a specific path
提问by Anders
I would like to save an Excel file as a .pdf file to a specific location and then send the file in a mail.
我想将 Excel 文件作为 .pdf 文件保存到特定位置，然后通过邮件发送该文件。
I'm using Office 2000 :|
我正在使用 Office 2000 :|
This is my code so far:
到目前为止，这是我的代码：
Application.ActivePrinter = "PDF995 on Ne00:"
ActiveSheet.PageSetup.PrintArea = Range("A68").Value
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"PDF995 on Ne00:", Collate:=True
Set WB = ActiveWorkbook
Set oApp = CreateObject("Outlook.Application")
Set omail = oApp.Createitem(0)
With omail
.To = Range("B61").Value
.Subject = "Approved"
.Body
.Display
Rows("81:134").Select
Selection.EntireRow.Hidden = True
End With
I can easily save the file and mail it, but I can't save it to a specific location.
我可以轻松保存文件并将其邮寄，但我无法将其保存到特定位置。
I need to be able to specificy a path like "C:\path\file.pdf".
我需要能够指定像“C:\path\file.pdf”这样的路径。
回答by Neville Shaun Ng
If you have the file saved to fixed location but you're unable to choose where, as a last resort you could always use fso's MoveFile to move it to your specified location
如果您将文件保存到固定位置，但无法选择位置，作为最后的手段，您可以始终使用 fso 的 MoveFile 将其移动到您指定的位置
eg. If the file is always saved as "C:\temp\file1.pdf", and you want it on desktop
例如。如果文件始终保存为“C:\temp\file1.pdf”，并且您希望它在桌面上
'Initialise first'
set fso = CreateObject("Scripting.FileSystemObject")
...
'After procedure'
desired_destination = "c:\windows\desktop\"
target_file = "C:\temp\file1.pdf"
fso.MoveFile target_file, desired_destination
If you want to check for an existing file conflict (I believe fso's Move doesn't allow for overwrite), use CopyFile with over-write switched on then Delete the source file if necessary
如果要检查现有文件冲突（我相信 fso 的 Move 不允许覆盖），请使用 CopyFile 并打开覆盖，然后在必要时删除源文件
If you'd like to use a file dialog to choose the destination, you can use the UserAccounts.CommonDialog object (although that doesn't work with Vista) or SAFRCFileDlg.FileOpen (pretty much only works on XP) or borrow an IE prompted box. (Unfortunately the options aren't all that great with VBS to my knowledge)
如果您想使用文件对话框来选择目标，您可以使用 UserAccounts.CommonDialog 对象（尽管这不适用于 Vista）或 SAFRCFileDlg.FileOpen（几乎仅适用于 XP）或借用 IE 提示盒子。（不幸的是，据我所知，VBS 的选项并不是那么好）
Check them out here: http://www.robvanderwoude.com/vbstech_ui_fileopen.php
在这里查看它们：http: //www.robvanderwoude.com/vbstech_ui_fileopen.php
回答by Lance Roberts
It's a little complicated, as you have to set registry keys. Assuming that you have a full version of Adobe Acrobat that has installed the initial registry keys:
这有点复杂，因为您必须设置注册表项。假设您拥有已安装初始注册表项的完整版 Adobe Acrobat：
First, you have the registry accessing functions, which you put in a non-sheet module:
首先，您有注册表访问功能，您将其放入非工作表模块中：
Private Const HKEY_CURRENT_USER As Long = &H80000001
Private Const HKCU = HKEY_CURRENT_USER
Private Const KEY_SET_VALUE = &H2&
Private Const REG_SZ = 1
Private Declare Function RegOpenKeyEx Lib "advapi32" _
Alias "RegOpenKeyExA" ( _
ByVal hKey As Long, _
ByVal lpSubKey As String, _
ByVal ulOptions As Long, _
ByVal samDesired As Long, _
phkResult As Long) As Long
Private Declare Function RegSetValueExA Lib "ADVAPI32.DLL" _
(ByVal hKey As Long, _
ByVal sValueName As String, _
ByVal dwReserved As Long, _
ByVal dwType As Long, _
ByVal sValue As String, _
ByVal dwSize As Long) As Long
Private Declare Function RegCloseKey Lib "ADVAPI32.DLL" ( _
ByVal hKey As Long) As Long
Then, you use the following code to set the registry key that tells Adobe where to save the file. Note, it has to be set everytime you print.
然后，您使用以下代码设置注册表项，告知 Adobe 将文件保存到何处。请注意，每次打印时都必须设置它。
Dim RegResult As Long, Result As Long
RegResult = RegOpenKeyEx(HKCU, "Software\Adobe\Acrobat Distiller\PrinterJobControl", _
0&, KEY_SET_VALUE, Result)
RegResult = RegSetValueExA(Result, "C:\Windows\splwow64.exe", 0&, REG_SZ, _
FileName, Len(FileName))
RegResult = RegCloseKey(Result)
Also Note, the "C:\Windows\splwow64.exe" is what I needed for my Excel 2010 32-bit, and it may be different for you. To determine it (which won't change) first print manually to PDF, then go to the registry key and see what application is used in the HKCU\Software\Adobe\Acrobat Distiller\PrinterJobControl LastPDFPortFolder key. Then use the name of the full application path for that executable.
另请注意，“C:\Windows\splwow64.exe”是我的 Excel 2010 32 位所需的，对您来说可能有所不同。要确定它（不会更改），请首先手动打印为 PDF，然后转到注册表项并查看 HKCU\Software\Adobe\Acrobat Distiller\PrinterJobControl LastPDFPortFolder 项中使用的应用程序。然后使用该可执行文件的完整应用程序路径的名称。
回答by Lance Roberts
Try this:
尝试这个：
sName = "C:\path\file.pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sName
