• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatically Open Hyperlinks

Kavish Sekhri

New Member
Can someone please check the following sheet and help me with the VBA for this? Please check the sheet attached and query is as follows:

Query:
I work on data wherein I fill a CompanyID in excel (Column-A) and use plug-in to extract Company Name (Column C) and Country (Column D).
Then, I apply Hyperlink along with Vlookup (in Column E and F) to lookup Country (Column D) value in Sheet 2-Column B and C respectively.

After this I need a macro wherein I can open all the hyperlinks of a particular country to open in a single browser window.

Example:
Value in cell D2- Belgium

Please suggest a macro to open all the hyperlinks in E2, F2, and so on in a browser window....

Note: The formula in E2 and F2 are as follows
E2- =HYPERLINK(VLOOKUP(D2,Sheet2!A:B,2,0))
F2- =HYPERLINK(VLOOKUP(D2,Sheet2!A:C,3,0))

Thanks
Kavish
 

Attachments

  • Source List Automation.xlsm
    38.7 KB · Views: 13
Since formula is used as hyper link and not stored in conventional URL format, you won't be able to use Hyperlinks.Follow method.

You need to use Shell and open IE.
Code:
Dim numRow As Integer
Dim URL As String
numRow = 1
Do While WorksheetFunction.IsText(Range("E" & numRow))
    URL = Range("E" & numRow).Text
    Shell "C:\Program Files\Internet Explorer\iexplore.exe " & URL, vbNormalNoFocus
    numRow = numRow + 1
Loop

This, will open all links in IE but in separate window. Might be best to use another code to add column(s) where conventional hyper links are stored and referenced to use Hyperlinks.Follow. So that you don't have to use Shell.
 
Since formula is used as hyper link and not stored in conventional URL format, you won't be able to use Hyperlinks.Follow method.

You need to use Shell and open IE.
Code:
Dim numRow As Integer
Dim URL As String
numRow = 1
Do While WorksheetFunction.IsText(Range("E" & numRow))
    URL = Range("E" & numRow).Text
    Shell "C:\Program Files\Internet Explorer\iexplore.exe " & URL, vbNormalNoFocus
    numRow = numRow + 1
Loop

This, will open all links in IE but in separate window. Might be best to use another code to add column(s) where conventional hyper links are stored and referenced to use Hyperlinks.Follow. So that you don't have to use Shell.


Hi Chihiro, Thanks for the response..

But the code is not working, when applied it is giving me the following error "Compile error: Invalid outside procedure"
Can you please also help with the code for getting conventional hyper links and then opening them all in a single window? The code which I used lets me open the hyperlinks stored in a single column, but I want to open the hyperlinks in a single row. Please check the original query for further details.

Thanks
Kavish
 
Hmm, odd. It's working on my end. You may need to change path in your environment.

To change to conventional URL/hyperlink, just copy and paste as value column(s) with =HYPERLINK() to another column(s). You may need double click and hit enter for each cell to make it properly anchor.

Link below should help you code in VBA.
http://stackoverflow.com/questions/...-of-text-urls-into-active-hyperlinks-in-excel

This is the code I used. Enter Row# in Input box and then it will look for valid hyperlink in range to open all in same window.

Code:
Sub Test()

Dim nRow As Long
Dim lCol As Long
Dim xHyperlink As Long
nRow = Application.InputBox("Enter Row#", Type:=1)
lCol = ActiveSheet.Cells(nRow, Columns.Count).End(xlToLeft).Column
xHyperlink = Range(Cells(nRow, 1), Cells(nRow, lCol)).Hyperlinks.Count
For n = 1 To xHyperlink
    ActiveSheet.Range(Cells(nRow, 1), Cells(nRow, lCol)).Hyperlinks(n).Follow
Next n
End Sub
 

Attachments

  • Source List Automation_1.xlsm
    36 KB · Views: 7
Hmm, odd. It's working on my end. You may need to change path in your environment.

To change to conventional URL/hyperlink, just copy and paste as value column(s) with =HYPERLINK() to another column(s). You may need double click and hit enter for each cell to make it properly anchor.

Link below should help you code in VBA.
http://stackoverflow.com/questions/...-of-text-urls-into-active-hyperlinks-in-excel

This is the code I used. Enter Row# in Input box and then it will look for valid hyperlink in range to open all in same window.

Code:
Sub Test()

Dim nRow As Long
Dim lCol As Long
Dim xHyperlink As Long
nRow = Application.InputBox("Enter Row#", Type:=1)
lCol = ActiveSheet.Cells(nRow, Columns.Count).End(xlToLeft).Column
xHyperlink = Range(Cells(nRow, 1), Cells(nRow, lCol)).Hyperlinks.Count
For n = 1 To xHyperlink
    ActiveSheet.Range(Cells(nRow, 1), Cells(nRow, lCol)).Hyperlinks(n).Follow
Next n
End Sub




Thanks Chihiro

This code works only when we have direct hyperlinks but it gets difficult to double click on each hyper link and manually convert it. I am using the following code which reads the hyperlink with the formula and splits it into 2 parts web link and formula. Since I am using Vlookup in the sheet to get the hyperlinks the following code read this with the Vlookup function.

________________________________________________________________

Code:
Private Declare Function ShellExecute _
Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hWnd As Long, ByVal Operation As String, _
ByVal Filename As String, Optional ByVal Parameters As String, _
Optional ByVal Directory As String, _
Optional ByVal WindowStyle As Long = vbMinimizedFocus _
) As Long

Sub Sample()
    Dim sFormula As String
    Dim sTmp1 As String, sTmp2 As String
    Dim i As Long
    Dim ws As Worksheet

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets(1)

    i = 2

    With ActiveSheet
        Do While WorksheetFunction.IsText(.Range("G" & i))
            With .Range("G" & i)
                '~~> Store the cells formula in a variable for future use
                sFormula = .Formula

                '~~> Check if cell has a normal hyperlink like as shown in E2
                If .Hyperlinks.Count > 0 Then
                    .Hyperlinks(1).Follow
                '~~> Check if the cell has a hyperlink created using =HYPERLINK()
                ElseIf InStr(1, sFormula, "=HYPERLINK(") Then
                    '~~> Check if it has a friendly name
                    If InStr(1, sFormula, ",") Then
                        '
                        ' The idea here is to retrieve "www."&"Google"&".Com"
                        ' from =HYPERLINK("www."&"Google"&".Com","Google")
                        ' and then store it as a formula in that cell
                        '
                        sTmp1 = Split(sFormula, ",")(0)
                        sTmp2 = "=" & Split(sTmp1, "HYPERLINK(")(1)

                        .Formula = sTmp2

                        ShellExecute 0, "Open", .Text

                        '~~> Reset the formula
                        .Formula = sFormula
                    '~~> If it doesn't have a friendly name
                    Else
                        ShellExecute 0, "Open", .Text
                    End If
                End If
            End With
            i = i + 1
        Loop
    End With
End Sub
_______________________________________________________________

But the problem is that this code executes hyperlinks in a single column, while I want it to execute the hyperlinks in a single row.

Please see if your code can be integrated with the above code so that I can execute hyper links in a row.

Thanks
 

Attachments

  • Source List Automation_1 (1).xlsm
    36 KB · Views: 1
Last edited by a moderator:
Ah I see.

Code:
i = 2

With ActiveSheet
Do While WorksheetFunction.IsText(.Range("G" & i))
With .Range("G" & i)
'~~> Store the cells formula in a variable for future use
sFormula = .Formula

This portion needs to be changed as it's looking at fixed Column.

I don't have time to write and test just now, will get back to you.
 
Here you go.

Had to add sValue = .Value portion as original would not work with nested formula within =HYPERLINK()

It will now store .Value of cells and use that to open links.

Code:
Sub Sample()
    Dim sFormula As String
    Dim sTmp1 As String, sTmp2 As String
    Dim i As Long
    Dim nRow As Long
    Dim ws As Worksheet

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets(1)
    nRow = Application.InputBox("Enter Row#", Type:=1)

    i = 4

    With ActiveSheet
        Do While WorksheetFunction.IsText(.Cells(nRow, i))
            With .Cells(nRow, i)
                '~~> Store the cells formula in a variable for future use
                sFormula = .Formula
                sValue = .Value

                '~~> Check if cell has a normal hyperlink like as shown in E2
                If .Hyperlinks.Count > 0 Then
                    .Hyperlinks(1).Follow
                '~~> Check if the cell has a hyperlink created using =HYPERLINK()
                ElseIf InStr(1, sFormula, "=HYPERLINK(") Then
                    '~~> Check if it has a friendly name
                    If InStr(1, sFormula, ",") Then
                        '
                        ' The idea here is to retrieve "www."&"Google"&".Com"
                        ' from =HYPERLINK("www."&"Google"&".Com","Google")
                        ' and then store it as a formula in that cell
                        '
                        sTmp1 = sValue
                    
                        .Formula = sTmp1

                        ShellExecute 0, "Open", .Text

                        '~~> Reset the formula
                        .Formula = sFormula
                    '~~> If it doesn't have a friendly name
                    Else
                        ShellExecute 0, "Open", .Text
                    End If
                End If
            End With
            i = i + 1
        Loop
    End With
End Sub
 

Attachments

  • Source List Automation_Row.xlsm
    34.9 KB · Views: 1
Thanks a lot for the help Sir...

This seems to be working perfect sometimes but on repeated use it opens only the first hyper link in a row. I tested it multiple times, it opens the first hyper link every time but not all the hyper links. Sometimes after I restart the sheet, then it opens all the hyper links. I don't know if its happening on my system or something needs to be tweaked in the coding.

Can you please check at you convenience..

Thanks :)
 
Hmm... working fine on my end.
It may be that the code is trying to open succession of links too fast for the browser to handle in some environments.

Try putting below code between "i = i +1" and "Loop".
Code:
Application.Wait(Now + TimeValue("00:00:01"))

Adjust length of time for pause as needed. If you need it more precise and want to adjust for less than a second pause...

Add below to the module
Code:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

And this in the code at same location as the first.
Code:
Sleep (1000)

1000 = 1 sec as it's in milliseconds.
 
Hmm... working fine on my end.
It may be that the code is trying to open succession of links too fast for the browser to handle in some environments.

Try putting below code between "i = i +1" and "Loop".
Code:
Application.Wait(Now + TimeValue("00:00:01"))

Adjust length of time for pause as needed. If you need it more precise and want to adjust for less than a second pause...

Add below to the module
Code:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

And this in the code at same location as the first.
Code:
Sleep (1000)

1000 = 1 sec as it's in milliseconds.

Thanks for this :)
Yes this worked for rows where there are 2 sources..the weblinks did open after a pause...but it is not working for rows where there is a single source and the second column is "0". I think the code doesn't work if there is no hyperlink..
 
Can you upload example where it's failing?

My guess is that since the code uses ISTEXT()
If there is error or null value, it stops there.
 
Hi Sir

I have re-attached the sheet as an example after adding the Pause code.

The code works only when there are 2 hyper link values in a row Example- Row no. 18.

For rows where there is 1 hyper link only and the next value is 0, it doesn't open even the first hyper link. Example- Row no. 2, 4, 6, 7 etc.

I even tried removing the formula from Column H but the first link din't execute....

Not sure about the problem...
 

Attachments

  • Source List Automation.xlsm
    46.1 KB · Views: 6
Ah, I see where your problem is.

You changed formula for E:F and left just VLOOKUP. The code you gave looks for =HYPERLINK().

Just leave =HYPERLINK(VLOOKUP(D2,SHEET2!A:B,2,0)) as is for Column E and
=HYPERLINK(VLOOKUP(D2,SHEET2!A:C,3,0)) for Column F.

Also in the code change i = 4 to i = 5

Columns G & H are no longer needed.

P.S. The reason it works for 2 links is that there is no null value between all 4 links and code will skip over E:F and go to G:H. For those that have 0 code will stop there and cannot proceed to G.
 
Back
Top