Macro to create Hyperlinks in Excel 2003

  Chillie 21:15 05 Apr 07
Locked

Hello everyone, although I have been a member of this forum for sometime and greatly value the comments and advice given, this is my first posting requiring your assistance.

I have an Excel 2003 spreadsheet with over 1000 file names created by Folderprint and I need to hyperlink each file name. the manual process is too much!

Does anyone know how to create a macro to automate the hyperlink process.

Your help would be appreciated

  VoG II 21:21 05 Apr 07

Please record a macro doing this for one cell/hyperlink. To do this, Tools > Macro, Record New Macro. Then go through the process of manually adding a hyperlink. When done click the Stop button on the macro recorder.

Having done that, press ALT + F11 to open the Visual Basic Editor. In the left hand pane click the + next to Modules then double click on Module1. The code should now appear in the right hand window. Copy it and paste it here.

If you do that and tell us the range in which the filenames are we can modify the macro to do the lot.

  Chillie 21:48 05 Apr 07

VoG™

Many thanks for responding so quickly, I was keeping my fingers crossed that you would.

Macro created for 1 cell: Column C has list of filenames (full path), I copy C6 contents, shift to B6, Ctrl-K for hyperlink, then past C6 contents in Hyperlink, then down-arrow to select next cell for hyperlink.

The range of file names is C3-C1044. I had manually done C3-C5

Range("C6").Select
ActiveCell.FormulaR1C1 = "C:\Folder\ADFS\Filename"
Range("B6").Select
ActiveCell.FormulaR1C1 = _
"=HYPERLINK(""[C:\Folder\ADFS\Filename]"")"
Range("B7").Select

Many thanks

  VoG II 21:59 05 Apr 07

OK, try this.

ALT + F11 to open the Visual Basic Editor. This should now open on the 'page' with your recorded macro - you can delete that code if you wish. Then copy and paste in the following:

Sub HYPER()
Dim LastRow As Long, iRow As Long
LastRow = ActiveSheet.Columns(3).Find("*", searchdirection:=xlPrevious).Row
For iRow = 3 To LastRow
ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow, 2), Address:=Cells(iRow, 3).Value, TextToDisplay:=Cells(iRow, 3).Value
Next iRow
End Sub

then close the VBE. With the sheet that you want to work on selected, Tools > Macro > Macros, click on HYPER then click the Run button.

This code will (should?) run down column C from row 3 to the last row (whatever that row is) and add hyperlinks in the same row of column B.

  Chillie 22:11 05 Apr 07

VoG™

It worked like a dream!

I now need to sit quietly and try to understand how the macro works.

I am extremely grateful for your help.

Will mark this as resolved.

Have a good night.

  VoG II 22:21 05 Apr 07

You are welcome!

You will see that I didn't use your recorded code - the macro recorder can be useful but not in this case. The info you gave me was sufficient. Sorry for wasting your time. Here is a commented version of the code:

Sub HYPER()

' first dimension the variables - they are declared as Long as there could be up to 65,536 rows
Dim LastRow As Long, iRow As Long

' find the last used row in column C (3)
' an alternative is
' LastRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
' ActiveSheet is the currently selected sheet


LastRow = ActiveSheet.Columns(3).Find("*", searchdirection:=xlPrevious).Row

' now loop through rows 3 to the last filled row in column C

For iRow = 3 To LastRow

'Anchor is where the hyperlink is added
'Address is the location to hyperlink to
'TextToDisplay is what the hyperlink displays as (i.e. the displayed text)

ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow, 2), Address:=Cells(iRow, 3).Value, TextToDisplay:=Cells(iRow, 3).Value
Next iRow
End Sub

  Chillie 22:30 05 Apr 07

VoG™

Again many thanks. No time wasted here. I have gained valuable information and much needed help. Your explanation also makes it easier for my one good grey cell!!

All the best.

  fewa1982 21:52 16 Feb 09

Hi guys,

I am working on a similar situation, but instead of a list of files I only have the file names without the .suffix

So my problem is that if there isn't a full file name the code doesn't seem to want to work.

Now I have simaler code that searches a location and uses just the file name sans the suffix and shows me the files with hyperlinks. I have been trying to combined the two sets of code with little luck.

any help you guys can provide would help a lot

  fewa1982 23:43 19 Mar 09

Sub FIND_DRAWING2()
Dim MyFolder As String
MyFolder = "C:\DATA\EXCEL\_FORUM\OzGrid\HyperLink"
Dim LASTROW As Long
Dim FIRSTROW As Long
Dim I As Long
Dim f As Integer
Dim MyFileCount As Integer
Dim WS As Worksheet
Set WS = ActiveSheet
FIRSTROW = 2
LASTROW = Range("A" & Rows.Count).End(xlUp).Row
For I = FIRSTROW To LASTROW
FindText = Range("A" & I).Value
MyFileType = "*" & FindText & "*.*" ' = "*Test*.*"
'- CHECK FILE NAMES
With Application.FileSearch
.NewSearch
.LookIn = MyFolder
.Filename = MyFileType
'- RESULTS
MyFileCount = 0
If .Execute() > 0 Then
MyFileCount = .FoundFiles.Count
For f = 1 To MyFileCount
MyFileName = .FoundFiles(f)
WS.Hyperlinks.Add Anchor:=Range("E" & I), Address:=MyFileName, TextToDisplay:= _
Replace(.FoundFiles(f), MyFolder & "\", "")
Next
Else
MsgBox ("Search for file names containing : " & FindText & vbCr _
& "No matches found")
End If
End With
Next I

This thread is now locked and can not be replied to.

Surface Pro 5 News - release date, UK price, features, specs

Microsoft Surface Studio hands-on review: the iMac killer is here

Best Mac antivirus 2017