Using VBA to open links in non-default browser

[peddy00]

Hi,

I have a pretty simple script to open the hyperlink in a cell:

Selection.Hyperlinks(1).Follow NewWindow:=False

My default browser is FireFox, but I'd like the script to open the link in Chrome? Can VBA do that?

Thanks for your help.

Peter

 

[Scott Huish]

In a standard module:

Private Declare Function SHGetFolderPath Lib "shell32.dll" Alias "SHGetFolderPathA" (ByVal hwndOwner As Long, ByVal nFolder As Long, ByVal hToken As Long, ByVal dwFlags As Long, ByVal lpszPath As String) As Long
 
Private Const S_OK = &H0
Private Const S_FALSE = &H1
Private Const E_INVALIDARG = &H80070057
Private Const SHGFP_TYPE_CURRENT = 0
Private Const SHGFP_TYPE_DEFAULT = 1

Public Function GetFolder(ByVal lngFolder As Long) As String

    Dim strBuffer As String * 1000
    Dim strPath As String
    Dim lngReturn As Long
    
    lngReturn = SHGetFolderPath(0&, lngFolder, 0&, SHGFP_TYPE_CURRENT, strBuffer)
    
    If lngReturn = S_OK Then
        strPath = Left$(strBuffer, InStr(strBuffer, Chr$(0)) - 1)
    Else
        strPath = "(error)"
    End If
    
    GetFolder = strPath
    
End Function

Sub ChromeLink()
Shell GetFolder(&H1C) & "\Google\Chrome\Application\chrome.exe " & Selection
End Sub

 

[peddy00]

Hi Scott,

Thanks for writing that out for me. I'm actually using a trigger, and have no experience with functions in VBA. If I insert the script for the function in a standard module, and then replace my command above with your command below, will that do the trick?

Shell GetFolder(&H1C) & "\Google\Chrome\Application\chrome.exe " & Selection

Thanks for the help.

Peter

 

[Scott Huish]

What do you mean a trigger? Do you mean event code, or is your code in a standard module as well?

 

[peddy00]

Sorry for being unclear, it's an event. When there's a change in the worksheet the script acts.

 

[Scott Huish]

Change the code in the standard module to this:

Public Declare Function SHGetFolderPath Lib "shell32.dll" Alias "SHGetFolderPathA" (ByVal hwndOwner As Long, ByVal nFolder As Long, ByVal hToken As Long, ByVal dwFlags As Long, ByVal lpszPath As String) As Long
 
Public Const S_OK = &H0
Public Const S_FALSE = &H1
Public Const E_INVALIDARG = &H80070057
Public Const SHGFP_TYPE_CURRENT = 0
Public Const SHGFP_TYPE_DEFAULT = 1

Public Function GetFolder(ByVal lngFolder As Long) As String

    Dim strBuffer As String * 1000
    Dim strPath As String
    Dim lngReturn As Long
    
    lngReturn = SHGetFolderPath(0&, lngFolder, 0&, SHGFP_TYPE_CURRENT, strBuffer)
    
    If lngReturn = S_OK Then
        strPath = Left$(strBuffer, InStr(strBuffer, Chr$(0)) - 1)
    Else
        strPath = "(error)"
    End If
    
    GetFolder = strPath
    
End Function

and if you are using Event code, you probably shouldn't need Selection, but I don't know exactly what you are doing, the following example is code that if you put a hyperlink in Column A, it will open it in Chrome:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Set c = Intersect(Range("A:A"), Target)
If c Is Nothing Then Exit Sub
If c.Cells.Count > 1 Then Exit Sub
If c.Hyperlinks.Count Then Shell GetFolder(&H1C) & "\Google\Chrome\Application\chrome.exe " & c
End Sub

[/code]

 

[peddy00]

Thanks. That's really helpful.

Peter

 

[Excelentemexicano]

Hi Experts companions as they solve to navigate Google Chrome is fabulous, try to do for Mozilla Firefox, but my little knowledge in how macros allow. I believe that is the statement "GetFolder" and on this I based. Have any of you dear friends could clarify my doubt? In advance thank you.

 

[Scott Huish]

Try this:

Private Declare Function SHGetFolderPath Lib "shell32.dll" Alias "SHGetFolderPathA" (ByVal hwndOwner As Long, ByVal nFolder As Long, ByVal hToken As Long, ByVal dwFlags As Long, ByVal lpszPath As String) As Long
 
Private Const S_OK = &H0
Private Const S_FALSE = &H1
Private Const E_INVALIDARG = &H80070057
Private Const SHGFP_TYPE_CURRENT = 0
Private Const SHGFP_TYPE_DEFAULT = 1

Public Function GetFolder(ByVal lngFolder As Long) As String

    Dim strBuffer As String * 1000
    Dim strPath As String
    Dim lngReturn As Long
    
    lngReturn = SHGetFolderPath(0&, lngFolder, 0&, SHGFP_TYPE_CURRENT, strBuffer)
    
    If lngReturn = S_OK Then
        strPath = Left$(strBuffer, InStr(strBuffer, Chr$(0)) - 1)
    Else
        strPath = "(error)"
    End If
    
    GetFolder = strPath
    
End Function

Sub FirefoxLink()
Shell GetFolder(&H26) & "\Mozilla Firefox\firefox.exe " & Selection
End Sub

 

[Excelentemexicano]

Thanks estimated expert, not really find a way to give solution to my question. I consider much their great support. Regards.