Excel VBA

[quinton.ho@gd-ots.com]

I'm having trouble getting commands that relate to the Adobe Acrobat 10.0 Type Library to run in Excel VBA. In my code I'm just trying to open two PDFs and merge them into one.

I have Adobe Acrobat Pro (Version 23.001.20174.0) installed and I have Adobe Acrobat 10.0 Type Library enabled in my VBA editor. But when I run the program line by line the only command that seems to work is the Set Aapp = CreateObject("AcroExch.App").  Everything after that will "run" in Excel VBA but nothing actually happens.

Thanks for your help!

Q

Sub combine_pdf_files()

Dim Aapp As Acrobat.AcroApp
Dim toDoc As Acrobat.AcroPDDoc
Dim fromDoc As Acrobat.AcroPDDoc

Set Aapp = CreateObject("AcroExch.App")
Set toDoc = CreateObject("AcroExch.PDDoc")
Set fromDoc = CreateObject("AcroExch.PDDoc")

Aapp.Show

toDoc.Open ("S:\DRYLNE\DT Report - New\24 Hr Reports\Dryer Daily Report.pdf")
fromDoc.Open ("S:\DRYLNE\DT Report - New\24 Hr Reports\Blender Daily Report.pdf")

If toDoc.InsertPages(0, fromDoc, 0, fromDoc.GetNumPages(), True) = False Then
Debug.Print "Failed to insert the page"

End If
If toDoc.Save(PDSaveFull, "S:\DRYLNE\DT Report - New\24 Hr Reports\Dryline 24 Hour Report.pdf") = False Then
Debug.Print "Failed to save"
Else
Debug.Print "Dryline Report Saved"
End If

toDoc.Close
fromDoc.Close

Aapp.Exit

End Sub

[TeMatcha]

Here's a revised version of your VBA code with comments and additional checks to ensure it operates correctly:

Sub combine_pdf_files()

    Dim Aapp As Object
    Dim toDoc As Object
    Dim fromDoc As Object
    Dim retVal As Boolean
    
    ' Create Acrobat application object
    Set Aapp = CreateObject("AcroExch.App")
    
    ' Create PDF document objects
    Set toDoc = CreateObject("AcroExch.PDDoc")
    Set fromDoc = CreateObject("AcroExch.PDDoc")
    
    ' Display the Acrobat application
    Aapp.Show
    
    ' Open the first PDF document
    If toDoc.Open("S:\DRYLNE\DT Report - New\24 Hr Reports\Dryer Daily Report.pdf") = False Then
        Debug.Print "Failed to open the first PDF document"
        Exit Sub
    End If
    
    ' Open the second PDF document
    If fromDoc.Open("S:\DRYLNE\DT Report - New\24 Hr Reports\Blender Daily Report.pdf") = False Then
        Debug.Print "Failed to open the second PDF document"
        toDoc.Close
        Exit Sub
    End If
    
    ' Insert pages from the second document into the first
    retVal = toDoc.InsertPages(toDoc.GetNumPages() - 1, fromDoc, 0, fromDoc.GetNumPages(), True)
    
    If retVal = False Then
        Debug.Print "Failed to insert pages from the second PDF document"
        toDoc.Close
        fromDoc.Close
        Exit Sub
    End If
    
    ' Save the combined document
    retVal = toDoc.Save(1, "S:\DRYLNE\DT Report - New\24 Hr Reports\Dryline 24 Hour Report.pdf")
    
    If retVal = False Then
        Debug.Print "Failed to save the combined PDF document"
    Else
        Debug.Print "Combined PDF document saved successfully"
    End If
    
    ' Close the documents
    toDoc.Close
    fromDoc.Close
    
    ' Exit Acrobat application
    Aapp.Exit
    
    ' Release the objects
    Set toDoc = Nothing
    Set fromDoc = Nothing
    Set Aapp = Nothing

End Sub

Key Points:

1. Error Checking: I've added error checks after opening each document and after inserting pages to ensure that each operation is successful. If any operation fails, the code will print an appropriate message and exit gracefully.

2. Method Usage: I used toDoc.GetNumPages() - 1 as the insertion point to ensure that pages are appended at the end of the first document. This could be adjusted based on your specific requirements.

3. Save Method: The toDoc.Save method uses the 1 parameter (PDSaveFull) instead of the constant name to ensure compatibility. If you prefer using named constants, make sure they are properly declared or imported.

4. Clean Up: Properly close and release all objects to prevent memory leaks and ensure the Acrobat application exits correctly.

Troubleshooting Tips:

• Check File Paths: Ensure the file paths provided are correct and accessible from your VBA environment.
• Verify Permissions: Make sure you have the necessary permissions to read and write the PDF files in the specified directory.
• Compatibility: Ensure that the version of Acrobat installed is fully compatible with the Acrobat 10.0 Type Library. Sometimes, upgrading to the latest version or matching the type library with the installed version can resolve issues.

By following these steps, you should be able to merge PDF documents using VBA in Excel. If you continue to experience issues, consider updating Adobe Acrobat or checking for any VBA-specific settings in Acrobat that might be affecting the script's execution.

Elisabet