Results 1 to 4 of 4

Bulk Hyperlinks?

This is a discussion on Bulk Hyperlinks? within the Excel Questions forums, part of the Question Forums category; So Excel lets you create hyperlinks from cell text (right-click, hyperlink), ok. What if you have several thousand files somewhere ...

  1. #1
    New Member
    Join Date
    Jul 2005
    Posts
    2

    Default Bulk Hyperlinks?

    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? :o

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330

    Default

    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.
    Code:
    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?
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    New Member
    Join Date
    Jul 2005
    Posts
    2

    Default 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?

  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330

    Default

    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,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com