How to list all filenames in a folder and create hyperlinks for them in Excel?
For your daily work, you may need to import multiple filenames from a folder to a worksheet, and create hyperlinks for each files so that open the file quickly and easily. Maybe, you can copy and paste the filenames and create a hyperlink for it one by one manually, but, this will waste much time if there are hundreds files in the folder. Today, I will talk about some quick and interesting methods to deal with this job.
To list and create hyperlink with files from a folder, the following VBA code can do you a favor.
1. Activate a new worksheet to put the result.
2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
3. Click Insert > Module, and paste the following code in the Module Window.
VBA: List all filenames in a folder and create hyperlinks
Sub Example1() 'Updateby Extendoffice Dim xFSO As Object Dim xFolder As Object Dim xFile As Object Dim xFiDialog As FileDialog Dim xPath As String Dim I As Integer Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker) If xFiDialog.Show = -1 Then xPath = xFiDialog.SelectedItems(1) End If Set xFiDialog = Nothing If xPath = "" Then Exit Sub Set xFSO = CreateObject("Scripting.FileSystemObject") Set xFolder = xFSO.GetFolder(xPath) For Each xFile In xFolder.Files I = I + 1 ActiveSheet.Hyperlinks.Add Cells(I, 1), xFile.Path, , , xFile.Name Next End Sub
4. Then press F5 key to run this code, and choose the folder that you want to list the files from the Browse window, see screenshot:
5. After specifying the folder, click OK button, and all the files in the specific folder have been listed into active worksheet with hyperlinks, see screenshot:
Note: If there are subfolders in your specific folder, the files in the subfolders will not be listed.
To list all files both in a folder and its subfolders and created hyperlinks for each file, Kutools for Excel’s Filename List may do you a favor. With this feature, all files in folder and subfolders will be listed at once with some attributes, such as file name, file size, created time, containing folder and so on.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
If you have installed Kutools for Excel, please do with following steps:
1. Click Kutools Plus > Import & Export > Filename List, see screenshot:
2. In the Filename List dialog box, please do the following operations:
(1.) Click button to select the folder that you want to list the filenames;
(2.) Check Include files in subdirectories if you want to list filenames within subfolders, check Include hidden files and folders if you want to list the hidden files;
(3.) Specify the file types that you want to find and list under the Files type section;
(4.) Select the file size unit that you want to display;
(5.) Check Create hyperlinks at the left bottom of the dialog box.
3. Then click Ok button, and all the files in the folder and subfolders have been listed in a new worksheet with the hyperlinks as you need, see screenshot:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!