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-09 14:23:06Z

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 9, 2019 at 14:23

answered Apr 9, 2019 at 8:55

Chronocidal

8,3551 gold badge15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andy Over a year ago

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).

Chronocidal Over a year ago

@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

Andy Over a year ago

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...

not2qubit Over a year ago

@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.

QHarr · Accepted Answer · 2019-04-09 08:22:24Z

1

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

answered Apr 9, 2019 at 8:22

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

1 Comment

Andy Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related