2 Replies Latest reply: Jul 3, 2013 2:55 PM by MichaelKazlow RSS

    Help using VBA to auto generate PDF using PDFMaker

    aufer Community Member

      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