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.
The VBA Code
Enter your email address below to download the sample workbook.