To post as a guest, your comment is unpublished.· 3 months agoHi, Ellie,
To list all folder and subfolder names from a directory, the below VBA code can help you, please try, hopt it can help you.
Application.ScreenUpdating = False
Dim xPath As String
Dim xWs As Worksheet
Dim fso As Object, j As Long, folder1 As Object
.Title = "Choose the folder"
On Error Resume Next
xPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
Set xWs = Application.ActiveSheet
xWs.Cells(1, 1).Value = xPath
xWs.Cells(2, 1).Resize(1, 5).Value = Array("Path", "Dir", "Name", "Date Created", "Date Last Modified")
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder1 = fso.getFolder(xPath)
xWs.Cells(2, 1).Resize(1, 5).Interior.Color = 65535
xWs.Cells(2, 1).Resize(1, 5).EntireColumn.AutoFit
Application.ScreenUpdating = True
Sub getSubFolder(ByRef prntfld As Object)
Dim SubFolder As Object
Dim subfld As Object
Dim xRow As Long
For Each SubFolder In prntfld.SubFolders
xRow = Range("A1").End(xlDown).Row + 1
Cells(xRow, 1).Resize(1, 5).Value = Array(SubFolder.Path, Left(SubFolder.Path, InStrRev(SubFolder.Path, "\")), SubFolder.Name, SubFolder.DateCreated, SubFolder.DateLastModified)
For Each subfld In prntfld.SubFolders
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.
List all files in a folder and create hyperlinks for them at once:
With Kutools for Excel's Filename List utility, you can list all files from a folder which includingt the subfolders into a worksheet as soon as possible.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
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() 'Update 20150831 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 60 days.|
If you have installed Kutools for Excel, please do with following steps:
1. Click Enterprise > 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;
(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:
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 months agoHow would I change this code to have it list folder names rather than file names? And how would I change it to check subfolders for files?
To post as a guest, your comment is unpublished.· 5 months ago"VBA: List all filenames in a folder and create hyperlinks" is great! It works perfectly! Thank you for posting! One question... Now that you told us how to add links from folder, is it possible to use those links to get information from say one or two different sheets in each of the files listed? They would be the same cells for each of the files we now have a list for. This would help create a comprehensive contents with active links to otherwise unintelligible file names.
To post as a guest, your comment is unpublished.· 1 years agoGreat thanks although can not view the files in the selected directory as barry said, it creates hyperlinks for all files in that directory anyway.
To post as a guest, your comment is unpublished.· 1 years agoAwesome ..thanks a lot. It really saved my time.
To post as a guest, your comment is unpublished.· 3 years agoRunning the VBA code listed above, shows the selected directory is empty, when it is full of Excel files.