CODEWITHSUNDEEP
excelvbaweb-scrapinghttp-redirect
Andy
Andy

Reputation: 129

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

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

Upvotes: 3

Views: 12924

Answers (2)

Chronocidal
Chronocidal

Reputation: 7951

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

Upvotes: 8

QHarr
QHarr

Reputation: 84465

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

Upvotes: 1

Related Questions