Bulk Hyperlinks?

[gbram]

So Excel lets you create hyperlinks from cell text (right-click, hyperlink), ok. What if you have several thousand files somewhere on your intranet? (e.g. \\servername\sharename\file500.xls, file501.xls, etc) and you want one file list in a spreadsheet to hyperlink to all of them. (e.g. FILE500 as a hyperlink) You don't wanna manualy create hyperlinks for 1000 cells! Is there a more automated way of doing this?

 

[Barrie Davidson]

Welcome to the Board!

Here's one way. The following code will prompt the user to select the files you'd like listed with a hyperlink to each file. The list is created in a new workbook.

Sub HyperlinkedList()
'Written by Barrie Davidson
Dim FilestoLink As Variant
Dim i As Integer

FilestoLink = Application.GetOpenFilename("Excel files (*.xls),*.xls", , "Create hyperlinked list", , True)
If IsArray(FilestoLink) = False Then
    If FilestoLink = False Then Exit Sub
End If
Workbooks.Add
For i = 1 To UBound(FilestoLink)
    ActiveSheet.Cells(i, 1).Value = FilestoLink(i)
    ActiveSheet.Hyperlinks.Add anchor:=Cells(i, 1), _
        Address:=FilestoLink(i)
Next i
ActiveSheet.Columns.AutoFit

End Sub

Does this help?

 

[gbram]

Bulk Hyperlinks

Forgive me, not much of a coding background here. Where is this script code entered? Do I save it as a .vbs file then run it? I tried that and got an error. What's the best way of executing this?

 

[Barrie Davidson]

Sorry, from the menu select Tools|Macro|VisualBasicEditor (or ALT+F11). Find your workbook that you want to run the code from in the Project window (usually the top left). Left click on it and select Insert|Module. Paste that code in the module window (on the right side). Close the Visual Basic Editor Window (or ALT+Q). To run it: Tools|Macro|Macros, select the macro name (HyperlinkedList) and click on run. Post back results.

Regards,