Tweet
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:
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
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 |
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
Comment