Loop through all files in folder

This code opens a PDF file and copies the contents to an excel worksheet.

The code works with one file, I am trying adapt the code to loop through all files in the folder, copying each file into its own worksheet, without any success.

Ideally I would like to close the PDF file before opening the next one.

Any suggestions?

Please Login or Register  to view this content.

I have managed to get my code to:
Loop through all the files
Open each file
Add a worksheet for each file

BUT...the code only copies the very last file, all the other sheets are blank.

I am thinking that if I can close each PDF after copying then my code might work?
Any assistance appreciated.

Please Login or Register  to view this content.

I should also add, that the code is selecting the data from each of the PDF files, it is just not copying the data to the worksheet except for the very last file.

Not possible?
Too hard?