Using VBA how do I call up the Adobe Create PDF function

Question

  Sheets("Key Indicators").ExportAsFixedFormat Type:=xlTypePDF,
 Filename:=ArchivePath, Quality:=xlQualityStandard,
 IncludeDocProperties:=True, IgnorePrintAreas _
         :=False, OpenAfterPublish:=False

Currently this is what I have.

I understand how to ExportAsFixedFormat PDF but what I need to know how to do is to access the Create PDF function under Acrobat (As show in the picture below) using VBA. If I do ExportAsFixedFormat the links get flattened. Acrobat "Create PDF" would allow me to convert an Excel to PDF with hyperlinks included.

How would I do that?

I am using Excel 2016 and Adobe Pro DC

These are my adobe references

Does the Acrobat add-in provide an API? an object model? *anything* that's programmatically accessible from VBA? If not, you might have to resort to *SendKeys*, which is a horrible, horrible way of doing things. What's wrong with saving as PDF? What does the Adobe add-in do that Excel doesn't already? — Mathieu Guindon, May 31 '16 at 17:37
If I save as PDF normally it will flatten all of the links in the document. — Michael Downey, May 31 '16 at 17:42
Related to @Mat'sMug comment, have you tried to add the reference for Adobe Acrobat and see the available commands there? (References->Adobe Acrobat) — Sgdva, Jun 03 '16 at 15:23
In Excel VBA Editor, go to Tools->References-> Look for something that resembles acrobat. See attached images for reference Image1: http://s20.postimg.org/3qxr9vqpp/Capture.png Image2: http://s20.postimg.org/iuoirq2gt/Capture.png — Sgdva, Jun 03 '16 at 16:55
It is true that the builtIn PDF Publishing only retains Web URLs, Hyperlinks that goes to another Range in the workbook are removed. — PatricK, Jun 10 '16 at 01:45

score 3 · Answer 1 · edited Jun 07 '16 at 09:02

3

Sub PDF()
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\PCNAME\Documents\Book1.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    True
End Sub

Please try the above codes

edited Jun 07 '16 at 09:02

Anders Marzi Tornblad

18,896
9
51
66

answered Jun 04 '16 at 06:31

David Syriac

65
1
10

The problem is that without calling up Adobe, the hyperlinks do not transfer to PDF – Michael Downey Jun 09 '16 at 17:01

score 1 · Answer 2 · answered Jun 03 '16 at 14:46

1

With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:="N:\JKDJKDJ", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True,  
    IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

answered Jun 03 '16 at 14:46

jellz77

324
4
15

worth noting my links don't get flattened when using this approach – jellz77 Jun 03 '16 at 14:48

score 1 · Accepted Answer · answered Jun 03 '16 at 18:02

Acrobat Reference should work
Here is the guide from Adobe
Once added, you may use the following code Tip: It may lead you to correct coding -I'm not quite sure since I coded it "blindly" because I don't have Acrobat in my PC-. Debug step by step to see what's doing.

Sub ExportWithAcrobat()
Dim AcroApp As Acrobat.CAcroApp 'I'm not quite sure it's needed since we are creating the doc directly
Dim AcrobatDoc As Acrobat.CAcroPDDoc
Dim numPages As Long
Dim WorkSheetToPDF As Worksheet
Const SaveFilePath = "C:\temp\MergedFile.pdf"
    Set AcroApp = CreateObject("AcroExch.App") 'I'm not quite sure it's needed since we are creating the doc directly
    Set AcrobatDoc = CreateObject("AcroExch.PDDoc")
    'it's going to be 0 at first since we just created
    numPages = AcrobatDoc.GetNumPages
    For Each WorkSheetToPDF In ActiveWorkbook.Worksheets
    If AcrobatDoc.InsertPages(numPages - 1, WorkSheetToPDF, 0, AcrobatDoc.GetNumPages(), True) = False Then 'you should be available to work with the code to see how to insert the sheets that you want in the created object ' 1. If Part1Document.InsertPages(numPages - 1, "ExcelSheet?", 0, AcrobatDoc.GetNumPages(), True) = False
    MsgBox "Cannot insert pages" & numPages
    Else ' 1. If Part1Document.InsertPages(numPages - 1, "ExcelSheet?", 0, AcrobatDoc.GetNumPages(), True) = False
    numPages = numPages + 1
    End If ' 1. If Part1Document.InsertPages(numPages - 1, "ExcelSheet?", 0, AcrobatDoc.GetNumPages(), True) = False
    Next WorkSheetToPDF
    If AcrobatDoc.Save(PDSaveFull, SaveFilePath) = False Then ' 2. If Part1Document.Save(PDSaveFull, "C:\temp\MergedFile.pdf") = False
    MsgBox "Cannot save the modified document"
    End If ' 2. If Part1Document.Save(PDSaveFull, "C:\temp\MergedFile.pdf") = False
End Sub

Following pages may provide better assistance: Link1, Link2

Refer to the documentation and try to "play" combining them with the codes provided at the bottom, can't code further since I don't have Acrobat. — Sgdva, Jun 03 '16 at 19:00

score 1 · Answer 4 · 2016-08-05T03:34:27.673

1

You can publish any Excel Range as a PDF using ExportAsFixedFormat. There is no need to set a refernce to Acrobat.

' Usage:
' PublishRangePDF(Thisworkbook, fileName) : Will Publish the entire Workbook
' PublishRangePDF(AvtiveSheet, fileName) : Will Publish all selected worksheets
' PublishRangePDF(Range("A1:H100"), fileName) : Will Publish Range("A1:H100")


Sub PublishRangePDF(RangeObject As Object, fileName As String, Optional OpenAfterPublish As Boolean = False)
    On Error Resume Next
    RangeObject.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=OpenAfterPublish
    On Error GoTo 0
End Sub

edited Aug 05 '16 at 03:34

answered Jun 09 '16 at 02:29

The problem is that without calling up Adobe, the hyperlinks do not transfer to PDF – Michael Downey Jun 09 '16 at 17:02
URL hyperlinks work for me. Bookmark hyperlinks get flattened. jellz77 uses ExportAsFixedFormat also an you said that it didn't flatten them. If you can send me your workbook I'll take a lot. If not can you post the rest if your code? – Jun 09 '16 at 17:46

Using VBA how do I call up the Adobe Create PDF function

4 Answers4

Linked