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: