I am using Adobe 9 Pro ( v9.2.0) and would to automate the building of PDFs from Excel.
I have an Excel document with a [Main] page that has hyperlinks to the multiple worksheets in the document.
I can manually select the [Main] sheets + all other sheets and choose SaveAs [Adobe PDF] and it builds a single PDF with all the sheets and includes the Hyperlinks so I can go from the [Main] page to each of the individual pages ( worksheets ) and back.
I would like to automate this build ( as I have to create 100+ PDFs per month ) using VBA.
In the Excel IDE, I added reference to [AdobePDFMakerForOffice] and the code below (which I copied from another post on this forum ) but it only creates one page ( active sheet ) !
Can anyone help with what I am doing wrong ?
Cannot find any documentation on the Usage or Example on the subject.
Thanks Aubrey
Sub rxSaveAsPDF(pSaveAs)
Dim xx As Integer, retVal As Long
Dim appAdobe As AdobePDFMakerForOffice.PDFMaker
Dim appSettings As AdobePDFMakerForOffice.ISettings
For xx = 1 To Application.COMAddIns.Count
If InStr(UCase(Application.COMAddIns(xx).Description), "PDFMAKER") > 0 Then Set appAdobe = Application.COMAddIns(xx).Object
Next xx
appAdobe.GetCurrentConversionSettings appSettings
appSettings.OutputPDFFileName = pSaveAs
appSettings.ConvertAllPages = True
appSettings.CreateFootnoteLinks = True
appSettings.CreateXrefLinks = True
appSettings.AddTags = False
' appSettings.ShouldShowProgressDialog = True
' appSettings.ViewPDFFile = True
appSettings.FitToOnePage = False
appSettings.PromptForSheetSelection = False
appSettings.AddLinks = True
appSettings.AddTags = True
appSettings.AddBookmarks = True
appSettings.AdvancedTagging = True
appAdobe.CreatePDFEx appSettings, 0
Set appAdobe = Nothing
Set appSettings = Nothing
End Sub