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 CodeYou can download the code in a .xlsm workbook, or the plain text file. You can also copy and paste the code below.
Option Explicit Sub ListHyperlinkFiles() ' Written by Philip Treacy, http://www.myonlinetraininghub.com/author/philipt ' My Online Training Hub http://www.myonlinetraininghub.com/create-hyperlinked-list-of-files-in-a-folder-using-vba ' April 2014 Dim MyDirectory As String Dim MyFilename As String Dim CurrentRow As Long Dim StartingCell As String 'Cell where hyperlinked list starts 'Make this a cell address to insert list at fixed cell 'e.g. StartingCell = "A1" StartingCell = ActiveCell.Address 'Ask for folder to list files from With Application.FileDialog(msoFileDialogFolderPicker) .InitialFileName = Application.DefaultFilePath & "\" .Title = "Please select folder to list files from" .Show 'If a folder has been selected If .SelectedItems.Count <> 0 Then MyDirectory = .SelectedItems(1) & "\" 'Get the first file, look for Normal, Read Only, System and Hidden files MyFilename = Dir(MyDirectory, 7) 'Need to blank all existing hyperlinks Columns(Range(StartingCell).Column).ClearContents 'Clear formatting from starting cell 'Enter default message in case no files are in folder With Range(StartingCell) .ClearFormats .Value = "No files found in " & MyDirectory .Select End With 'While there are files, list them Do While MyFilename <> "" ActiveCell.Offset(CurrentRow).Hyperlinks.Add Anchor:=ActiveCell.Offset(CurrentRow), Address:=MyDirectory & MyFilename, TextToDisplay:=MyFilename CurrentRow = CurrentRow + 1 'Get the next file MyFilename = Dir Loop End If End With End Sub