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
Posted by Ayush Jain at 12:55 AM

15 comments:

  1. KimberlyMay 4, 2011 at 8:19 AM

    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. MarcosNovember 4, 2011 at 8:44 AM

    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. Murali MohanJuly 22, 2017 at 4:30 AM

      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. EXCEL SYEDJanuary 12, 2012 at 7:23 AM

    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. AnonymousFebruary 15, 2013 at 10:32 PM

      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. SarahDecember 6, 2012 at 5:14 PM

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

    ReplyDelete
    Replies
    1. UnknownMay 3, 2018 at 1:06 AM

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

      Delete
  5. bcanalsMarch 25, 2013 at 2:49 AM

    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. bayzfinanceMay 21, 2013 at 6:53 AM

    This comment has been removed by the author.

    ReplyDelete
  7. bayzfinanceMay 21, 2013 at 6:54 AM

    I thank you a lot, it works perfectly

    A French developer

    ReplyDelete
  8. AnonymousJune 25, 2013 at 1:18 PM

    I got error 1004, application or object defined error

    ReplyDelete
  9. UnknownSeptember 5, 2015 at 3:08 AM

    I got error
    Can you send a file

    ReplyDelete
  10. UnknownJune 28, 2018 at 3:23 AM

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

    ReplyDelete
  11. huda novalOctober 6, 2019 at 3:13 PM

    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
  12. d1g1t4LnrgApril 12, 2022 at 9:13 AM

    Error Code 53 file not found.

    ReplyDelete

Subscribe to: Post Comments (Atom)