Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Contents of multiple PDF files copied to Excel

    I would like a macro that selects numerous PDF files, I suppose one at a time, all in the same folder, copies the content from each, then paste that content in column A of Sheet1.

    Ultimately, if there are 100 PDF files in the folder, the A column would contain 100 blocks of text. The PDF files are all the same format, so if there are 15 lines in each PDF file, the resulting A column will have 1500 lines.

    I'm wondering if this is too far a reach for even an Excel macro to do. Maybe just impossible...maybe not.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Each line can be selected. The PDFs contain time-sheet data all in the same format.
    Once all of the info is in Excel, I can parse what is needed.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi kweaver

    ..you can do this with VBA.
    ..are you going to make me create some test pdf files first, or can you attach a couple of non-sensitive sample pdfs to make it easier for us.

    zeddy

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I'll have to get some test files without sensitive data from the user. Might not be this weekend, but next week early.

    Actually, these two would be good illustrations.
    Attached Files Attached Files
    Last edited by kweaver; 2016-04-02 at 13:36.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi kweaver

    Thanks for the sample files.
    I'll have a look at this in the morning.

    zeddy

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi kweaver

    ..bit delayed after West Indies knocked England for six. Actually it was four consecutive sixes in the last over of the T20 Cricket Final. Good job I support both teams.

    So, here is my posted file, along with a zip file containing 20 test pdf files (just file copies as per your post renamed).

    To make it more available for others to contribute, my file assumes you only have the free Adobe Reader installed (rather than say, Adobe Acrobat Pro DC).

    When I was testing, I believe that you might get a different number of rows of data imported, depending on the actual pdf timesheet values. So rather than append them into a single column, I dumped each processed file into its own column, on the [PDF Dump] sheet in my file.

    My posted file here is a modified version of something I did previously here, so it includes the capability to move processed files to a chosen destination folder afterwards etc etc etc.

    And I included some simple extracted results for each file processed, on the [Main] sheet.

    You may have to tinker with the 'time allowed' settings in the VBA code for working with the Adobe Reader.
    And I know that using sendkeys method isn't the most elegant.
    But it worked OK in my testing.
    I look forward to seeing more efficient adaptions.

    Finally, since you mention there may be a hundred files to process, the processing will scroll as each file is processed, 'retaining' the last 10 files on screen.

    zeddy
    Attached Files Attached Files

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Fantastic. Sorry about the England loss.
    I will look at these later. I have Acrobat Pro XI but not DC.

    Thanks.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    ..I assume you have the Adobe Reader as well then.
    (My file works with the free Reader rather than money-no-object Acrobat)

    zeddy

Posting Permissions

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