Using Excel VBA to Print to PDF File?

[NateBrei]

Hi, All !!

I have an Excel VBA application that creates Excel reports.  These need to be sent external to our company in PDF format.  I've downloaded the Acrobat SDK and have found the VB sample for AdobePDFSilent.  Unfortunately, it appears that this is written for VB or VB.Net as I don't have any of the data types available that are created in the code.  However, from another forum, I've gotten some VB code that appears to do many of the processes included in the SDK sample.

The code below doesn't give me any errors, but no PDF file is created.

My VBA code

Declare Function RegOpenKeyA Lib "advapi32.dll" ( _
    ByVal Key As Long, _
    ByVal SubKey As String, _
    NewKey As Long) As Long
Declare Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" ( _
    ByVal hKey As Long, _
    ByVal lpValueName As String, _
    ByVal Reserved As Long, _
    ByVal dwType As Long, _
    lpData As Any, _
    ByVal cbData As Long) As Long
Declare Function RegCloseKey Lib "advapi32.dll" ( _
    ByVal hKey As Long) As Long

Sub TestPrintPDF()
    Dim strDefaultPrinter As String
    Dim strOutFile As String
    Dim lngRegResult As Long
    Dim lngResult As Long
    Dim dhcHKeyCurrentUser As Long
    Dim PDFPath As String
    Const dhcRegSz As Long = 1
   
1    Workbooks.Open ("\\master\fnshares\bcbcm\Client Management\Client Services\New Account Fees\09 September 2010\3Q10 Rebate Ltrs\Infi\MacroTest\A02.xls")
2    Select Case ActiveWorkbook.Sheets.Count
3        Case 1
4            Sheets(1).Select
5        Case 2
6            Sheets(Array(Sheets(1).Name, Sheets(2).Name)).Select
7        Case 3
8            Sheets(Array(Sheets(1).Name, Sheets(2).Name, Sheets(3).Name)).Select
9        Case 4
10            Sheets(Array(Sheets(1).Name, Sheets(2).Name, Sheets(3).Name, Sheets(4).Name)).Select
11        Case 5
12           Sheets(Array(Sheets(1).Name, Sheets(2).Name, Sheets(3).Name, Sheets(4).Name, Sheets(5).Name)).Select
13      Case 6
14         Sheets(Array(Sheets(1).Name, Sheets(2).Name, Sheets(3).Name, Sheets(4).Name, Sheets(5).Name, Sheets(6).Name)).Select
15  End Select
16  dhcHKeyCurrentUser = &H80000001
17  strDefaultPrinter = Application.ActivePrinter
18  PDFPath = ActiveWorkbook.Path & Application.PathSeparator 'The directory in which you want to save the file
19  strOutFile = PDFPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & ".pdf" 'Change the pdf file name if required. This should have the fully qualified path
   
20  lngRegResult = RegOpenKeyA(dhcHKeyCurrentUser, "Software\Adobe\Acrobat Distiller\PrinterJobControl", lngResult)
21  lngRegResult = RegSetValueEx(lngResult, Application.Path & "\Excel.exe", 0&, dhcRegSz, ByVal strOutFile, Len(strOutFile))
22  lngRegResult = RegCloseKey(lngResult)
23  ThisWorkbook.ActiveSheet.PrintOut copies:=1, ActivePrinter:="Adobe PDF"
24  Application.ActivePrinter = strDefaultPrinter
25  ActiveWorkbook.Close False
End Sub

From what I can determine, the lines 17 & 24 combined basically accomplish the same thing as the SaveandUpdateDefaultPrinter function in the SDK (get and save the current default printer and return it to that default after printing the PDF).

Line 20 opens the Registry key for Distiller\PrinterJobControl which is done in part of the ConvertFile function in the following SDK code.

SDK Code

            Dim objDistillerRegKey As Microsoft.Win32.RegistryKey = Microsoft.Win32.Registry.ClassesRoot
            Dim strDistillerSubKey As String = "SOFTWARE\\Adobe\\Acrobat Distiller\\PrinterJobControl"

            'Open Current User's Distiller Subkey for writing
            objDistillerRegKey = Microsoft.Win32.Registry.CurrentUser.OpenSubKey(strDistillerSubKey, True)

Line 21 sets the Registry key for Excel with the name of the PDF file to output which also appears to be done in part of the ConvertFile function in the following code.

SDK Code

           If (Not objDistillerRegKey Is Nothing) Then     'set reg key value for this app and file
                objDistillerRegKey.SetValue(strAppPath, strOutputFile)
                objDistillerRegKey.Close()
            End If

I have verified, using RegEdit, that this Registry key does get set with the desired output filename.

Line 23 prints the Excel file to PDF when done manually (this was recorded using the Excel Macro Recorder).  This should be comparable to the PrintToAdobePDF function in the SDK as below.

SDK Code

    Private Sub PrintToAdobePDF(ByVal InputfilePath As String)

        'Prints InputFilePath to the AdobePDF printer.
        'Since we just gathered all this info programmatically,
        'this function assumes the file is present, that it has an
        'associated application and that the current user has print privileges.

        'Define properties for the print process
        Dim pProcInfo As New ProcessStartInfo

        pProcInfo.FileName = InputfilePath
        pProcInfo.Verb = "Print"

        'Make process invisible
        pProcInfo.CreateNoWindow = True
        pProcInfo.WindowStyle = ProcessWindowStyle.Hidden

        'start print process
        Dim pMyProc As Process = Process.Start(pProcInfo)
        pMyProc.WaitForExit()

    End Sub

These are some of the statements I can't do because I don't have a ProcessStartInfo type.  What am I doing wrong or NOT doing that the PDF file is not created?  I hope I've described my situation in enough, but not too much detail.  Thanks for your help.

Nate Brei

[ReinhardF]

Hi,

here is a link to a VBA example Script: http://forums.adobe.com/message/3309184#3309184

It uses Excel to print to a postscript file (print to file) and then the distiller (Adobe Pdf) API to convert to PDF.

That works for Acrobat v5-6. Perhabs you test if it also works for newer versions.

Best regards, Reinhard

[NateBrei]

Reinhard,

Thanks for your reply.  I've incorporated the printtoPDF sub into my code and made the minor changes necessary to work with my project (i.e. changed variable names, etc.).  It runs through, that is, I receive no errors, but neither the postscript file nor the pdf file show up in my folder.  When you do a PrintOut with PrintToFile=True, should it actually create the postscript file in my folder?  Any ideas why neither file seems to get created?

Thanks.

Nate

[ReinhardF]

"... When you do a PrintOut with PrintToFile=True, should it actually create the postscript file in my folder?"

Yes. You can try/test it manuel:

Select a range to print, From Menu choose File->Print. Select Adobe PDF as printer, mark the box print to file and press OK. Printing starts.

Then in the top left you get an input box, where you can state full-filename (path) of the Output File (e.g. c:\Test.ps). Type the name in and press OK, then the print process continue and you find the postscript file, how you have entered. If you doubleclick on it Adobe PDF starts and convert it to pdf.

So after this manual operation you should have the files: c:\test.ps, c:\test.pdf and as standard the log-file c:\test.log.

Does that work for you?

Reinhard

[NateBrei]

Reinhard,

Thanks again for your response.  Yes, I can get those things to occur manually as I follow your instructions.  When I initially tried following your instructions, I got an Adobe PDF error message that said:

When you create a PostScript file you must rely on system fonts and use document fonts.  Please go to the printer properties, "Adobe PDF Settings" page and turn OFF the option "Rely on system fonts only; do not use document fonts."

When I went back through the process & turned off that option, I got the 3 files created.

So, I deleted those 3 files and ran my code.  Prior to this time, the program would run but no files were created.  This time, when it got to the PrintOut statement, I got the above error message.  I looked in the folder and the file was created with a size of 0K.  I skipped the PrintOut step & finished running the code.  This time I got a log file that said the PostScript file was empty and no PDF was created.

So, it appears that I now have to have some way to programmatically turn-off that option on the Adobe PDF Printer Properties page.  How come you don't have to do that?  Any ideas how I do that?

Nate

[ReinhardF]

Mmmh,

you need only to turn OFF one time, so if it is only for you there is no realy a need to look for that.

However, it may be good to check if it is set - seems I forgot already that I've done that - , so I will look for that. It's a registry setting.

best regards, Reinhard

PS: What's your Acrobat version?

[Karl Heinz Kremer]

Since Acrobat 7 you do not have to create a Postscript file first, you can

print directly to the Adobe PDF printer and set the output filename (just

like you mentioned in your original message). Take a look at this page from

the documentation:

http://livedocs.adobe.com/acrobat_sdk/9.1/Acrobat9_1_HTMLHelp/Distiller_WinIntro.61.11.html

You need to set the registry key just before printing the document, and the

print process will remove that key again. The problem usually is to figure

out what application name to use for your specific case. Just print a

document manually from that application, and then check the

PrinterJobControl key in regedit - you will find a new entry of the form

"LastPdfPortFolder - " which has a value of the directory

your file got saved to. Just use that application name plus the appropriate

path, and printing to the "Adobe PDF" printer should give you the file

specified as part of that key.

Karl Heinz Kremer

PDF Acrobatics Without a Net

khk@khk.net

http://www.khkonsulting.com

[NateBrei]

Reinhard & Karl Heinz,

Thank you both for your responses and willingness to work with me on this problem.  This is driving me crazy & is getting very frustrating.  It seems that I've tried everything that people have suggested (I've also posted on a VB Forum that I subscribe to) and I'm basically doing what works for everyone else but doesn't work for me.  I've got to be close.

Reinhard, regarding your last post, it doesn't appear to be a one-time setting.  Everytime I come into the Printers Property box (even after I've printed a PDF document manually, that option about system & document fonts is ALWAYS turned on.  If it is a registry setting, please let me know how to turn it off.  I'm using Adobe Acrobat 9 Standard.

Karl Heinz, I've tried that based on my initial post (see the code there).  Since your post, I tried it again.  I get the same result, NO FILE is produced anywhere.

I wish I could post pictures, but evidently I can't.  So, I going to post first my code (in case you want to try to recreate my problem), then the values of the variables in that code when I run it (so you can I have everything set correctly as far as I know), and finally, the values of the Registry entries that I have after I run it.  So, please bear with me as the post may be a little long.

My Code
Declare Function RegOpenKeyA Lib "advapi32.dll" ( _
    ByVal Key As Long, _
    ByVal SubKey As String, _
    NewKey As Long) As Long
Declare Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" ( _
    ByVal hKey As Long, _
    ByVal lpValueName As String, _
    ByVal Reserved As Long, _
    ByVal dwType As Long, _
    lpData As Any, _
    ByVal cbData As Long) As Long
Declare Function RegCloseKey Lib "advapi32.dll" ( _
    ByVal hKey As Long) As Long

Sub TestPrintPDF()
    Dim strDefaultPrinter As String
    Dim strOutFile As String
    Dim lngRegResult As Long
    Dim lngResult As Long
    Dim dhcHKeyCurrentUser As Long
    Dim PDFPath As String
    Const dhcRegSz As Long = 1
   
    'Workbooks.Open ("\\master\fnshares\bcbcm\Client Management\Client Services\New Account Fees\09 September 2010\3Q10 Rebate Ltrs\Infi\MacroTest\A02.xls")
    Workbooks.Open ("H:\A02.xls")
    Select Case ActiveWorkbook.Sheets.Count
        Case 1
            Sheets(1).Select
        Case 2
            Sheets(Array(Sheets(1).Name, Sheets(2).Name)).Select
        Case 3
            Sheets(Array(Sheets(1).Name, Sheets(2).Name, Sheets(3).Name)).Select
        Case 4
            Sheets(Array(Sheets(1).Name, Sheets(2).Name, Sheets(3).Name, _
             Sheets(4).Name)).Select
        Case 5
            Sheets(Array(Sheets(1).Name, Sheets(2).Name, Sheets(3).Name, _
             Sheets(4).Name, Sheets(5).Name)).Select
        Case 6
            Sheets(Array(Sheets(1).Name, Sheets(2).Name, Sheets(3).Name, _
             Sheets(4).Name, Sheets(5).Name, Sheets(6).Name)).Select
    End Select
    dhcHKeyCurrentUser = &H80000001
    strDefaultPrinter = Application.ActivePrinter
   
    'The directory in which you want to save the file
    PDFPath = ActiveWorkbook.Path & Application.PathSeparator
    'Change the pdf file name if required. This should have the fully qualified path
    strOutFile = PDFPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & ".pdf"
   
    lngRegResult = RegOpenKeyA(dhcHKeyCurrentUser, "Software\Adobe\Acrobat Distiller\PrinterJobControl", _
        lngResult)
    lngRegResult = RegSetValueEx(lngResult, Application.Path & "\Excel.exe", 0&, dhcRegSz, _
        ByVal strOutFile, Len(strOutFile))
    lngRegResult = RegCloseKey(lngResult)

    ThisWorkbook.ActiveSheet.PrintOut copies:=1, ActivePrinter:="Adobe PDF"
    'ThisWorkbook.ActiveSheet.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
        printtofile:=True, collate:=True, prtofilename:=strOutFile
    'Call printToPdf(strOutFile)
   
    Application.ActivePrinter = strDefaultPrinter
    ActiveWorkbook.Close False
End Sub

Sub printToPdf(PDFFilename)
    ' Define a postscript file name
    PSFileName = "H:\TempPostScript.ps"
    ' Print the Excel range to the postscript file
    'Dim MySheet As Worksheet
    'Set MySheet = ActiveSheet
    ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
        printtofile:=True, collate:=True, prtofilename:=PSFileName
   
    ' Convert the postscript file to .pdf
    Set myPDF = CreateObject("PdfDistiller.PdfDistiller.1")
    myPDF.FileToPDF PSFileName, PDFFilename, ""
End Sub

Values of my Variables When I Run the Code
? dhcHKeyCurrentUser
     -2147483647
? strDefaultPrinter
     \\tcps01p\FNT12W00 Canon 5020 PCL 5e on Ne04:
? PDFPath
     H:\
? strOutFile
     H:\A02.pdf
? lngResult
     2280
? Application.Path & "\Excel.exe
     C:\Program Files\Microsoft Office\OFFICE11\Excel.exe
? dhcRegSz
     1
? Len(strOutFile)
     10
? PSFileName
     H:\TempPostScript.ps
? PDFFilename
     H:\A02.pdf

Values of my Registry Entries (HKEY_CURRENT_USER\Software\Adobe\Acrobat Distiller\PrinterJobControl)
(Default)          REG_SZ          (value not set)
C:\Program Files\Microsoft Office\OFFICE11\Excel.exe          REG_SZ          H:\A02.pdf
LastPdfPortFolder - EXCEL.EXE          REG_SZ          "Q:\Client Management\Client Services\New Account Fees\09 September 2010\3Q10 Rebate Ltrs\Infi\MacroTest

Note:  There are a couple of other entries for documents that I've printed manually that I didn't include.  Also, the last entry above, contains the value of the folder that I last manually "printed" to.

I've also noticed that I have a Registry SubKey under PrinterJobControl called DownloadFonts.  However, the only entry there is:
     (Default) REG_SZ (value not set)
Is this the registry key you mentioned, Reinhard?

As you can see in my code, I have 3 different methods that I've tried to print.  The first one defaults everything after selecting the Acrobat PDF printer.  The second sets the output filename as a PDF (basically what you suggested, Karl Heinz).  The third method calls a procedure that prints to a PostScript file & then uses Distiller to print from that file to pdf.  This is the method Reinhard suggested.

With the first 2 methods, I get NO error messages, but no file(s) show up.  With the 3 method, I get the error about the Fonts checkbox, but it creates a 0K PostScript file.  When I skip that statement and run the other 2 statements, I get a log file that says the PostScript file is empty and not PDF file was created.

YIKES...  What's going on?  Thanks again for attempting to help me!!!
Nate

[ReinhardF]

Hi,

the registry key for sending fonts HKEY_USERS\Printers\DevModePerUser\Adobe PDF is a binary. So it's cumbersome to set.

It's hard to imagine that Adobe offers a Distiller API, but emphasize to write into registry.

With versions > 6 I don't can help.

By the way I work mostly with "FreePdf", a ghostscript based pdf-printer driver.

Good luck,

Reinhard