จึงทำการค้นหาใน Internet ก็เจอ code ที่เป็นประโยชน์มาก
สิ่งที่ต้องใช้
1. Acrobat Reader Pro >>> download
2. MS Excel
Code:
Sub WritePDFForms() '-------------------------------------------------------------------------------------- 'This macro uses the data in sheet Write in order to fill a sample PDF form named 'Test Form, which is located in the same folder with this workbook. The data from 'each row is used to create a new PDF file, which is saved in the Forms subfolder. 'The code uses late binding, so no reference to external library is required. 'However, the code works ONLY with Adobe Professional, so don't try to use it with 'Adobe Reader because you will get an "ActiveX component can't create object" error. 'Written by: Christos Samaras 'Date: 15/10/2013 'e-mail: xristos.samaras@gmail.com 'site: http://www.myengineeringworld.net '-------------------------------------------------------------------------------------- 'Declaring the necessary variables. Dim strPDFPath As String Dim strFieldNames(1 To 11) 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. 'Full path example: 'strPDFPath = "C:\Users\Christos\Desktop\Test Form.pdf" 'Using workbook path: strPDFPath = ThisWorkbook.Path & "\" & "Test Form.pdf" 'Set the required field names in the PDF form. strFieldNames(1) = "First Name" strFieldNames(2) = "Last Name" strFieldNames(3) = "Street Address" strFieldNames(4) = "City" strFieldNames(5) = "State" strFieldNames(6) = "Zip Code" strFieldNames(7) = "Country" strFieldNames(8) = "E-mail" strFieldNames(9) = "Phone Number" strFieldNames(10) = "Type Of Registration" strFieldNames(11) = "Previous Attendee" 'Find the last row of data in sheet Write. With shWrite .Activate LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row End With 'Loop through all rows of sheet Write and use the data to fill the PDF form. For i = 4 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 10 objJSO.GetField(strFieldNames(j)).Value = CStr(shWrite.Cells(i, j + 1).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 'Fill the checkbox field. If shWrite.Cells(i, j + 1).Value = "True" Then objJSO.GetField(strFieldNames(11)).Value = "Yes" End If On Error GoTo 0 'Create the output path, which will be like C:\Users\Christos\Desktop\Forms\01) First Name Last Name.pdf. With shWrite If i - 3 < 10 Then strPDFOutPath = ThisWorkbook.Path & "\Forms\0" & i - 3 & ") " & .Cells(i, 2).Value & " " & .Cells(i, 3).Value & ".pdf" Else strPDFOutPath = ThisWorkbook.Path & "\Forms\" & i - 3 & ") " & .Cells(i, 2).Value & " " & .Cells(i, 3).Value & ".pdf" End If 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
อ้างอิงจาก :
http://www.myengineeringworld.net/
http://www.myengineeringworld.net/2013/10/read-and-write-pdf-forms-from-excel-vba.html
0 ความคิดเห็น:
แสดงความคิดเห็น