VBA to open all hyperlinks in an Excel column

[chasidar]

I have a column (I) of hyperlinks to Google search book titles which are listed in a different column (H). Syntax in column I is: =HYPERLINK("Google" & H2,"Google "&H2). I wanted a VBA which will run through the column and run each search so I don't need to manually. I ran below VBA but the pages that open up are: OneDrive
Any idea why or how to correct?
Sub Open_Link()
Dim iCell As Range

For Each iCell In Range("I1:I60")
If Not IsEmpty(iCell) Then
ThisWorkbook.FollowHyperlink iCell.Value
End If
Next iCell

End Sub

 

[Logit]

Untested here. Let me know if this works for you.

Sub OpenGoogleSearchesInTabs()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim cell As Range
    Dim link As String
    
    ' Set your worksheet and range
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
    Set searchRange = ws.Range("I2:I" & ws.Cells(ws.Rows.Count, "I").End(xlUp).Row)
    
    ' Loop through each cell in the search range
    For Each cell In searchRange
        If cell.Hyperlinks.Count > 0 Then
            link = cell.Hyperlinks(1).Address
            ' Open the hyperlink in a new browser tab
            Shell "cmd /c start """" """ & link & """", vbNormalFocus
            ' Optional: Wait for a short time before opening the next link
            Application.Wait Now + TimeValue("00:00:01")
        End If
    Next cell
End Sub

 

[chasidar]

Untested here. Let me know if this works for you.

Sub OpenGoogleSearchesInTabs()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim cell As Range
    Dim link As String
   
    ' Set your worksheet and range
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
    Set searchRange = ws.Range("I2:I" & ws.Cells(ws.Rows.Count, "I").End(xlUp).Row)
   
    ' Loop through each cell in the search range
    For Each cell In searchRange
        If cell.Hyperlinks.Count > 0 Then
            link = cell.Hyperlinks(1).Address
            ' Open the hyperlink in a new browser tab
            Shell "cmd /c start """" """ & link & """", vbNormalFocus
            ' Optional: Wait for a short time before opening the next link
            Application.Wait Now + TimeValue("00:00:01")
        End If
    Next cell
End Sub

 

[chasidar]

Thanks. Nothing happens when I run the macro.

 

[Logit]

I just had time to test it here. It works.

Paste the macro into a Regular module.

Create a command button on your worksheet. Immediately after pasting the command button a small pop-up window appears. Click on the macro name
"OpenGoogleSearchesInTabs" then click OK.

You can then click the command button to open each of the URLs in your browser.

 

[chasidar]

I just had time to test it here. It works.

Paste the macro into a Regular module.

Create a command button on your worksheet. Immediately after pasting the command button a small pop-up window appears. Click on the macro name
"OpenGoogleSearchesInTabs" then click OK.

You can then click the command button to open each of the URLs in your browser.

I tried with a command button and still nothing happens

 

[Logit]

Workbook download : Internxt Drive – Private & Secure Cloud Storage

 

[chasidar]

Workbook download : Internxt Drive – Private & Secure Cloud Storage

So this works when the hyperlinks are entered in the cell but not when using the hyperlink function.

And my hyperlink searches the text I have in a specific cell
=HYPERLINK("Google" & H2,"Google "&H2)

 

[Logit]

Attempting to place your hyperlink in cell H2 creates a circular reference. Something Excel won't tolerate.
Placing your hyperlink in G2 works with the following macro :

Option Explicit

Sub OpenGoogleSearchesInTabs()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim cell As Range
    Dim link As String
    
    ' Set your worksheet and range
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
    Set searchRange = ws.Range("G2")
    
    ' Loop through each cell in the search range
    For Each cell In searchRange
        If cell.Hyperlinks.Count > 0 Then
            link = cell.Hyperlinks(1).Address
            ' Open the hyperlink in a new browser tab
            Shell "cmd /c start """" """ & link & """", vbNormalFocus
            ' Optional: Wait for a short time before opening the next link
            Application.Wait Now + TimeValue("00:00:01")
        End If
    Next cell
End Sub

???