Use VBA to open URL in Default-Browser an catch existing Session

Question

I try to open a specific URL of a web-application I'm already logged in (or tells me to login if I'm not) in the default browser (Chrome). When I copy/paste this URL into the browser address bar, it perfectly works. It doesn't when I open this URL by VBA with ThisWorkbook.FollowHyperlink - then it redirects - as a kind of fallback - to the homepage instead the specific URL.

I found out that this is a session problem and VBA somehow doesn't recognize/catch the existing session.

As "ugly workaround" I'm currently redirecting over http://www.dereferer.org/ to the specific URL, what perfectly works, but is needing additional time.

This doesn't work:

ThisWorkbook.FollowHyperlink ("https://www.example.com/function/edit/2019-04-09)

This works:

ThisWorkbook.FollowHyperlink ("http://www.dereferer.org/?https://www.example.com/function/edit/2019-04-09)

(for my needs it's not required to encode the target URL)

As this redirect is slow and indirect, I'm searching for a way to directly open the targeted URL while using the existing session (if possible). If this isn't possible (for example because of security), what's the best/fastest way to redirect without setting up an own redirector (which redirects like dereferer.org over a GET parameter)?

Chronocidal · Accepted Answer · 2019-04-09T14:23:06.650

8

A clunky and ill-advised workaround, but you could bypass FollowHyperlink, and instead use Shell to open the website in a new tab/window of your default web-browser:

Shell "explorer ""https://www.example.com/function/edit/2019-04-09"""

(As a note, if you type as a hyperlink in a cell and clicked on it manually, instead of using VBA FollowHyperlink, then the same issue would still occur. This also happens in Word and PowerPoint. Just be thankful you're not trying to catch the FollowHyperlink event and "correct" that in the window)

In response to comments - for Mac you will need to use "open" instead of "explorer". This code should run on both Mac or PC:

Shell IIf(Left(Application.Operatingsystem, 3)="Win","explorer ","open ") & _
    """https://www.example.com/function/edit/2019-04-09"""

edited Apr 09 '19 at 14:23

answered Apr 09 '19 at 08:55

Chronocidal

6,827
1
12
26

I'll test your solution in the evening. Thanks also for the remark related the hyperlink. For my current target it should work, as I've mapped the Sub/Macro to a button. One additional question: Do you know if this works also for Excel on Mac's? (I don't have one and can't test it but want to stay as compatible as possible). – Andy Apr 09 '19 at 14:00
1

@Andy I believe that you would need to change `"explorer ""https://www.example.com/function/edit/2019-04-09"""` to `"open ""https://www.example.com/function/edit/2019-04-09"""` for Mac – Chronocidal Apr 09 '19 at 14:14
1

Thanks a lot! This worked perfectly and is super fast (I didn't test the mac version, but according what I found on the net it should too) exactly as I've aimed. Sometimes the solution can be so easy WHEN you know it finally... – Andy Apr 09 '19 at 19:08
@Chronocidal How would you use the `Shell` *version* of this to ensure that when you click on a link in a cell, it opens using that, just as when you use `FollowHyperlink`? I have posted a question of a related issue [here](https://stackoverflow.com/q/73122968/1147688). – not2qubit Jul 30 '22 at 18:22

score 1 · Answer 2 · answered Apr 09 '19 at 08:22

If you are allowed to install selenium basic I would use that

Option Explicit

'download selenium https://github.com/florentbr/SeleniumBasic/releases/tag/v2.0.9.0
'Ensure latest applicable driver e.g. ChromeDriver.exe in Selenium folder
'VBE > Tools > References > Add reference to selenium type library
Public Sub DownloadFile()
    Dim d As WebDriver
    Set d = New ChromeDriver
    Const URL = "url"

    With d
        .Start "Chrome"
        .get URL
        'login steps
        .get 'otherUrl'
        Stop '<delete me later
        .Quit
    End With
End Sub

Thanks a lot for this creative answer - funnily I just looked into the Selenium VBA approach. But for my specific case it would be an overkill, as I really just need to open a specific URL and don't want to create dependencies if it's not critically needed. — Andy, Apr 09 '19 at 13:58

Use VBA to open URL in Default-Browser an catch existing Session

2 Answers2