No announcement yet.

Excel VBA Code will open PDF and close. but will not open the same file again

  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel VBA Code will open PDF and close. but will not open the same file again

    I am having issues with the following VBA Code in Excel.

    I am setting form fields from an excel spreadsheet.
    A row has the details and also the filename of the template file to be used. After a save as has been done, the document closes and the next row is done.
    If I have 2 template files the same in 2 consequctive row the file won't open and the returns VBA error 91 because it can't write to the form field.

    The following is the VBA code.

    Sub MyPdf()

    If Sheet1.Range("G6").Value = Empty Or Sheet1.Range("G8").Value = Empty Then
    MsgBox "Both PDF Template and Saved PDF Locations are required for macro to run"
    Exit Sub
    End If

    Dim FtrRow, FtrLastRow As Long
    FtrLastRow = Sheet2.Range("A9999").End(xlUp).Row 'Last Row
    Sheet2.Range("F2").Value = FtrLastRow

    Dim phApp As PhantomPDF.Application
    Dim phFormDoc As PhantomPDF.Document
    Set phApp = CreateObject("PhantomPDF.Application")

    For FtrRow = 2 To FtrLastRow

    Dim TemplateFileName As String
    TemplateFileName = Sheet1.Range("G4").Value & "" & Sheet2.Range("D" & FtrRow).Value & ".pdf"

    Set phFormDoc = phApp.OpenDocument(TemplateFileName, "", True, True)

    'Name Text Field
    Dim strName As String
    strName = phFormDoc.GetFieldValue("Name")
    Call phFormDoc.SetFieldValue("Name", Sheet2.Range("A" & FtrRow).Value)
    strName = phFormDoc.GetFieldValue("Name")

    'Age Integer Field
    Dim intName As Integer
    intAge = phFormDoc.GetFieldValue("Age")
    Call phFormDoc.SetFieldValue("Age", Sheet2.Range("B" & FtrRow).Value)
    intAge = phFormDoc.GetFieldValue("Age")

    'Date Generated Field
    Dim FtrGeneratedDate As Date
    FtrGeneratedDate = Date

    Sheet2.Range("E" & FtrRow).Value = Format(FtrGeneratedDate, "dd/mm/yyyy")

    Application.Wait Now + 0.00001

    Dim SaveAsFileName As String
    SaveAsFileName = Sheet1.Range("G8").Value & "" & Sheet2.Range("C" & FtrRow).Value & ".pdf"

    Call phFormDoc.Export(SaveAsFileName)

    Call phApp.CloseDocument(phFormDoc, False)

    Next FtrRow

    Call phApp.Exit

    End Sub

    Thank you for your assistance.

  • #2

    Sorry for the inconvenience. I have reported the issue to our team for further investigation, any updates, I will keep you informed. Report ID:QCPHANTOM-12838.

    Please help to check the version of Foxit PhantomPDF you are using.


    • #3
      I put a Application.Wait function for 1 second in after the close function and then it opened the pdf again.

      I am thinking that the time between the close instruction and open instruction was too short without the wait. The open instruction saw the pdf was open before the close function had completed. It then didn't reopen the pdf.

      How can I write the Return value from the CloseDocument function to a variable ? I am thinking I could then write a loop to continue until the close is successful, before trying to open the next pdf.

      Thank you for your assistance.


      • #4
        I am using Version: currently with 3 days left of a free trial.

        However, I think I may have resolved the issue being a coding error.

        I think the scan time between the CloseDocument and OpenDocument commands is too quick, and the OpenDocument command sees it open and thinks its opened the pdf and continues the code. But because the CloseDocument command is still running, it still closes the pdf, which then results in the error further down the code.

        What is the VBA code to retrieve the Return value S_OK when the pdf has been closed sucsessfully. I could then write a loop to allow the pdf to close before trying to open it again.

        Thank you for your assistance.


        • #5

          Thank you for your feedback. I have forwarded your question to our team, any updates, I will keep you informed.