Announcement

Collapse
No announcement yet.

Extract text from pdf file to excel using vba code

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Extract text from pdf file to excel using vba code



    Hi all vba gurus,


    I need to extract text from a .pdf report into excel using vba code.
    The pdf file has a lot of data that is not needed. I would like to have a macro in an excel (.xls) file and need the code to do as follows:


    1. Ask to choose the pdf file
    2. From the file extract data from 8 columns in the table (ones which I have highlighted)
    3. Import into excel in 1 row in 8 different columns.
    4. Loop the entire pdf file through all tables and repeat steps 2 & 3.


    I am attaching 2 sample pdf files with data and need the highlighted text to be imported into a .xls file.
    I really appreciate any help.

    Regards
    Attached Files

  • #2
    Re: Extract text from pdf file to excel using vba code

    I doubt very much whether Excel can do this. Converting a pdf into a usable excel file is rarely successful even with Adobe
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: Extract text from pdf file to excel using vba code

      thank you for your response royUK.
      I do understand it would be complicated to do the above, but im a total novice at vba. Would it be easier if the extraction is from a .txt or .doc or something. I could convert the pdf into .doc.
      Many thanks and regards.

      Comment


      • #4
        Re: Extract text from pdf file to excel using vba code

        It depends how good the conversion is, Excel would be the easiest or a text file
        Hope that Helps

        Roy

        New users should read the Forum Rules before posting

        For free Excel tools & articles visit my web site

        RoyUK's Web Site

        royUK's Database Form

        Where to paste code from the Forum

        About me.

        Comment


        • #5
          Re: Extract text from pdf file to excel using vba code

          Well if you can make a DOC file that would be much easier than a PDF. Remember that PDF originally started out to be like a "PICTURE" of a document. And you have a form which makes things even harder.

          If this can be extracted into a DOC flie that would be ok. As you can then record and clean up a macro opening the file and gettting the information. A text file would be the best as you don't have to contend with formatting.
          Regards,
          Barry

          My Favorite New Thing:
          Dynamic Named Ranges



          The alternative for
          "Press Any Key To Continue."

          and we all have one we'd like to use it on

          1. Cross Posting Etiquette
          2. Are You Here To Learn: What Have You Tried?
          3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

          Comment


          • #6
            Re: Extract text from pdf file to excel using vba code

            Many Thanks to everyone for all the suggestions... I guess, I'll get in the data through .txt and try to work smthing out.

            Regards.

            Comment


            • #7
              Re: Extract text from pdf file to excel using vba code

              post a .txt file!
              Regards,
              Barry

              My Favorite New Thing:
              Dynamic Named Ranges



              The alternative for
              "Press Any Key To Continue."

              and we all have one we'd like to use it on

              1. Cross Posting Etiquette
              2. Are You Here To Learn: What Have You Tried?
              3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

              Comment


              • #8
                Re: Extract text from pdf file to excel using vba code

                Dear iwrk4dedpr,

                Attached is a .txt file and a .doc file. In the .doc file, in RED (manually colored) is the data that I need in 8 columns in a row in an excel file. Could not highlight the data in .txt that's why I added the word document.

                However, for the benefit of those seeking to extract tabular data from a pdf document, the best results I have achieved so far for my needs are as below.

                1. Convert the pdf to .doc using a free converter which can be downloaded from the below link.

                http://en.kioskea.net/download/downl...-pdf-converter

                2. In word, convert tables to text using
                Convert to Text
                from the Layout Tab.

                In my case, I found a vba code and tweaked it a bit (not the best tweak job i must admit) to do step 2, to loop thru all tables in the document and convert tables to text.

                Below is the code for MS Word. May be some one could clean it up
                Code:
                Sub FindTables1()
                      
                      Dim tTable As Table
                      'If any tables exist, loop through each table in collection.
                      For Each tTable In ActiveDocument.Tables
                         tTable.Select
                         Selection.Rows.ConvertToText Separator:=wdSeparateByParagraphs, _
                        NestedTables:=True
                        
                         If response = vbNo Then Exit For 'User chose to leave search.
                      Next
                      MsgBox prompt:="Search Complete.", buttons:=vbInformation
                
                
                End Sub
                Regards.
                Attached Files

                Comment


                • #9
                  Re: Extract text from pdf file to excel using vba code

                  Excel can open a PDF in Acrobat Reader then copy and paste the FIRST PAGE ONLY into Excel. Each row of data is pasted as a single cell. I copy many files this way but after 30-50 files are 'processed' the clipboard gets bloated and the Reader crashes the code and the only way to free it up is to reboot. As such, it is only good for a handful of files at a time.

                  The basic coded is as follows: (private stuff has been stripped out)

                  adobeApp = "C:\Program Files\Adobe\Reader 10.0\Reader\AcroRd32.exe"
                  folderLoc = "M:\Source Files\folder1\Reports\"
                  numFiles = 0
                  i = -1
                  fName = Dir(folderLoc)
                  While fName <> ""
                  numFiles = numFiles + 1
                  i = i + 1
                  ReDim Preserve fNameArray(numFiles - 1)
                  fNameArray(i) = fName
                  fName = Dir()
                  Wend
                  If numFiles > 0 Then
                  For i = 0 To UBound(fNameArray)
                  adobeFile = folderLoc + fNameArray(i)
                  fName = Dir(adobeFile)
                  fileDate = Mid(fName, 6, 8)
                  fileYear = Left(fileDate, 4)
                  fileMonth = Mid(fileDate, 5, 2)
                  tagMonth = Left(MonthName(fileMonth), 3)
                  fileDay = Mid(fileDate, 7, 2)

                  Set ws4 = Worksheets(tagMonth & " " & Right(fileYear, 2))
                  lRow = ws4.UsedRange.Rows.Count
                  startAdobe = Shell("" & adobeApp & " " & adobeFile & "", 1)
                  Application.Wait (Now + TimeValue("00:00:05"))

                  SendKeys ("^a")
                  SendKeys ("^c")
                  Application.Wait (Now + TimeValue("00:00:05"))

                  'This re-activates MS Excel.
                  AppActivate "Microsoft Excel"

                  If Worksheets(3).Name = "PDF Dump" Then
                  Sheets("PDF Dump").UsedRange.Clear
                  Cells(1, 1).Select
                  Else
                  Worksheets.Add(AFTER:=Worksheets("whatever")).Name = "PDF Dump"
                  Cells(1, 1).Select
                  End If
                  Set ws3 = Worksheets("PDF Dump")

                  'This moves the file from the current folder to the "Processed" folder after copying the data (remember, only the first page of the PDF gets copied)
                  Name adobeFile As folderLoc & "Processed\" & fNameArray(i)

                  'This pastes the data copied from the PDF into Worksheet(3).
                  ws3.PasteSpecial

                  'This gets the required 'PDF Dump' data and puts it in the appropriate worksheet
                  For r = i to lRow
                  (The data is now text in Excel so I can search, strip, or whatever, so I loop through cells(i,1) looking for what I need etc)

                  I hope this helps.

                  Cheers,
                  BJA




                  Originally posted by royUK View Post
                  I doubt very much whether Excel can do this. Converting a pdf into a usable excel file is rarely successful even with Adobe
                  Last edited by bja; August 7th, 2014, 14:45. Reason: Didn't finish

                  Comment


                  • #10
                    Re: Extract text from pdf file to excel using vba code

                    Please take the time to actually read the Forum Rules and then follow them. Always use code tags when posing code on the Forum.
                    Hope that Helps

                    Roy

                    New users should read the Forum Rules before posting

                    For free Excel tools & articles visit my web site

                    RoyUK's Web Site

                    royUK's Database Form

                    Where to paste code from the Forum

                    About me.

                    Comment


                    • #11
                      Re: Extract text from pdf file to excel using vba code

                      Thanks for another simply useless post Roy. Anyone would think it was your list... is it (Heaven forbid)?

                      Please note that it was YOUR previous stupid comment "I doubt very much whether Excel can do this. Converting a pdf into a usable excel file is rarely successful even with Adobe" which was simply WRONG and led to a lot of time waste for the original poster and led others on the list down a useless path. It also led me to comment on this list for the first (and now last) time.

                      But you keep telling us the 'rules' Roy. I guess you want to be a List Admin too eh?

                      Oh... and BTW... that was my first post BEFORE I got the introduction email that cited the rules, so you should try actually being more polite (not that POMS are renown for that). For me, I've better things to do so I won't post again. That will deny 'little' people like you the pleasure of actually being right for once.

                      BJA
                      For the rest of the list, I'm sure the code block helps some of you, so my apologies. A more appropriate response would have been from the list admin in private.

                      Originally posted by royUK View Post
                      Please take the time to actually read the Forum Rules and then follow them. Always use code tags when posing code on the Forum.

                      Comment


                      • #12
                        Re: Extract text from pdf file to excel using vba code

                        bja,

                        Not quite sure what POMS is .... but having an issue with someone is best kept to private messages.


                        Also from your code it looks like you're rather well versed in coding and probably forums. You did sign up... You did agree to use the code tags .... and you didn't.

                        You have every right to think that someone is a jerk, but Roy's been a long time member and a good contributor you're new and well ... if you're familliar with forums you know you can filter him out so let's keep the personal crap off the walls.

                        Or honestly I'd be happy to ask you to leave!
                        Regards,
                        Barry

                        My Favorite New Thing:
                        Dynamic Named Ranges



                        The alternative for
                        "Press Any Key To Continue."

                        and we all have one we'd like to use it on

                        1. Cross Posting Etiquette
                        2. Are You Here To Learn: What Have You Tried?
                        3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

                        Comment


                        • #13
                          Re: Extract text from pdf file to excel using vba code

                          Simply following Forum Rules is required by everyone no matter how clever you think that you are.

                          Prove your point by adding a working example of the code that allows selecting a pdf. My comment about pdf to Excel is valid and i have tried software supposed to do this and have never seen a good result. Time moves on so it may be possible a software improves.

                          As for your comments about useless posts by me, you state that you have only posted twice, not really necessary to say anymore looking at my posts record here and on other Forums!

                          It's fine to disagree with another member, but referring to me as a Pom is racist and that is not allowed.

                          You accuse me of wanting to be an admin here. That's a joke, I have moderated and administered three Excel Forums

                          Hopefully you will stick to your word and not return here.

                          Whether you posted before you received an email about the rules or not you are asked to read them before you join.
                          Hope that Helps

                          Roy

                          New users should read the Forum Rules before posting

                          For free Excel tools & articles visit my web site

                          RoyUK's Web Site

                          royUK's Database Form

                          Where to paste code from the Forum

                          About me.

                          Comment


                          • #14
                            Re: Extract text from pdf file to excel using vba code

                            Hi, have you tried to convert the PDF files to word? In this case, it becomes much simpler to copy text from PDF.

                            Comment


                            • #15


                              Re: Extract text from pdf file to excel using vba code

                              This thread dates from August 2014 - three years ago.
                              Ali

                              Enthusiastic self-taught user of MS Excel who's always learning!
                              If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

                              Comment

                              Working...
                              X