---

Excel VBA: Create a List of Hyperlinks

Excel VBA: Create a List of Hyperlinks to List all Excel Workbooks

 

Got any Excel Questions? Free Excel Help.

 

Create a Hyperlinked List of Excel Workbook File Names

With the use of Excel VBA Macro code we can create a list of Hyperlinked Excel Workbook names on any Excel Worksheet. The code below can be used on any version of Excel, 2000 or higher. The only changes needed are to the file paths used. The part of the code that reads: .Filename = "Book*.xls" has been commented out but can be uncommented if you wish to restrict the list to that of specific Excel Workbook name. Note also the code 'as is' restricts the list to only Excel Workbooks. However, this can be any of the File types listed below;

MsoFileType can be one of these MsoFileType constants

msoFileTypeAllFiles
msoFileTypeBinders
msoFileTypeCalendarItem
msoFileTypeContactItem
msoFileTypeCustom
msoFileTypeDatabases
msoFileTypeDataConnectionFiles
msoFileTypeDesignerFiles
msoFileTypeDocumentImagingFiles
msoFileTypeExcelWorkbooks
msoFileTypeJournalItem
msoFileTypeMailItem
msoFileTypeNoteItem
msoFileTypeOfficeFiles
msoFileTypeOutlookItems
msoFileTypePhotoDrawFiles
msoFileTypePowerPointPresentations
msoFileTypeProjectFiles
msoFileTypePublisherFiles
msoFileTypeTaskItem
msoFileTypeTemplates
msoFileTypeVisioFiles
msoFileTypeWebPages
msoFileTypeWordDocuments

Ensure the active Worksheet at the time of running the code is clean so as to not over write existing data.

Sub HyperlinkXLSFiles()

Dim lCount As Long



Application.ScreenUpdating = False

Application.DisplayAlerts = False

Application.EnableEvents = False



On Error Resume Next



    With Application.FileSearch

        .NewSearch

         'Change path to suit

        .LookIn = "C:\MyDocuments\Testings"

        .FileType = msoFileTypeExcelWorkbooks

       ' .Filename = "Book*.xls"

        

            If .Execute > 0 Then 'Workbooks in folder

                For lCount = 1 To .FoundFiles.Count 'Loop through all.

                    ActiveSheet.Hyperlinks.Add Anchor:=Cells(lCount, 1), Address:= _

                    .FoundFiles(lCount), TextToDisplay:= _

                     Replace(.FoundFiles(lCount), "C:\MyDocuments\Testings\", "")

                 Next lCount

            End If

    End With

    

    On Error GoTo 0

    Application.ScreenUpdating = True

    Application.DisplayAlerts = True

    Application.EnableEvents = True

End Sub

 

See also:

Custom Excel Formulas - User Defined Functions/Formulas

Parse/Extract File Name from Full File Name and Path

Excel: Get Maximum Number Between 2 Numbers

Group Excel Worksheets/Sheets by Color

Hide Pivot Table Fields Pivot Items by Criteria

Excel: Get Underlying Hyperlink Address

 

See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

Gallery

---

• 
• 
• 
• 
• 
•