List All Files in a Folder and Create Hyperlinks to Each File, Excel VBA
Previously in the article Find and List All Files and Folders in a Directory I’ve explained how you can list all the files in a folder using VBA for Excel. In this article I will explain how you can use VBA to list all the files in a folder, and create hyperlinks to each file.
This can be achieved using the code below:
Sub Example1()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("D:StuffBusinessTemp")
i = 1
'loops through each file in the directory
For Each objFile In objFolder.Files
'select cell
Range(Cells(i + 1, 1), Cells(i + 1, 1)).Select
'create hyperlink in selected cell
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
objFile.Path, _
TextToDisplay:=objFile.Name
i = i + 1
Next objFile
End Sub
The code assumes the files are located in the path “D:StuffBusinessTemp”. The code below creates the hyperlink at the path specified. The text to display will be the name of the file:
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
objFile.Path, _
TextToDisplay:=objFile.Name
Assuming the following files are located in the directory:
The result will be the worksheet below:
The cells in column A are hyperlinks to the files
You can download the file and code used in this article from the link below:
See also:
- Excel VBA, Find and List All Files in a Directory and its Subdirectories
- Find and List all Files and Folders in a Directory
If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com
23 thoughts on “List All Files in a Folder and Create Hyperlinks to Each File, Excel VBA”