Macro to insert hyperlinks

  • Thread starter Thread starter Felipe
  • Start date Start date
F

Felipe

Hi all,

I have a list of file names (including their location and
type of file) in a column in excel. This workbook is
supposed to work as an index for the contents of all files
in it's folder and subfolders

What I want to do is to automatically create hyperlinks to
all files, doing this manually would take a lot of time,
there are more than 1000 files.

I wrote the following code, but the hyperlinks it creates
don't work:

Sub File_hyperlink()
Dim i As Integer
Dim myRange As Range

Set myRange = Selection

With myRange
For i = 1 To .Rows.Count
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:=ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1").Select
Next i
End With
End Sub

I have two doubts:
1. What should I be including in the cells in order to
produce correct links? currently the cells' content looks
like:
Subfolder1\Subfolder2\Filename.xls
2. If this content is Ok, what should the code be?
Can anyone help?

Thanks in advance,
Felipe
 
Did you try the shell command?

Shell ("excel c:\test.xls")
or
shell("excel" & range("A1"))
 
Maybe you can build your hyperlinks like:

file:\\c:\my documents\excel\book1.xls

If I type that in manually, it gets converted to a hyperlink and loads the file
when I click on it.

ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:="file:\\" & ActiveCell.Value

(with no validity checks at all!)
 
Back
Top