Loop through PDF files in a folder and save data in Excel

[Legacy 316613]

Hi All,

I am a very amateur and green VBA coder but learning new tricks every day.

Here is a big project I have:

I have PDF documents saved in a specific folder and would like to have the VBA code to do the following:

1) Either access the particular folder automatically or allow me to paste the path of the folder in a particular cell, lets say particular folder is C:\pdf or path of folder is in cell A2
2) I would like the program to loop through each one of the PDF files and paste all the pages into a new workbook which we can call C:\pdf\ExcelData.xlsx or xlsm (whichever is relevant)
3) Once I have the data in Excel and I assume it will be a very big file (I have 100+ pdf's) I want the code to look through the entire file of "bad data" and pick out a specific 8 digit numeric field such as "12345678" and paste it into a worksheet called "Codes" as well as capture the original file name of the PDF - for example file name is PDF1.pdf and the numerical field is "12345678" I would like the code to capture the following in the "Codes" worksheet - Column A - pdf1.pdf and Column B - "12345678"
4) To add further complexities, there may be several numeric fields in the same original PDF file and hence I would like to capture the following in the "Codes" worksheet:
Column A: pdf1.pdf Column B - 12345678
Column A: pdf1.pdf Column B - 23456789

etc...

I know this is asking for a lot but if I did this manually I would be looking at a minimum of 200-300 manhours and here is me hoping that you VBA experts can help me.

I would be using this code regularly and it will save me a lot of time and could lead to a promotion

Plz help!!

 

[Pikopako]

Hi Macropod,

UPDATE

Your code works on my friends laptop and he has Adobe Acrobat Pro DC 2015 installed. I would just need the exact DLL which is needed for the code so that I can copy it. There are so many on the Reference list. I don't need the entire software (I think).

Can I kindly ask you to change the code in such a way that the pdfs are copied into one sheet, in column A, starting from cell A2, 1 under the other?

Anyway thanks in advance.

 

[Macropod]

It doesn't matter which version of Acrobat Professional you use for this.

 

[Pikopako]

Hi Macropod,

I finally got my Adobe Acrobat Pro 2017 installed, modified the macro to my needs and.... stumbled upon another problem. As long as the pdf documents are 1 page everythig works well but in case of pdf with more pages than 1 the output in xlsx ist not the same as the original in pdf i.e. I noticed that the pages are doubled...??? So I get page 1 twice, page 2 twice etc.

Do you know where the problem might lie?

Rgds.

 

[Macropod]

There's nothing in the code I posted that would account for that. Evidently it has something to do with your modifications.