Automate creation of a table of hyperlinks?

  • Thread starter Thread starter Herb
  • Start date Start date
H

Herb

I would like to create a simple spreadsheet consisting of a single
column, where each entry is a hyperlink to a related image file (located
in the same folder as the spreadsheet).

It's easy, but laborious, to do the cells one at a time - either with
the Hyperlink function or Insert Hyperlink.

I can easily generate the list of text entries (just the image file
names, to start) with a DOS DIR command to create an importable text
file. But haven't been able to figure out how to convert these into
Hyperlinks in one fell swoop!

- Herb
 
Herb

You could try this macro from David McRitchie

Sub MakeHyperlinks()
'David McRitchie
Dim cell As Range
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
With Worksheets(1)
.Hyperlinks.Add Anchor:=cell, _
Address:=cell.Value, _
ScreenTip:=cell.Value, _
TextToDisplay:=cell.Value
End With
Next cell
End Sub

Gord Dibben Excel MVP
 
Well, that looked very promising, but I'm not sure I know what I'm doing
with it. I install the macro successfully, enter a fully qualified file
name into one or more cells, then execute the macro. All I get is a
Visual Basic error message,
"Run time error '1004':
Application-defined or object-defined error"

Any additional help or pointers would be welcome!

- Herb
 
Hi Herb

Try this code;

Sub MakeHyperlinks()
Dim rCell As Range
For Each rCell In Selection.SpecialCells _
(xlConstants, xlTextValues)
rCell.Hyperlinks.Add Anchor:=rCell, _
Address:=rCell, ScreenTip:=rCell, _
TextToDisplay:=rCell
Next rCell
End Sub

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Thanks Dave -

When I first ran this, it hiccupped over the ScreenTip and TextToDisplay
parameters, but when I deleted them, it ran perfectly.

Thanks again.

- Herb
-
 
Back
Top