0

I am putting together a database on Excel that sstores contact information for a group of people. This information is gathered through a .pdf that people fill out on their computer and e-mail into me.

Sometimes, I have to share the information about a specific person by making up a summary of somebody's information. To do so, I have the following Macro that opens a .pdf tempalte and fills in the details.

My questions is, is there a simple way to have this Macro do the opposite? That is, take fields from a .pdf and use the information to populate a new row in my Excel table.

Sub CreatePDFForms():

Dim PDFTemplateFile, NewPDFName, SavePDFFolder, FullName As String
Dim ApptDate As Date
Dim CustRow, LastRow As Long
With Sheet1

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

LastRow = .Range("B9999").End(xlUp).Row  'Last Row
PDFTemplateFile = .Range("E2").Value 'Template File Name
SavePDFFolder = .Range("E4").Value 'Save PDF Folder
ThisWorkbook.FollowHyperlink PDFTemplateFile
Application.Wait Now + 0.00006

CustRow = .Range("K2")
FullName = .Range("B" & CustRow).Value 'Full Name
Application.SendKeys "{Tab}", True
Application.SendKeys FullName, True
Application.Wait Now + 0.00001

Application.SendKeys "{Tab}", True
Application.SendKeys .Range("H" & CustRow).Value, True 'Date of Birth
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True

Application.SendKeys Format(.Range("I" & CustRow).Value, "####-###-####"), True 'Phone
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True

Application.SendKeys .Range("L" & CustRow).Value, True 'School
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True

Application.SendKeys .Range("N" & CustRow).Value, True 'House
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True

Application.SendKeys .Range("O" & CustRow).Value, True 'Housemaster
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True

Application.SendKeys Format(.Range("P" & CustRow).Value, "####-###-####"), True 'Housemaster Phone
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True

Application.SendKeys .Range("AC" & CustRow).Value, True 'Emergency Contact
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True

Application.SendKeys Format(.Range("AF" & CustRow).Value, "###-###-####"), True 'Emergency Phone
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True

Application.SendKeys .Range("AE" & CustRow).Value, True 'Emergency EMAIL
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True


Application.SendKeys "^(p)", True
Application.Wait Now + 0.00003
Application.SendKeys "{Enter}", True
Application.Wait Now + 0.00007

Application.SendKeys "^(q)", True
Application.SendKeys "{numlock}%s", True


End With
End Sub

EDIT: The comments have directed me to other questions like mine, but these do not provide a means within VBA to do this, they require using other 3rd parties software. Here is some code I have developed from other users' to do the reverse, the problem I am having now is that Excel refuses to paste. This is a problem I have seen mentioned here (How to copy-paste from DOS application to Excel using SendKeys?) but is unresolved.

Sub GetPDFForms()
Dim PDFFile As String, textPDF As String
Dim ApptDate As Date
Dim objPDF As MSForms.DataObject
Dim CustRow, LastRow As Long

With Sheet1
If .Range("O2").Value = Empty Then
    MsgBox "Must browse and select .pdf file."
    Exit Sub
End If

LastRow = .Range("B9999").End(xlUp).Row  'Last Row
PDFFile = .Range("O2").Value 'File Name
ThisWorkbook.FollowHyperlink PDFFile
Application.Wait Now + 0.00006

CustRow = .Range("O4")
Application.SendKeys "{Tab}", True 'move to next field in .pdf
Application.SendKeys "^(c)", True 'copy
Application.Wait Now + 0.00006
AppActivate "Excel" 'return to Excel
Application.Wait Now + 0.00006
ActiveSheet.Range("E" & CustRow).Select
Application.Wait Now + 0.00006
Application.SendKeys "^(v)", True 'paste
Application.Wait Now + 0.001

End With
End Sub

So when I run this, the programme works until it has to paste in the Excel cell (which it has selected). When I Esc out of the process, it actually pastes the text into the VBA module. So what is stopping Excel from accepting the command?

  • Welcome to Stack Overflow! Luckily for you this question was already asked [here](https://stackoverflow.com/questions/36270247/extract-data-from-pdf-and-add-to-worksheet) and there is already a few answers there that should help you solve this issue. – DecimalTurn Jul 06 '20 at 22:34
  • Hi there, unfortunately the thread you linked had no answer beyond utilising 3rd party programme like Adobe which I do not have acces too. I have developed some code that does the trick, though it does not 'paste' at the end. I will paste the code above. –  Jul 07 '20 at 14:39
  • What about this answer? https://stackoverflow.com/a/49341685/5958842 – DecimalTurn Jul 07 '20 at 16:42
  • I am sorry, but it requires ByteScout and I would prefer a solution that only uses Excel VBA and Adobe Acrobat Reader (free version). –  Jul 07 '20 at 17:21
  • Sorry, I meant this one https://stackoverflow.com/a/53343855/5958842 – DecimalTurn Jul 07 '20 at 17:57

0 Answers0