Hyperlinking in Excel

**Scooternm** · 07-04-2008, 10:43 AM

I don't really know if there is a way to do this, so i figured this is the best place to start. I currently have an Excel spreadsheet that I was manually making. Column A contains entries like:

Log435.log
Log364.log
Log758.log

What I would do is manually make each one of those a hyperlink to C:\Logs\whatever log is referenced.

Now, I have that spreadsheet set up as a query from a SQL Server database. Everything imports fine and the spreadsheet is much better because now I just have to refresh the data.

I am wondering if there is some way through functions or VBA to have Column A entries be hyperlinks. All the logs listed will be in the C:\Logs folder and the whole file name will be the entry in column A.

Anyone know of a way to do this? Thanks for your help!

Scott

**Ron Coderre** · 07-04-2008, 11:24 AM

This procedure attempts to create a hyperlink to the cell contents
for each cell in the currently selected range:

Sub CovertTxt2Hyperlink()
Dim cCell As Range
Dim strPath As String

strPath = "C:\Logs\"     '<--Your file path goes here

For Each cCell In selection.Cells
    
    On Error Resume Next
    ActiveSheet.Hyperlinks.Add _
        Anchor:=cCell, _
        Address:=strPath & cCell.Value, _
        TextToDisplay:= cCell.Value
Next cCell
End Sub

To put that code in your workbook:
• [Alt]+[F11]........to view the Visual Basic Editor (VBE)
• Right-Click on your workbook name in the VBE list
• <insert><module>
• Copy the above code and paste it into that module

To run the macro...
• Select the range of cells with the file names
• [Alt]+[F8]
...Select: CovertTxt2Hyperlink
...Click [Run]

Does that help?

**Scooternm** · 07-04-2008, 11:54 AM

That's a better solution than I was hoping for, I think that will work great for me.

On the first sheet of the workbook, the links will link to a matching entry on another sheet. Basically the first sheet is an overview and then the other sheets are broken down into sections. So, your module will work great on the other sheets. Should that module, with a little tweaking, also work on my first sheet...to link to other sheets rather than an external file.

In any event, you solved a problem I wasn't sure I could solve. Thanks a lot!

Scott

**Ron Coderre** · 07-04-2008, 12:47 PM

If you want to hyperlink to cell C13 on Sheet5....

Enter this in the cell to be converted:
#Sheet5!C13

Note: Before running the CovertTxt2Hyperlink macro,
Set the strPath variable to an empty string:

strPath = ""

Does that help?
Post back with more questions

**Scooternm** · 07-04-2008, 01:39 PM

Thanks a lot. I've been playing around with this and have ONE more question. Going back to the first module...is it possible, rather than having to select the cells to run the macro on....to say if the value in column E is greater than 0, then run on column A of the same row(s)?

**Ron Coderre** · 07-04-2008, 02:07 PM

I need a clarification....

Are you saying that:
Col_A will still contain the hyperlink destination text
and
Col_E will contain a numeric value
AND
you want to only create hyperlinks in Col_A where the
corresponding Col_E value is greater than zero?