Mass Linking of pdf files to Excel

I need a way to find a filename path for a large amount of files so that I can link them.

I have a spreadsheet that has about 5000 "Lot Numbers"  They are 7 digit numbers.  I have a folder, that has some sub folders, which contain PDF files.  The Main folder, will call it year folder, contains subfolders called, 2013, 2012, 2011, etc.  Within these folders are pdf's that are named, USUALLY with the Lot Number first, then there are additional alpha/numeric characters to represent other data.   I'd like to know how I can find out the path of the file name and put it in my spreadsheet next to the Lot Number.  I suppose if there was a way to dump all the file names into a spreadsheet I could do a VLOOKUP or something...

Any suggestions?
Snapshot-of-PDF-Files.docx
LVL 4
ITworksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steven HarrisPresidentCommented:
I suppose if there was a way to dump all the file names into a spreadsheet I could do a VLOOKUP or something...

That would be simple enough-

Open CMD Shell and navigate to your top level folder - [PATH] is the full path of the folder, minus the drive letter.
cd\[PATH]

Open in new window

Example - If the path is "C:\user\username\folder1\folder2\date\", you would use "cd\user\username\folder1\folder2\date\"

Once you are there, run the following:
dir /b /s > C:\FilePaths.xls

Open in new window

This will write a full listing to a .xls file (located on the C:\ root) that you can open in excel.  Now you can transfer that sheet to your populated workbook and do an INDEX MATCH instead of a VLOOKUP.


INDEX MATCH
If the data you are looking in resides in A1:A1000, the value you are looking for resides in B1
=INDEX(A1:A1000,MATCH(B1,A1:A1000,0))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Joe Winograd, Fellow&MVEDeveloperCommented:
Hi ITworks,
It would be helpful if you embed screenshots directly in your post rather than uploading a Word file. That way, members can see it straightaway in your post, and clicking on it will bring up an even larger image in a new tab. As a Word file, folks have to download it and open it (and, of course, need to have software that will handle a DOCX file). As a JPG or PNG file, it will be handled directly in the browser. Here's a quick-read article, How to Embed Screenshots in Posts, that explains the embedding technique. I have taken the liberty of embedding your image below:
Snapshot-of-PDF-FilesNow, to your issue, but a question first: You say that the names "USUALLY" have the lot number first and that they are "7 digit numbers". What would you like to do when this is not the case (either or both)? For example, one of the files in your screenshot is named <625609>. How do you want that handled? Regards, Joe
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.