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?
Who is Participating?
Steven HarrisConnect With a Mentor PresidentCommented:
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.

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.

If the data you are looking in resides in A1:A1000, the value you are looking for resides in B1

Open in new window

Joe Winograd - EE 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.