Inserting a hyperlink using vba in excel

Question

Hi all,

I have an excel sheet that has a text column (B) and another column (H) with urls.  I would like to hyperlink the text columns with the urls.  My thought was to add a formula to the text column in which it copies the hyperlink.  Here's what I have:

With xlWs
.Range("B2").Formula = "= Hyperlink("H2")"
End With

But my syntax for the second line:  .Range("B2").Formula = "= Hyperlink("H2")"   

ia not correct.  Could someone help me figure out how to correct this?

Thank you!!!

Answer 1

It sounds as though you have more than one hyperlink you want to add, so try something like this...

Dim cL As Range
Dim n As String
Dim nM As String

For Each cL In ActiveSheet.Range("A4:A6")
    nM = cL.Value
    If cL.Offset(0, 1).Value <> "" Then
        n = cL.Offset(0, 1).Value
        cL.Hyperlinks.Add Anchor:=cL, Address:=n, ScreenTip:="click me", TextToDisplay:=nM
    End If
Next

The range that I want to contain the hyperlinks in this example is A4:A6.
The range that contains the URLs is B4:B6 (see offset(0,1))
Using this example, the text in A4:A6 remains the same but is now a clickable hyperlink.

---

"The new phonebooks are here!"

Answer 2

Hi suznal,

I tried your example and as I step through the code it works like a charm;  however, when the code completes and then I open the excel file, it whows that column A has links but the links do not seem to work.  Any thoughts?

Here's my code snippet:

Dim xlApp As Excel.Application
Dim xlWs As Excel.Worksheet

Dim lngRowIndex As Long
Dim lngListIndex As Long
Dim myCount As Integer
Dim RowCount As Integer

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("c:\temp\test.xls")
Set xlWs = xlWB.ActiveSheet

Dim cL As Range
Dim n As String
Dim nM As String

For Each cL In ActiveSheet.Range("A2:A26")
    nM = cL.Value
    If cL.Offset(0, 1).Value <> "" Then
        n = cL.Offset(0, 7).Value
        cL.Hyperlinks.Add Anchor:=cL, Address:=n, ScreenTip:="click me", TextToDisplay:=nM
    End If

Next

xlApp.Visible = False
xlWB.Save
xlWB.Close

Set xlWs = Nothing
Set xlWB = Nothing
Set xlApp = Nothing

MsgBox "DONE!!!!!!!!!"

End Sub

Thanks!!!! 

BTW,  what format do you choose when you insert vba code into the forum windows?  :)

Answer 3

Couple of things...

If you are using n as the variable for both the Address and the TextToDisplay, then you can get rid of the following in your code...

Dim nM As String

and...

nM = cL.Value

I think you need to correct your cell offset...
You have this...

    If cL.Offset(0, 1).Value <> "" Then
        n = cL.Offset(0, 7).Value

(0,1 and then 0,7)
I beleive what you want is...

    If cL.Offset(0, 7).Value <> "" Then
        n = cL.Offset(0, 7).Value

You can also right click a cell that contains a hyperlink and select 'edit hyperlink' to see what the link is.
Can you right click a cell and manually add a valid link and see if it works?





Finally, when I enter code I select VB.net (since there isn't an option for VBA).




BTW - I use this code on several workbooks and it works perfectly, so it is tested code.

---

"The new phonebooks are here!"

Answer 4

Thank you suznal!  I discovered what my issue is.  For some reason the file is being saved as a Microsoft Excel 5.0/95 Workbook and what it should be saving it as should be a Excel 97-2003 Workbook.

Any ideas at how I could fix this?

Here's my code snippet.  Thanks!!!

Private Sub Command0_Click()

Dim myQuery As QueryDef
Dim openTable As TableDef
Dim dbCurrent As Database
  
Set dbCurrent = DBEngine.Workspaces(0).Databases(0)

DoCmd.OutputTo acOutputQuery, "qry_Offerings", acFormatXLS, "c:\temp\test.xls", False

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWs As Excel.Worksheet

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("c:\temp\test.xls")
Set xlWs = xlWB.ActiveSheet

Dim cL As Range
Dim n As String

For Each cL In ActiveSheet.Range("A2:A26")
    If cL.Offset(0, 7).Value <> "" Then
        n = cL.Offset(0, 7).Value
        cL.Hyperlinks.Add Anchor:=cL, Address:=n,   
         ScreenTip:="click me"
    End If
Next

xlApp.Visible = False
xlWB.Save
xlWB.Close

Set xlWs = Nothing
Set xlWB = Nothing
Set xlApp = Nothing

MsgBox "DONE!!!!!!!!!"

End Sub

Answer 5

I figured it out:

xlApp.DisplayAlerts = False
xlWB.SaveAs "C:\temp\test.xls", FileFormat:=56
xlApp.DisplayAlerts = True

Thanks for all your help suznal!!!