VBA to Search Folder of PDF Docs & Return based on Criteria

[xcellrodeo]

Hi MrExcel Community

I would like to ask for some help with achieving the following:
BASE INFO: I have a folder with 50-100 PDF documents. Each PDF document has about 10-15 pages with a cover page.
The cover page is the first page.
TASK: Search the first page of each PDF Document to see if it matches any of the 1 to 4 criteria below AND has a date.
Each PDF document cover page will most likely have one of the Criteria 1 to 4 AND a Date.

CRITERIA 1) "Build Survey"
CRITERIA 2) "Build Sample"
CRITERIA 3) "Build Removal"
CRITERIA 4) "Build Qlty"

CRITERIA 5) "Date" followed by the actual date i.e 14 November 2020 Note: This is on every cover page!

RESULT: In Excel Sheet named 'Results', I would like the following results returned for each PDF searched.
Col A = Name of PDF file (i.e File Name)
Col B = Name of the Criteria found (as mentioned above Criteria 1-4)
Col C = 'Date' followed by the actual date in each document on page 1
NOTE: If the search comes across a PDF which does not fulfil all or any of the above criteria, I would like it still listed
in the Excel Sheet 'Results' but with comment added saying 'Not found'

Hope this makes sense.

Thanks so much for your help


PS: Excel vs is 2010

 

[xcellrodeo]

Any help would be appreciated

 

[brawnystaff]

In order for this to work, you would need Acrobat Pro installed on your computer and NOT just the Adobe Reader. Do you have Acrobat Pro?

 

[xcellrodeo]

In order for this to work, you would need Acrobat Pro installed on your computer and NOT just the Adobe Reader. Do you have Acrobat Pro?

Hi Brawnystaff, Thanks for your message. Unfortunately I only have Adobe Reader not the Pro vs. Out of interest, why is the Pro vs required since all that is needed in the above process is for the PDF documents to be opened and searched by Criteria on 1st page only? No changes to the PDF document is required. Thanks

 

[brawnystaff]

Typically, Excel cannot read PDF files natively and needs to use APIs/references available only in Acrobat Pro to see into them.

That being said, Excel 365 has a new feature under Power Query that imports PDF content as Tables. Believe this is only available in the "Insider Channel" and is not so much for searching PDFs as much as importing them, although I am sure something in M query could be used to return results for the keywords you are looking for.

 

[brawnystaff]

See link below for Excel 365 beta feature on importing PDFs...

Import PDF Data into Excel with Power Query - Excel University

 

[Macropod]

Depending on the Office version, you could open & read the PDFs with Word. However, it seems the OP is only using Office 2010, which doesn't support that.

 

[xcellrodeo]

Typically, Excel cannot read PDF files natively and needs to use APIs/references available only in Acrobat Pro to see into them.

That being said, Excel 365 has a new feature under Power Query that imports PDF content as Tables. Believe this is only available in the "Insider Channel" and is not so much for searching PDFs as much as importing them, although I am sure something in M query could be used to return results for the keywords you are looking for.

Hi again, yes thats an interesting suggestion. However I don't have Office 365 I'm afraid. Looking at the Excel University link you sent, would it not be possible to create a VBA script to perform this function via a Loop? For example the 'Open a pdf file at specified page number and scale.' ...can't this function be used to open and copy over into say a designated Excel sheet all the first pages for each PDF in a given folder? Might end up with 50 - 100 front pages. Not sure how that would be imported though....Does that get downloaded as data that can then be subsequently searched for the Criteria I mention above or is it best to import all the first pages into a Word doc instead and then write a VBA script to open and search all docs in that word doc? Or is it best to write a VBA which converts 50 -100 PDFs into Word doc and then opens and checks each Word doc if one of the Criteria is matched? Thanks

 

[xcellrodeo]

Depending on the Office version, you could open & read the PDFs with Word. However, it seems the OP is only using Office 2010, which doesn't support that.

Hi Macropod, thanks for your comments. Please see my above reply to brawnystaff. Thanks

 

[Macropod]

FWIW, it doesn't have to be Office 365 - any Office version after Office 2010 will do.