Macro to insert hyperlinks
 

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

Macro to insert hyperlinks
 

Did you try the shell command?

Shell ("excel c:\test.xls")
or
shell("excel" & range("A1"))


"Felipe" wrote in message
...
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

Macro to insert hyperlinks
 

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!)


Felipe wrote:

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

--

Dave Peterson