Extract Data from PDF and Add to Worksheet

by

You can open the PDF file and extract its contents using the Adobe library (which I believe you can download from Adobe as part of the SDK, but it comes with certain versions of Acrobat as well)

Make sure to add the Library to your references too (On my machine it is the Adobe Acrobat 10.0 Type Library, but not sure if that is the newest version)

Even with the Adobe library it is not trivial (you’ll need to add your own error-trapping etc):

Function getTextFromPDF(ByVal strFilename As String) As String
   Dim objAVDoc As New AcroAVDoc
   Dim objPDDoc As New AcroPDDoc
   Dim objPage As AcroPDPage
   Dim objSelection As AcroPDTextSelect
   Dim objHighlight As AcroHiliteList
   Dim pageNum As Long
   Dim strText As String

   strText = ""
   If (objAvDoc.Open(strFilename, "") Then
      Set objPDDoc = objAVDoc.GetPDDoc
      For pageNum = 0 To objPDDoc.GetNumPages() - 1
         Set objPage = objPDDoc.AcquirePage(pageNum)
         Set objHighlight = New AcroHiliteList
         objHighlight.Add 0, 10000 ' Adjust this up if it's not getting all the text on the page
         Set objSelection = objPage.CreatePageHilite(objHighlight)

         If Not objSelection Is Nothing Then
            For tCount = 0 To objSelection.GetNumText - 1
               strText = strText & objSelection.GetText(tCount)
            Next tCount
         End If
      Next pageNum
      objAVDoc.Close 1
   End If

   getTextFromPDF = strText

End Function

What this does is essentially the same thing you are trying to do – only using Adobe’s own library. It’s going through the PDF one page at a time, highlighting all of the text on the page, then dropping it (one text element at a time) into a string.

Keep in mind what you get from this could be full of all kinds of non-printing characters (line feeds, newlines, etc) that could even end up in the middle of what look like contiguous blocks of text, so you may need additional code to clean it up before you can use it.

Hope that helps!

More Related Contents:

  1. Save multiple sheets to .pdf
  2. Excel VBA to Export Selected Sheets to PDF
  3. excel to pdf ignores print area and calls printer
  4. Sequence of numbers based on start and end value of 2 cells [closed]
  5. Function to convert column number to letter?
  6. Better way to find last used row
  7. How to insert a picture into Excel at a specified cell position with VBA
  8. 2 Dimensional array from range [closed]
  9. Having multiple Excel instances launched, how can I get the Application Object for all of them?
  10. How can I create a calendar input in VBA Excel?
  11. Using Google Chrome in Selenium VBA (Installation Steps)
  12. Dir() function not working in Mac Excel 2011 VBA
  13. Merge values of column B based on common values on column A
  14. How to use workbook.saveas with automatic Overwrite
  15. How to import a table from web page (with “div class”) to excel?
  16. Range limit conundrum
  17. Using LocationTextExtractionStrategy in itextSharp for text coordinate
  18. How do you run a .exe with parameters using vba’s shell()?
  19. How to determine the last Row used in VBA including blank spaces in between [duplicate]
  20. How to use FindWindow to find a visible or invisible window with a partial name in VBA
  21. Passing variable from Form to Module in VBA
  22. How to give a time delay of less than one second in excel vba?
  23. Changing the date format to yyyy-mm-dd
  24. Delete all duplicate rows Excel vba
  25. Excel VBA: Convert a date string to a Unix timestamp
  26. Select Newest Record and Create New Table of Unique Values in Excel
  27. Excel VBA – exit for loop
  28. Are ActiveX Controls Disabled?
  29. Converting a string representation of a constant into a constant?
  30. How to protect Excel workbook using VBA?

Leave a Comment