How to open an URL in a browser using VBA?

Question

Trying to open a PDF(-website) with a referer which can only be opened with a link-click on the parents page.

By Using

 CreateObject(WinHttp.WinHttpRequest.5.1) 
.setRequestHeader "referer", "https://...“

the access works, but I need to open the page in a browser to view the pdf.

Found this:

https://learn.microsoft.com/en-us/previous-versions/windows/internet-explorer/ie-developer/platform-apis/aa752094(v=vs.85)

Syntax: object.Navigate2(URL, Flags, TargetFrameName, PostData, Headers)

(PostData [in, optional] Headers [in, optional])

and tried

 Dim IE As InternetExplorer  
 Set IE = New InternetExplorer 
    
   With IE
    .Navigate2
    https://main...,
    "https://referer..."

No results! Does anyone have a solution? (Just VBA please! Thx)

You have a choice of cans of worms. You can open the can of worms where you use internet explorer and have to interact with the save dialogue on file download, or the selenium basic tin where you can use chrome but have the additional set-up. In both cases, if you are automating a browser, I think you need to perform the steps before to generate the results table then click on the pdf links. My preference would be for selenium basic to avoid the faff of save dialogue. You don't set a referer header in these cases. They are generated by your actions on the page. — QHarr, Apr 17 '21 at 10:19
~~~~~o. <<= you mean this kind of worms? :-))) Thanks for the comment but I still have hopes of finding a VBA-solution that does not involve any other software or modules! But...believe it or not, I'm just dealing with plan B in case of a no-VBA-emergency: Python (!!!!!), but I can't get any further with **open(url)&referer** -combination either! — Jasco, Apr 17 '21 at 10:33
@QHarr Hello Mr. Qharr, it's been a long time since we wrote, now i have another huge problem that i need your help with, maybe you'd be so kind and save me from the misery again? Cause now IE is shutting down and I have to try Selenium&Chrome. You wrote: "... or the selenium basic tin where you can use chrome but have the additional set-up...."! What exactly do I have to do with Chrome. If you find time, please take a look here: https://stackoverflow.com/questions/75357656/excel-vba-selenium-to-post-a-referrer-as-request-header-with-edge-chrome-or-fire — Jasco, Feb 12 '23 at 13:41

score 5 · Accepted Answer · answered Apr 17 '21 at 10:41

5

I believe you provided the wrong Referer URL (and wrong format too, you need to also include Referer: in addition to the Referer URL) to the Headers parameter, try this:

Private Sub Test()
    Dim oIE As InternetExplorer
    Set oIE = New InternetExplorer
        
    With oIE
        .Visible = True
        .navigate "https://www.zvg-portal.de/index.php?button=showAnhang&land_abk=ni&file_id=16396&zvg_id=6467", _
                    headers:="Referer: https://www.zvg-portal.de/index.php?button=showZvg&zvg_id=6467&land_abk=sh"
    End With
    oIE.Quit
    Set oIE = Nothing
End Sub

answered Apr 17 '21 at 10:41

Raymond Wu

3,357
2
7
20

AAAAAAAAAAAAhhhhhhhhh.....my goodness it works!!! The circle of my favorite friends is getting bigger and bigger. Great people here...Mr. QHARR & Mr. WU: A million thanks gentlemen! – Jasco Apr 17 '21 at 10:55
1

Good to know it works! For reference, I got the idea from tek-tips.com/viewthread.cfm?qid=1263806 – Raymond Wu Apr 17 '21 at 10:58
1

It doesn't matter where you got it, you REALLY helped me and I am thankful for that. Have a nice weekend :-) – Jasco Apr 17 '21 at 11:01
1

I keep forgetting to click the accept-button and just vote! Did it, thanks for the reminder! – Jasco Apr 17 '21 at 11:37

score 1 · Answer 2 · answered Apr 17 '21 at 11:06

Upvoted Raymond's as very handy to know.

If you want to do the long way you would interact with the dropdowns as follows. Once results are generated, click the links to the pdfs. Note that the first dropdown has an onchange event which takes the value in the select as argument.

Option Explicit

Public Sub ClickDownloads()
    Dim ie As SHDocVw.InternetExplorer, html As MSHTML.HTMLDocument

    Set ie = New SHDocVw.InternetExplorer: Set html = New MSHTML.HTMLDocument

    With ie
        .Visible = True
        .Navigate2 "https://www.zvg-portal.de/index.php?button=Termine suchen"
 
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
    
        Dim evt As Object
        
        Set evt = .document.createEvent("HTMLEvents")
        evt.initEvent "onchange", True, False
        
        .document.querySelector("[value='ni']").Selected = True
        .document.parentWindow.execScript "updateAmtsgericht('ni');"
        .document.querySelector("[value='P2411']").Selected = True
        .document.querySelector("[type=submit]").Click
        
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
         
        Dim linkNodes As Object, i As Long
         
        Set linkNodes = .document.querySelectorAll("td:last-child a")
        
        For i = 0 To linkNodes.Length - 1
         
            linkNodes.Item(i).Click
            'Do something. Interact with save as dialogue to save. May also want to loop windows to close new tabls that were opened.
        Next
         
        Stop
 
        .Quit
    End With

End Sub

How to open an URL in a browser using VBA?

2 Answers2