Bulk Hyperlinks?

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Bulk Hyperlinks?

  1. #1
    New Member
    Join Date
    Jul 2005
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

User Tag List

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