Tags

, , , ,

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:\Stuff\Business\Temp")
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:\Stuff\Business\Temp”. 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:
File in Folder
The result will be the worksheet below:
Result
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:

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

Advertisements