Export Excel Workbook to PDF with bookmarks using VBA

[tjwdickinson]

Through extensive searching, I came across this post from 2009, but I'm sure quite a lot has changed since then.

I have a macro in Excel VBA which executes a number of operations on a workbook, and I want it to automatically export the results to PDF using the Acrobat add-on so that it can create bookmarks for each sheet in the workbook.

I also found this code (VBA), but I don't really understand it, so I can't update it myself:

Dim AcroApp As Acrobat.CAcroApp 
Dim AcrobatDoc As Acrobat.CAcroPDDoc
Dim numPages As Long
Dim WorkSheetToPDF As Worksheet
Const SaveFilePath = "C:\temp\MergedFile.pdf"
    Set AcroApp = CreateObject("AcroExch.App") 
    Set AcrobatDoc = CreateObject("AcroExch.PDDoc")
    numPages = AcrobatDoc.GetNumPages
For Each WorkSheetToPDF In ActiveWorkbook.Worksheets
    If AcrobatDoc.InsertPages(numPages - 1, WorkSheetToPDF, 0, AcrobatDoc.GetNumPages(), True) = False Then 
        MsgBox "Cannot insert pages" & numPages
    Else 
        numPages = numPages + 1
    End If     
Next WorkSheetToPDF
If AcrobatDoc.Save(PDSaveFull, SaveFilePath) = False Then 
    MsgBox "Cannot save the modified document"
End If

The parameters I need are the following:

• Create PDF: entire workbook, actual size << these are the options in the 'Create PDF' window of the Acrobat add-on. Each file I have has a different number of pages.
• Conversion settings: Standard(1) << custom settings which includes adding bookmarks for each worksheet
• Location: parent directory (up one directory from the location of the Excel file)
• File name: The Excel file name will look something like this:
  21-22 January v6 SomeTextR3.1234_Leerkrachten
            -- "SomeText" should be removed and the preceding space replaced with a . to look like:
                21-22 January v6.R3.1234
            -- The suffix _Leerkrachten should be replaced with _LK
                Two other possible suffixes: _Leerlingen >> _LL // _pauze >> _TZ
• Do not view file on export

Very grateful for any help or advice you can give. Thanks in advance!

[tjwdickinson]

Updating my query:

My question is hopefully rather simple, but I'm relatively new to VBA and I know nothing about Acrobat API. After spending the better part of the past two days scouring the web for an answer, I've only found long-outdated results (like like this post from 2016 based on this blog from 2009 and this post from 2009) which are only partially relevant (most of the results dealt with merging two already existing PDFs), minimal documentation, and broken reference links.

What I have so far is really just the beginning:

VBA Code:

Sub exportPDF(pdfExportLoc)
Dim pdfNamePath as String
pdfNamePath = pdfExportLoc & "\" & left(activeworkbook.name, len(activeworkbook.name)-5) & ".pdf"    ' adds the file name to the path and changes the extension
End Sub

After that, I'm totally lost. It seems like it should be straight forward, but I'm stuck. I really just need it to create the PDF with all worksheets in the active workbook, add a bookmark to each sheet (using the sheet name), and then save it to pdfNamePath.

Thank you in advance for any help you can give.