Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: PDF to Excel data copy

  1. #1
    Board Regular
    Join Date
    Sep 2008
    Posts
    202

    Default PDF to Excel data copy

    Does anyone if its possible to write code that will select all in a pdf and copy paste to excel?
    I've used the following code to get the PDF file open, which works fine, but thats where it stops for me. If its possible I'd like to be able to continue the code to select all in the PDF doc and paste into excel.
    Any ideas?
    Thanks

    Code:
    Shell "C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe C:\users\richard\desktop\RDS\Richard Pay form.pdf"

  2. #2
    Board Regular crimson_b1ade's Avatar
    Join Date
    Sep 2008
    Location
    Yonkers, New York
    Posts
    1,557

    Default Re: PDF to Excel data copy

    Hey Vinny, try tinkering with SENDKEYS (sending keys: ctrl+a to select all, ctrl+c to copy and ctrl+v to paste). Below is a simple example. I also used Application.OnTime to allow time for the application to open and time for copying (you can change the timing to suit your needs - it set for 5 second delay on opening and 10 second delay on copying). As a side note, it has always been my experience that copying from PDF files into Excel makes a yucky mess as it always loses its pretty PDF formatting:


    Code:
     
    Sub StartAdobe()
     
    Dim AdobeApp As String
    Dim AdobeFile As String
    Dim StartAdobe
     
    AdobeApp = "C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe"
    AdobeFile = "C:\users\richard\desktop\RDS\Richard Pay form.pdf"
     
    StartAdobe = Shell("" & AdobeApp & " " & AdobeFile & "", 1)
     
    Application.OnTime Now + TimeValue("00:00:05"), "FirstStep"
     
    End Sub
    
    Private Sub FirstStep()
    
    SendKeys ("^a")
    SendKeys ("^c")
     
    Application.OnTime Now + TimeValue("00:00:10"), "SecondStep"
     
    End Sub
     
    Private Sub SecondStep()
     
    AppActivate "Microsoft Excel"
     
    Range("A1").Activate
    SendKeys ("^v")
    
    End Sub
    You may not know the secrets of the Universe but there is one thing you should know. Everyone born in and of this Earth knows not more to such matters than you and possesses no more clairvoyance or holiness than you. So if you ought to believe in something…believe in YOURSELF!

  3. #3
    Board Regular
    Join Date
    Sep 2008
    Posts
    202

    Default Re: PDF to Excel data copy

    crimson, many thanks for that, it works great.
    The final touch will be to close the PDF file and program, whcih i've failed to do, if you know how to do that too, that would be excellent.
    thanks

  4. #4
    Board Regular
    Join Date
    Dec 2008
    Location
    India
    Posts
    121

    Default Re: PDF to Excel data copy


    AppActivate "Microsoft Excel"

    Range("A1").Activate
    SendKeys ("^v")

    End Sub
    Hey this code is really cool.......

    Well if I had to paste this on to a Word document would it be possible?

    I tried replacing the above code with

    AppActivate "Microsoft Word"


    SendKeys ("^v")
    It didnt work though....
    It is always good to experiment

  5. #5
    Board Regular crimson_b1ade's Avatar
    Join Date
    Sep 2008
    Location
    Yonkers, New York
    Posts
    1,557

    Default Re: PDF to Excel data copy

    Hi Vinny, you can add another SENDKEYS function (see below revised SecondStep procedure - the item in bold face red is what I added.). By the way, when I was doing all this the only App I had open was Excel. So having other apps open will running the macro could cause unexpected results (e.g. if Adobe was already open). I was hoping you would use this as a general guide to provide you with some direction...any further enhancements are up to you.

    Code:
     
    Private Sub SecondStep()
     
    SendKeys ("%fx")
     
    AppActivate "Microsoft Excel"
     
    Range("A1").Activate
     
    SendKeys ("^v")
    
    End Sub

    Quote Originally Posted by vinny2984 View Post
    crimson, many thanks for that, it works great.
    The final touch will be to close the PDF file and program, whcih i've failed to do, if you know how to do that too, that would be excellent.
    thanks
    You may not know the secrets of the Universe but there is one thing you should know. Everyone born in and of this Earth knows not more to such matters than you and possesses no more clairvoyance or holiness than you. So if you ought to believe in something…believe in YOURSELF!

  6. #6
    Board Regular
    Join Date
    Sep 2008
    Posts
    202

    Default Re: PDF to Excel data copy

    Crimson, many thanks it works perfectly. Your right, if the pdf is already open it crashes, so i'll play with that to see if i can check if its open before it attempts to open.
    Below is the code i used, which is exacly what you sent. There's been no need to change it.
    brilliant, thanks

    Code:
    Sub StartAdobe()
    Dim AdobeApp As String
    Dim AdobeFile As String
    Dim StartAdobe
     
    AdobeApp = "C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe"
    AdobeFile = "C:\users\richard\desktop\RDS\Richard Pay form.pdf"
     
    StartAdobe = Shell("" & AdobeApp & " " & AdobeFile & "", 1)
     
    Application.OnTime Now + TimeValue("00:00:05"), "FirstStep"
    End Sub
    Private Sub FirstStep()
    SendKeys ("^a")
    SendKeys ("^c")
     
    Application.OnTime Now + TimeValue("00:00:10"), "SecondStep"
     
    End Sub
     
    Private Sub SecondStep()
    SendKeys ("%fx")
    AppActivate "Microsoft Excel"
    ThisWorkbook.Activate
    Sheets(1).Activate
    Range("A1").Activate
    SendKeys ("^v")
    End Sub

  7. #7
    Board Regular cgmojoco's Avatar
    Join Date
    Jan 2005
    Location
    San Diego
    Posts
    691

    Default Re: PDF to Excel data copy

    I'm trying to use this to cycle through some files in a directory and copy and paste the contents into new sheets in my workbook...it looks like it fails out at the call to FirstStep...

    Any ideas?

    Code:
    Dim AdobeApp As String
    Dim AdobeFile As String
    Dim StartAdobe
    Dim strPath As String
    Dim strFile As String
    Sub LoopThruDirectory()
        
    
        strPath = "C:\_ImportTest\"
        strFile = Dir(strPath)
    
        Do While strFile <> “”
            Sheets.Add After:=Sheets(Sheets.Count)
            StartAdobeApp
            strFile = Dir    ' Get next entry.
        Loop
        
    End Sub
    Sub StartAdobeApp()
    
    AdobeApp = "C:\Program Files (x86)\Adobe\Acrobat 9.0\Acrobat\Acrobat.exe"
    AdobeFile = strPath & strFile
      
    StartAdobe = Shell("" & AdobeApp & " " & AdobeFile & "", 1)
     
    Application.OnTime Now + TimeValue("00:00:05"), "FirstStep"
    
    End Sub
    Private Sub FirstStep()
    SendKeys ("^a")
    SendKeys ("^c")
    Application.OnTime Now + TimeValue("00:00:10"), "SecondStep"
    Call SecondStep
    End Sub
     
    Private Sub SecondStep()
    SendKeys ("%fx")
    AppActivate "Microsoft Excel"
    ThisWorkbook.Activate
    Range("A1").Activate
    SendKeys ("^v")
    End Sub
    “Success is having to worry about every **** thing in the world, except money.”~Johnny Cash

  8. #8
    Board Regular Emmily's Avatar
    Join Date
    Oct 2008
    Location
    Sydney
    Posts
    676

    Default Re: PDF to Excel data copy

    Crimson, i ran the below, but i got an error "There was an error opening this document. This file cannot be found". I have closed everything apart from excel

    There needs to be flexibility in choosing file via a dialgoue box.


    Quote Originally Posted by crimson_b1ade View Post
    Hey Vinny, try tinkering with SENDKEYS (sending keys: ctrl+a to select all, ctrl+c to copy and ctrl+v to paste). Below is a simple example. I also used Application.OnTime to allow time for the application to open and time for copying (you can change the timing to suit your needs - it set for 5 second delay on opening and 10 second delay on copying). As a side note, it has always been my experience that copying from PDF files into Excel makes a yucky mess as it always loses its pretty PDF formatting:


    Code:
     
    Sub StartAdobe()
     
    Dim AdobeApp As String
    Dim AdobeFile As String
    Dim StartAdobe
     
    AdobeApp = "C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe"
    AdobeFile = "C:\users\richard\desktop\RDS\Richard Pay form.pdf"
     
    StartAdobe = Shell("" & AdobeApp & " " & AdobeFile & "", 1)
     
    Application.OnTime Now + TimeValue("00:00:05"), "FirstStep"
     
    End Sub
     
    Private Sub FirstStep()
     
    SendKeys ("^a")
    SendKeys ("^c")
     
    Application.OnTime Now + TimeValue("00:00:10"), "SecondStep"
     
    End Sub
     
    Private Sub SecondStep()
     
    AppActivate "Microsoft Excel"
     
    Range("A1").Activate
    SendKeys ("^v")
     
    End Sub

  9. #9
    Board Regular cgmojoco's Avatar
    Join Date
    Jan 2005
    Location
    San Diego
    Posts
    691

    Default Re: PDF to Excel data copy

    Emmily....what version of Adobe Acrpbat are you using?...
    “Success is having to worry about every **** thing in the world, except money.”~Johnny Cash

  10. #10
    Board Regular Emmily's Avatar
    Join Date
    Oct 2008
    Location
    Sydney
    Posts
    676

    Default Re: PDF to Excel data copy

    Quote Originally Posted by cgmojoco View Post
    Emmily....what version of Adobe Acrpbat are you using?...

    version 8.0, but i changed that in the code

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com