Announcement

Collapse
No announcement yet.

Help with converting VBA code for Adobe to Foxit

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help with converting VBA code for Adobe to Foxit

    Hi,

    At work we've changed to Foxit after using Adobe's products for many years.
    We have an old excel spreadsheet with vba code to fill a pdf form with data.
    The problem now is that the vba code is written to match Adobe functions, but it doesn't work with Foxit.
    Is there anyone here who can help me convert the code to work with Foxit? Unfortunately the person who created the file has left the company.

    The data looks something like this, and starts in A1 in Excel:
    Emp_No Rank Name License
    12345 ABC Rehnstrom ABC-12345
    There's only one line of data, but there's about 40 columns with data.
    Adjusting the code below to this is no problem, but getting all the functions to work with Foxit is my problem.

    Vba-code as found below:

    Option Explicit

    Sub WritePDFForms()

    'Declaring the necessary variables.
    Dim strPDFPath As String
    Dim strFieldNames(1 To 4) As String
    Dim i As Long
    Dim j As Integer
    Dim LastRow As Long
    Dim objAcroApp As Object
    Dim objAcroAVDoc As Object
    Dim objAcroPDDoc As Object
    Dim objJSO As Object
    Dim strPDFOutPath As String

    'Disable screen flickering.
    Application.ScreenUpdating = False

    'Specify the path of the sample PDF form.
    'Using workbook path:
    strPDFPath = ThisWorkbook.Path & "" & "Test.pdf"

    'Set the required field names in the PDF form.
    strFieldNames(1) = "Emp_no"
    strFieldNames(2) = "Rank"
    strFieldNames(3) = "Name"
    strFieldNames(4) = "License"

    'Find the last row of data in sheet Write.
    Worksheets("Test").Activate
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

    'Loop through all rows of sheet Write and use the data to fill the PDF form.
    For i = 2 To LastRow

    On Error Resume Next

    'Initialize Acrobat by creating the App object.
    Set objAcroApp = CreateObject("AcroExch.App")

    'Check if the object was created.
    If Err.Number <> 0 Then
    MsgBox "Could not create the App object!", vbCritical, "Object error"
    'Release the object and exit.
    Set objAcroApp = Nothing
    Exit Sub
    End If

    'Create the AVDoc object.
    Set objAcroAVDoc = CreateObject("AcroExch.AVDoc")

    'Check if the object was created.
    If Err.Number <> 0 Then
    MsgBox "Could not create the AVDoc object!", vbCritical, "Object error"
    'Release the objects and exit.
    Set objAcroAVDoc = Nothing
    Set objAcroApp = Nothing
    Exit Sub
    End If

    On Error GoTo 0

    'Open the PDF file.
    If objAcroAVDoc.Open(strPDFPath, "") = True Then

    'Set the PDDoc object.
    Set objAcroPDDoc = objAcroAVDoc.GetPDDoc

    'Set the JS Object - Java Script Object.
    Set objJSO = objAcroPDDoc.GetJSObject

    On Error Resume Next

    'Fill the form fields.
    For j = 1 To 4

    objJSO.GetField(strFieldNames(j)).Value = CStr(Worksheets("Test").Cells(i, j).Value)

    If Err.Number <> 0 Then

    'Close the form without saving the changes.
    objAcroAVDoc.Close True

    'Close the Acrobat application.
    objAcroApp.Exit

    'Inform the user about the error.
    MsgBox "The field """ & strFieldNames(j) & """ could not be found!", vbCritical, "Field error"

    'Release the objects and exit.
    Set objJSO = Nothing
    Set objAcroPDDoc = Nothing
    Set objAcroAVDoc = Nothing
    Set objAcroApp = Nothing
    Exit Sub

    End If
    Next j

    On Error GoTo 0

    'Create the output path
    With Worksheets("Test")
    strPDFOutPath = ThisWorkbook.Path & "\Forms" & .Cells(i, 1).Value & " " & .Cells(i, 3).Value & ".pdf"
    End With

    'Save the form as new PDF file.
    objAcroPDDoc.Save 1, strPDFOutPath

    'Close the form without saving the changes.
    objAcroAVDoc.Close True

    'Close the Acrobat application.
    objAcroApp.Exit

    'Release the objects.
    Set objJSO = Nothing
    Set objAcroPDDoc = Nothing
    Set objAcroAVDoc = Nothing
    Set objAcroApp = Nothing

    Else

    MsgBox "Could not open the file!", vbCritical, "File error"

    'Close the Acrobat application.
    objAcroApp.Exit

    'Release the objects and exit.
    Set objAcroAVDoc = Nothing
    Set objAcroApp = Nothing
    Exit Sub

    End If

    Next i

    'Enable the screen.
    Application.ScreenUpdating = True

    'Inform the user that forms were filled.
    MsgBox "All forms were created successfully!", vbInformation, "Finished"

    End Sub

  • #2
    Rehnstrom
    Let me submit it to our engineer team, see if they can convert VBA code for you, I will back to you if I had updates. BTW, may I have this excel file for testing?

    Comment


    • #3
      Roy_Chen Many thanks!
      Unfortunately I'm unable to share the file since the forum doesn't accept .xslm being attached to a post. Please get back to me, via PM or likewise and I'll see what I can do.

      Comment


      • #4
        Rehnstrom
        You can submit a ticket by sending me an email to [email protected] with the file and tile attend Roy, I will take and reply with any updates.

        Comment

        Working...
        X