MS-Excel / General Formatting

---

Create a List of Workbook Hyperlinks

Use a bit of Excel VBA macro code to create a list of hyperlinked Excel workbook names on any Excel worksheet.

Using the code in this tutorial, you can get Excel to create a list of all hyperlinked files in an Excel workbook, which is a great tool if you have a large workbook with many hyperlinks in it and you want to see at a glance where the hyperlinks go. If you prefer, you can even restrict the list of hyperlinks to a specific workbook by specifying the workbook name in the part of the code that reads .Filename = "Book*.xls".

This tutorial also works with Excel versions 2000-2003.

The code uses the MsoFileType constant msoFileTypeExcelWorkbooks and is therefore restricted to Excel workbooks only (a .MSO file is a Microsoft Office file type), but you could adapt the code to record any of the following 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

The Code

To insert the code, right-click on your worksheet name, select View Code, go to Insert → Module, and paste the following:

Remember to change the file paths to suit your own environment.

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:\OzGrid Likom\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:\OzGrid Likom\ Testings\", "")
'Change path to suit
		Next lCount
	    End If
    End With
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

Exit and return to Excel proper, and save your workbook.

To run the code, make sure you have a clean worksheet. Then, select Tools → Macros or press Alt/Option-F8, select the macro, and press Run.

Ensure the active worksheet at the time of running the code is clean, to avoid overwriting existing data.