Excel web query to login into a website

Question

There are two different passwords on the page, thus I'm seeking for a macro or vba code to log me in. I've got some code that uses Internet Explorer to access websites, but it doesn't retrieve data because the web query browser is not logged in and I'm only logged into Internet Explorer, not the excel web query browser. As a result, I can't use my previously created web query connections to refresh or update data in Excel.

Sub WebLogin()
Dim a As String
Set IE = CreateObject("InternetExplorer.Application")
With IE
    .Visible = True
    .Navigate "https://newtrade.sharekhan.com/rmmweb/login/LoginPage.jsp"
    Do Until .ReadyState = 4
        DoEvents
    Loop
    .Document.all.Item("loginid").Value = "myuserid"
    .Document.all.Item("brpwd").Value = "password1"
    .Document.all.Item("trpwd").Value = "password2"
    .Document.forms(0).submit
End With
IE.Quit
End Sub

Is there a way to update this code so that it uses the Excel Web Query Browser instead of Internet Explorer and can auto-log in with only a simple refresh of the external web connection that is already attached to my Excel sheet?

Answer 1

To be recognized by the web server in subsequent requests, you must obtain the cookie after signing in. Always login first in order to get that variable from the browser.

Grab the necessary cookies once you log in using the code you posted (try simulating your attempt in Internet Explorer using Network Recorder in the developer tools of the browser). After exiting IE, you can send those cookies to the server in the HTTP Post request.