Using Excel VBA to Print to PDF File?

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