So you have a load of PDF's, or scanned documents, or some nice pictures of your stamp collection*, and you want to get this list into Excel. But you also want to be able to click on that file name/link and have the document/image open up in Excel. Easy.
* I don't have a stamp collection, my photos would more likely be of my bikes, a Trek Speed Concept, and a Scott Foil
How This Code Works
In this code I make use of Application.FileDialog(msoFileDialogFolderPicker) to allow the user to choose the folder.
The Dir() function returns the files in the folder and I use this in a DO ... WHILE loop to list each file on the sheet.
If no files are found, then a message stating that is entered into the active cell, along with the path/name of the folder that was checked.
I've assigned the macro to a shape on Sheet 1 of this workbook, click the shape and the macro runs. But you can create your own shortcut for the macro, or a shape on another sheet, and/or save it in your Personal macro workbook
How to Use This Macro
- Click on a cell, this is where the list will start from.
- Click the big, green shape to run the macro and create the hyperlinked list.
- Make a cup of tea or a sandwich as you just saved yourself a load of time.
Note On the Generated LinksThe generated link (URL) has an absolute path i.e. the link includes the path to the file including the drive e.g. C:\Excel\Sales\Jan_Sales.xlsx. These links will only work on your computer, or where path is the same for all e.g. to a common network drive, or on another computer with the same folder structure as yours.
EnhancementsI can think of a couple of ways to make this macro more flexible and useful, such as;
- Create a userform to specify only certain file types be listed, or excluded. You could do the same thing using check boxes in the worksheet.
- Check sub-folders for the files.
Please ShareIf you found this useful or know someone that would, please use the buttons below to share this on your favorite social network
The VBA Code
Enter your email address below to download the sample workbook.