VBA Macro Not Running

[BlakeT]

Hey everyone, thanks for any time or assistance, I'm a complete novice when it comes to this stuff.

I'm attempting to run a script in Excel that automatically opens a large amount of hyperlinks. Both scripts I'm attempting to use are from here: 3 Easy Ways to Batch Open Multiple Hyperlinks in Your Excel - Data Recovery Blog

The scripts I've tried are this, for a selected highlighted range in the file

Sub BatchOpenHyperLinks_SelectedRanges()
    Dim objSelectedRange As Excel.Range
    Dim objHyperlink As Excel.Hyperlink

    'Get selected ranges
    Set objSelectedRange = Excel.Application.Selection
    For Each objHyperlink In objSelectedRange.Hyperlinks
        objHyperlink.Follow
    Next
End Sub

and

Sub BatchOpenHyperLinks_Workbook()
    Dim objWorksheet As Excel.Worksheet
    Dim objSelectedRange As Excel.Range
    Dim objHyperlink As Excel.Hyperlink

    'Process each worksheet
    For Each objWorksheet In ThisWorkbook.Sheets
        For Each objHyperlink In objWorksheet.Hyperlinks
            objHyperlink.Follow
        Next
    Next
End Sub

I've made sure to change the security preferences to allow for VBA scripts/macros to run, but I'm still unsuccessful. I've tried implementing the code in a Module, in the Workbook itself, and in the individual Worksheet, and I'm still not getting anywhere.

Does anyone know what could be causing these to fail? I'm not getting any kind of error message that would begin to point me in the right direction. Every time I click "Run" (from VBA or from the Excel document), simply nothing happens.

Again, any help is appreciated.

 

[samitnair]

Try This

Sub HyperlinkOpen()
Dim rng As Range
For Each rng In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
    If rng.Hyperlinks.Count > 0 Then
        ThisWorkbook.FollowHyperlink rng.Hyperlinks(1).Address
    End If
Next
End Sub

 

[BlakeT]

Hey Samitnair, thank you for your reply and help! While unfortunately, this didn't work, it did help point me in the right direction.

Unfortunately, I have a new problem.

The Batch Open Hyperlink codes in the OP now are working, however they only work for plain text hyperlinks. "www.google.com" will work, but the spreadsheet I'm using has automatically generated hyperlinks that input the contents of a different row of cells into the hyperlink to complete it. If I manually write out the link address and input the contents of the cell to complete the address, the macro operates fine, but that defeats the purpose of the automatic generation of the links in the first place.

I've removed the hyperlink Friendly Name so that it literally reads out the full hyperlink instead of the shorthand friendly name, however, it appears that this function doesn't work at all with automatically generated Excel hyperlinks. Is there a different Dimension or Object name to use when referring to Excel auto-generated links?

(Hell, I even tried mashing multiple cells together to chain together the components of the link itself with =HYPERLINK(B18&""&Y18&""&Z18) and still got nothing.)

 

[rlv01]

...If I manually write out the link address and input the contents of the cell to complete the address, the macro operates fine,

That suggests your automatically generated hyperlinks are not properly formed. You've not explained the autogeneration process, but perhaps you can use some string variables to make sure that your autogenerated links match what they would be if you typed them by hand?

 

[BlakeT]

That suggests your automatically generated hyperlinks are not properly formed. You've not explained the autogeneration process, but perhaps you can use some string variables to make sure that your autogenerated links match what they would be if you typed them by hand?

Thank you for your help and pointing this out. The auto-links work when I manually click them, and without giving out too much information, they're structured like so:

=HYPERLINK("https://www.paypal.com/[redacted-address-information]=" &B8 & "#/caseDetails")

Where &B8 puts the information from the cell into the middle of the link to complete it.

Is there a more proper way of structuring the cell's function to get that hyperlink working in the same manner?

 

[rlv01]

If I manually write out the link address and input the contents of the cell to complete the address, the macro operates fine, but that defeats the purpose of the automatic generation of the links in the first place.

Can you post your modified macro to show how you are passing the hyperlink address created by the formula to the macro?

 

[BlakeT]

I'm actually using the same macro that operates off of the highlighted cells, so:

Sub BatchOpenHyperLinks_SelectedRanges()
    Dim objSelectedRange As Excel.Range
    Dim objHyperlink As Excel.Hyperlink

    'Get selected ranges
    Set objSelectedRange = Excel.Application.Selection
    For Each objHyperlink In objSelectedRange.Hyperlinks
        objHyperlink.Follow
    Next
End Sub

It just refuses to work with hyperlinks on cells created via formula, it appears, which I feel like is a glaring omission for Excel to have if that's the case.

 

[rlv01]

Since the root issue is that cells that use the Hyperlink formula are not added to the worksheet's hyperlink collection, an alternate method would be to just dynamically add them before using VBA to follow the link. One example:

'Add cells with Hyperlink formulas to the hyperlinks collection, so VBA can use them
Sub OpenSelectedHyperLinks()
    Dim HL As Hyperlink
    Dim ARow As Long, ACol As Long
    Dim FA As Variant
    Dim HLAddr As String
    Dim SelRange As Range

    Set SelRange = Selection
    FA = SelRange.Formula

    If TypeName(FA) = "Variant()" Then
        'Add all cells using the =HYPERLINKS formula to the worksheets hyperlink collection
        With SelRange
            FA = .Formula
            For ARow = 1 To UBound(FA, 1)
                For ACol = 1 To UBound(FA, 2)
                    If UCase(Left(FA(ARow, ACol), 10)) = "=HYPERLINK" Then
                        HLAddr = Mid(Split(Left(FA(ARow, ACol), Len(FA(ARow, ACol)) - 1), ",")(0), 12, Len(FA(ARow, ACol)))
                        .Cells(ARow, ACol).Hyperlinks.Add Anchor:=.Cells(ARow, ACol), Address:=Evaluate(HLAddr)
                    End If
                Next ACol
            Next ARow
        End With
    End If

    'Add code to follow the hyperlinks.
    For Each HL In SelRange.Hyperlinks
        Select Case MsgBox("Cell: " & HL.Range.Address & vbCr & vbCr & _
              "Link Addr: " & HL.Address & vbCr & vbCr & "Follow?", vbYesNoCancel _
               + vbDefaultButton2 + vbQuestion, Application.Name)
        Case vbYes
            HL.Follow
        Case vbCancel
            Exit Sub
        End Select
    Next HL

    'When finished, delete all hyperlinks for cells using the =HYPERLINKS formula or else the collection hyperlink will override the hyperlink formula when cell clicked.
    If TypeName(FA) = "Variant()" Then
        With SelRange
            For ARow = 1 To UBound(FA, 1)
                For ACol = 1 To UBound(FA, 2)
                    If UCase(Left(FA(ARow, ACol), 10)) = "=HYPERLINK" Then
                        .Cells(ARow, ACol).Hyperlinks.Delete
                        .Cells(ARow, ACol).Formula = FA(ARow, ACol)
                    End If
                Next ACol
            Next ARow
        End With
    End If
End Sub

 

[BlakeT]

God, I really appreciate you taking the type to take a stab at all of this. Sadly, that script still only makes the sheet recognize the manually typed out hyperlinks and does nothing with the ones created via formula. But it was really interesting to see how you added the message prompt box to the process!

Classic Microsoft to make something that should be obvious a pain in the ***.

 

[rlv01]

God, I really appreciate you taking the type to take a stab at all of this. Sadly, that script still only makes the sheet recognize the manually typed out hyperlinks and does nothing with the ones created via formula. But it was really interesting to see how you added the message prompt box to the process!

Classic Microsoft to make something that should be obvious a pain in the ***.

FWIW, I created a range of links including cells with hyperlink formulas similar to the example you posted, and for me the code I posted recognizes ALL links, including the formula hyperlinks.