Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel VBA: Create a List of Hyperlinks

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

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help .

See Also: Loop Through Folder

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: Loop Through Folder

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft