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:
Very grateful for any help or advice you can give. Thanks in advance!
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:
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.