Creating PDF File from Excel using VBA Script

[Bob_Barber@adobeforums.com]

I have Excel 2000 and Adobe Acrobat v6.0 Standard.

I'm trying to use a Visual Basic for Applications (VBA) macro to output reports to PDF files.

As you probably know, creating a PDF file from Excel requires "printing" to an "Adobe PDF" selection as though it were a printer. A dialog box named "Save PDF File As" appears. The default PDF file name will be same as the source Excel file.

Here's where my VBA skills get stuck. It seems that VBA won't work within the "Save PDF File As" box. But I need VBA to create the PDF file with a pre-determined name, not the default, which changes with each report that is generated.

Below is Excel's "recording" of the keystrokes where I output a report to PDF and named the file "XYZtest.pdf". Notice that Excel VBA failed to record the XYZtest.pdf file name into the script, nor account for any file name at all.

Sub Macro1()
Application.ActivePrinter = "Adobe PDF on Ne01:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne01:", Collate:=True
End Sub

So how can I get Excel VBA to work with Adobe Acrobat to name the PDF file what I want?

Thank you!
Bob Barber
[email]eatdrinksleepdream@sbcglobal.net[/email]

[Chuck Reed]

[email]Bob_Barber@adobeforums.com[/email] wrote in message news:<2cd11e49.-1@webx.la2eafNXanI>...

> I have Excel 2000 and Adobe Acrobat v6.0 Standard.
>
> I'm trying to use a Visual Basic for Applications (VBA) macro to output reports to PDF files.
>
> As you probably know, creating a PDF file from Excel requires "printing" to an "Adobe PDF" selection as though it were a printer. A dialog box named "Save PDF File As" appears. The default PDF file name will be same as the source Excel file.
>
> Here's where my VBA skills get stuck. It seems that VBA won't work within the "Save PDF File As" box. But I need VBA to create the PDF file with a pre-determined name, not the default, which changes with each report that is generated.
>
> Below is Excel's "recording" of the keystrokes where I output a report to PDF and named the file "XYZtest.pdf". Notice that Excel VBA failed to record the XYZtest.pdf file name into the script, nor account for any file name at all.
>
> Sub Macro1()
> Application.ActivePrinter = "Adobe PDF on Ne01:"
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
> "Adobe PDF on Ne01:", Collate:=True
> End Sub
>
> So how can I get Excel VBA to work with Adobe Acrobat to name the PDF file what I want?
>
> Thank you!
> Bob Barber
> [email]eatdrinksleepdream@sbcglobal.net[/email]

Bob:

You need to change the registry settings for Adobe. Here is a bit of
VBA that I use to do just that:

'declarations:
Global Const dhcRegSz = 1


Public Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias _
"RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, _
ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As _
Long) As Long

Public Declare Function RegSetValueEx _
Lib "advapi32.dll" Alias "RegSetValueExA" _
(ByVal hKey As Long, ByVal lpValueName As String, _
ByVal dwReserved As Long, ByVal dwType As Long, _
lpData As Any, ByVal cbData As Long) As Long

Public Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal hKey As Long) As Long

Sub Printme()
dim strDefaultPrinter as string, strOutFile as string
strDefaultPrinter = Application.ActivePrinter
Application.ActivePrinter = "Adobe PDF on Ne01:"
'Open key
lngRegResult = RegOpenKeyEx(dhcHKeyCurrentUser,
"Software\Adobe\Acrobat PDFWriter", 0&, dhcKeyAllAccess, lngResult)

'Turn view result off
lngRegResult = RegSetValueEx(lngResult, "bExecViewer", 0&,
dhcRegSz, ByVal "0", 1)
lngRegResult = RegSetValueEx(lngResult, "bDocInfo", 0&, dhcRegSz,
ByVal "0", 1)

'Set Output File name and path
strOutFile = "c:\Temp\PDFTemp\mypdf.pdf"

lngRegResult = RegSetValueEx(lngResult, "PDFFileName", 0&,
dhcRegSz, ByVal strOutFile, Len(strOutFile))
'close key
lngRegResult = RegCloseKey(lngResult)

ActiveWindow.SelectedSheets.PrintOut
Application.ActivePrinter = strDefaultPrinter
End sub

[zlatan24]

I work with excel files quite often and once my nephew asked me about one excel problem. I couldn't help him and started looking for the solution. And luckily found out it yesterday evening, he thanked me a lot and in my view in this situation the tool will be helpful

[Unregistered]

Hi,

It seems the above codes don't work for Adobe Acrobat 7 Pro and Excel 2003 :(. Can anyone please advice me which part of the codes should be changed to suit Adobe 7?

Thanks a lot!

[Unregistered]

I just follow the code written here, and my "Device and Printer" is disappear
does any one can help

Thanks