១ - ពិនិត្យតំណភ្ជាប់រវាង Excel និងPDF ដោយចូលទៅ Macro (Alt+F11)
ដោយចូលទៅ Macro ឬ(Alt+F11) => Tool => Reference
ចុចយកពាក្យ Acrobat => Ok
២ - ទាញយក Field នីមួយៗនៅក្នុង PDF មកដាក់នៅក្នុង Excel
Sub ReadAdobeField()
row_number = 1
Dim AcrobatApplication As Acrobat.CAcroApp
Dim AcrobatDocument As Acrobat.CAcroAVDoc
Dim fcount As Long
Dim sFieldName As String
On Error Resume Next
Set AcrobatApplication = CreateObject("AcroExch.App")
Set AcrobatDocument = CreateObject("AcroExch.AVDoc")
Location = Range("M5").Value 'Refer to
If AcrobatDocument.Open(Location, "") Then
AcrobatApplication.Show
Set AcroForm = CreateObject("AFormAut.App")
Set Fields = AcroForm.Fields
fcount = Fields.Count
For Each Field In Fields
row_number = row_number + 1
sFieldName = Field.Name
Sheet1.Range("B" & row_number) = Field.Name
Sheet1.Range("C" & row_number) = Field.Value
Sheet1.Range("D" & row_number) = Field.Style
Next Field
Else
MsgBox "Failure"
End If
AcrobatApplication.Exit
Set AcrobatApplication = Nothing
Set AcrobatDocument = Nothing
Set Field = Nothing
Set Fields = Nothing
End Sub
៣ - បញ្ចូលពត៌មានតាមField នីមួយៗនៅក្នុង PDF
Sub WriteToPDF()
Dim AcrobatApplication As Acrobat.CAcroApp
Dim AcrobatDocument As Acrobat.CAcroAVDoc
Dim fcount As Long
Dim sFieldName As String
Set AcrobatApplication = CreateObject("AcroExch.App")
Set AcrobatDocument = CreateObject("AcroExch.AVDoc")
Location = Range("M5").Value
If AcrobatDocument.Open(Location, "") Then
AcrobatApplication.Show
Set AcroForm = CreateObject("AFormAut.App")
Set Fields = AcroForm.Fields
fcount = Fields.Count
'Fields("Text1").Value = Range("C2").Value
'Fields("Text5").Value = Range("C3").Value
'Fields("Check Box8").Value = Range("C4").Value
Else
MsgBox "Failure"
End If
AcrobatApplication.Exit
Set AcrobatApplication = Nothing
Set AcrobatDocument = Nothing
Set Field = Nothing
Set Fields = Nothing
End Sub
៤ - ការប្រើប្រាស់ផ្សេងៗ
1. Location:
=LEFT(CELL("filename",C11),FIND("[",CELL("filename",C11))-1)
2. File Name
=SUBSTITUTE(CELL("filename"),RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("@",SUBSTITUTE(CELL("filename"),"\","@",LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"\",""))),1)),"*")
Formulas => Name Manager => Name(FileNameList)=> Refers to (FILES(-sheetname--row&columname--)
=IFERROR(INDEX(FileName,L3),"")