Macros to hyperlink using text from original worksheet

[Drewbie57]

I am trying to make a macro that will hyperlink to a new file which has the same filename as the text that the hyperlink has. For example, the hyperlink has the text in the field, "ST_630." I would like the macro to hyperlink to the new file "ST_630.xls".

I would also have to repeat this process a couple hundred times. How would I set up a for loop for this?

Thanks for the help.

 

[Mark O'Brien]

Welcome to the Board!

Here is a simple example of a macro that may suit your needs. I've asumed for the purposes of the example the following:

1. The files are all located in the same directory, i.e. C:\Temp
2. The filenames are located in cells A1 to A10 on Sheet1
3. I'm placing the hyperlink in the cells B1 to B10

Public Sub main()

  Dim oRange As Range
  Dim oCell As Range
  Dim sFilePath As String
  
  Set oRange = Sheets("Sheet1").Range("A1:A10")
  sFilePath = "C:\Temp\"
  
  For Each oCell In oRange
    Sheets("Sheet1").Hyperlinks.Add anchor:=oCell.Offset(0, 1), _
    Address:=sFilePath & oCell.Value & ".xls", _
    TextToDisplay:=oCell.Value
  Next

End Sub

 

[Damon Ostrander]

Hi Drew,

I'm not sure exactly what you have and what you want.

1. Do you currently have a bunch of hyperlinks that are hyperlinked to the wrong files, and you want these hyperlinks corrected to the file named in the hyperlink text?

2. Do you want to automatically process all hyperlinks
- on a current worksheet?
- in a particular range of cells?
- that start with the string "ST"?
- in the entire workbook?

3. Or are your current "hyperlinks" just file names, and you want them converted to true hyperlinks?

Damon

 

[Drewbie57]

Hey everyone. The code from Mark helped me out a lot. I've coded in Java but never Microsoft VBE. I got the problem all worked out, thanks for everyones help.