Sunday, December 12, 2010

Import a PDF in Excel

Here is the Macro to import text from a PDF file to your excel sheet. Make sure you have Adobe Reader 9.0 installed on your PC.

Sub BackToA1()
Range("A1").Select
End Sub

Sub GetPDFnow()
Dim varRetVal As Variant, strFullyPathedFileName As String, strDoIt As String
'Add a new worksheet
Sheets.Add After:=Sheets(Sheets.Count)
'Name it
ActiveSheet.Name = "Input01"
'Back to "A1"
Range("A1").Activate
'HERE YOU DEFINE THE FULLY PATHED PDF FILE
strFullyPathedFileName = "C:\Documents and Settings\ayujain1\Desktop\Excel_Tutorials\Ayush ebooks\14 secret shortcuts of Excel.pdf"
'HERE YOU SET UP THE SHELL COMMAND
strDoIt = "C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe " & strFullyPathedFileName
'The Shell command
varRetVal = Shell(strDoIt, 1)
'Clear CutCopyMode
Application.CutCopyMode = False
AppActivate varRetVal
'Wait some time
Application.Wait Now + TimeValue("00:00:03") ' wait 3 seconds
DoEvents
'IN ACROBAT :
'SELECT ALL
SendKeys "^a"
'COPY
SendKeys "^c"
'EXIT (Close & Exit)
SendKeys "^q"
'Wait some time
Application.Wait Now + TimeValue("00:00:03") ' wait 3 seconds
DoEvents
'Paste
ActiveSheet.Paste
'Go back to cell A1
Call BackToA1
End Sub

15 comments:

  1. Hey, I didn´t know how to do this. It is a good thing to know because several times I nedeed the PDF to be imported to the Excel sheet and I didn´t know how to do it. Especially now, that I´m in Argentina, I rented one of those buenos aires apartments and the rental company gave me a PDF about Argentina and its attractions and i wanted to have it in excel to see in items what I want to do.
    Thank you!
    Kim

    ReplyDelete
  2. As I am not an expert, your work is very useful for me.
    I have a problem with this instruction:
    AppActivate varRetVal
    Send an error #5
    Can you help me?
    Thanks

    Marcos (Santiago, Chile)

    ReplyDelete
    Replies
    1. The error is due to the delay in executing the exe file. Try holding the code for few seconds. It might help.


      'HERE YOU SET UP THE SHELL COMMAND
      strDoIt = "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe " & strFullyPathedFileName

      'The Shell command
      varRetVal = Shell(strDoIt, 1)
      Application.Wait Now + TimeValue("00:00:20")


      'Clear CutCopyMode
      Application.CutCopyMode = False
      Application.Wait Now + TimeValue("00:00:03")
      AppActivate varRetVal

      Delete
  3. my company have a legal notice template..they merged it and fwd us 27000 notices in pdf files..

    there is a sentence
    Contract no. xxxxxxx in every notices...i want that contract no. into excel...automatically A macro batch something

    ReplyDelete
    Replies
    1. I think if you add the acrobat library in your references, you can declare a variable for the pdf file type and get some some help. I think you can then use some search to locate the text you want. I suppose you could search for the next space and select the string of characters, copy, and paste into excel. Have you resolved this yet?

      Delete
  4. Thanks so much for this code. This is very helpful!

    ReplyDelete
    Replies
    1. i have a doubt..in which path the converted excel file is getting saved?

      Delete
  5. Hi,

    I have the same problem as Marcos,

    AppActivate varRetVal
    Send an error #5

    Could you help me out?

    Thank you in advanced.

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. I thank you a lot, it works perfectly

    A French developer

    ReplyDelete
  8. I got error 1004, application or object defined error

    ReplyDelete
  9. I need to copy some specified lines from pdf to excel ,how can it be done can u please give me an idea

    ReplyDelete
  10. I also have a problem with#varRetVal = Shell (strDoIt, 1)please help me, if there is another vba code that can fix it, and the pdf file doesn't appear in the search when I run the code

    ReplyDelete