How to automate Web Login using VBA?

Web Login can be automated using VBA. Below code automates Upstox API login using VBA and Internet Explorer. Developers can modify the code as per their requirement.

Note: As Auto Login needs to store your credentials in VBA as plain text, you should take utmost care to protect the credentials.

References:
https://docs.microsoft.com/en-us/previous-versions/windows/internet-explorer/ie-developer/platform-apis/aa752084(v=vs.85)
https://stackoverflow.com/questions/16441179/vbscript-for-automated-website-login-testing
https://riptutorial.com/vba/example/27772/internet-explorer-object
https://www.automateexcel.com/vba/automate-internet-explorer-ie-using/

Demo Excel sheet:

  • Download 18.85 KB Video

Option Explicit
'Add the following references to your VBA Project
'Menu --> Tools --> References
'Microsoft Internet Controls
'DotNet Library for Upstox API 'If not referenced already

'API Details
Public Const ApiKey As String = "api-key"
Public Const RedirectUri As String = "https://redirecturl.in"

'Login Details
Public Const ClientId As String = "ucc-id"
Public Const Password As String = "password"
Public Const YearOfBirth As String = "year"

Public IE As InternetExplorer
Public Upstox As New Upstox 'Remove this line if Upstox is already declared in another module

'References
'https://docs.microsoft.com/en-us/previous-versions/windows/internet-explorer/ie-developer/platform-apis/aa752084(v=vs.85)
'https://stackoverflow.com/questions/16441179/vbscript-for-automated-website-login-testing
'https://riptutorial.com/vba/example/27772/internet-explorer-object
'https://www.automateexcel.com/vba/automate-internet-explorer-ie-using/

'Intermediate pages
'https://api.upstox.com/index/login
'https://api.upstox.com/index/dialog/authorize/
'https://howutrade.in/?code=asasasasc3314911613a169dd974576d717f

Public Sub AutoLoginUpstox()
Dim IEStatus As Boolean
IEStatus = False

On Error GoTo ErrHandler
'construct login url
Dim LoginUrl As String
LoginUrl = "https://api.upstox.com/index/dialog/authorize?apiKey=" & ApiKey & "&redirect_uri=" & RedirectUri & "&response_type=code"

'Create IE object
Set IE = New InternetExplorer
IEStatus = True
IE.Width = 456
IE.Height = 433
IE.Visible = True
IE.Navigate2 LoginUrl

Do While IE.Busy = True Or IE.ReadyState <> 4
     Application.Wait (Now + TimeValue("00:00:01"))
Loop

'Check the page
Dim CurrentUrl As String
CurrentUrl = IE.LocationURL

If InStr(CurrentUrl, "index/login") > 0 Then
     'We are in Login Page
     IE.Document.GetElementById("name").Value = ClientId
     IE.Document.GetElementById("password").Value = Password
     IE.Document.GetElementById("password2fa").Value = YearOfBirth
     IE.Document.Forms(0).Submit
End If

Do While IE.Busy = True Or IE.ReadyState <> 4
     Application.Wait (Now + TimeValue("00:00:01"))
Loop

CurrentUrl = IE.LocationURL
If InStr(CurrentUrl, "index/dialog/authorize") > 0 Then
     'We are in Authorization Page
     IE.Document.GetElementById("allow").Click
End If

Do While IE.Busy = True Or IE.ReadyState <> 4
     Application.Wait (Now + TimeValue("00:00:01"))
Loop

CurrentUrl = IE.LocationURL
If InStr(CurrentUrl, "code=") > 0 Then
     'We are in Redirect Page
     Dim Index As Integer
     Index = InStr(CurrentUrl, "code=")
     Dim SubString As String
     SubString = Right(CurrentUrl, Len(CurrentUrl) - Index - 4)
     Dim Code As String
     Code = Split(SubString, "&")(0)
End If

IE.Quit
Set IE = Nothing
IEStatus = False

'Pass the received access code to GetAccessToken Method
'Call Get MasterContract
Upstox.GetAccessToken_2 (Code)
Upstox.GetMasterContract
Exit Sub

ErrHandler:
If IEStatus Then
     Set IE = Nothing
End If
MsgBox Err.Description
End Sub

Was this article helpful?

Related Articles

10 Comments

  1. Avatar Gupta

    Hi
    Im getting the below error when i copy paste this VBA code to my excel.
    “User-defined type not defined” in the line “Public IE As InternetExplorer”
    Pls help

    1. Hi Gupta,
      You need to add reference to ‘Microsoft Internet Controls’ in VBA Project
      Menu –> Tools –> References –> ‘Microsoft Internet Controls’

      Add Reference

  2. Avatar Logan

    Hi Howutrade team,
    When i try to login i finding an error of “The remote server machine does not exist or is unavailalble”,

    1. Hi Logan,

      Looks like you Upstox server was down that time.
      Could you try during market hours and also make sure you have set your credentials in the VBA?

  3. Avatar Logan

    Hi Team,
    Today i faced an error after using auto login, my orders are getting Rejected for the reason “error user/user_target not logged in”, hence i have login manually into web pro or mobile App in-order to resolve this issue, could you please help me out with Problem.

    1. Hi Logan,
      Possible Cause:
      1. Logged-in before 0730AM
      2. You have directly taken to Authorization page instead of login page
      3. Upstox cleared the session details for any technical reasons.

      Fix:
      1. Make sure you log-in after 0730 AM
      2. Make sure you get Login page to enter your Login credentials else clear cookies

      Why?
      OMS (Upstox) considers a user login valid only when you pass correct client id, password and 2FA and all the details are validated with user database at OMS server once in a day i.e. after 0730 AM. This session is valid until you specifically call Logout in any of the Upstox platform.

      Upstox Login page uses cookies to enhance user experience like ‘no need to login again after successful login’. This cookies needs to be cleared EOD (by upstox), so that you get Login page in the next day, else you will not get login page and directly shown Authorization page. In such cases, only upstox login server knows you, but the OMS unaware. The OMS will aware of you only when you pass login credentials (upstox login page) to OMS for validation.

      1. Avatar Logan

        Hi Team,
        Thank you for your quick response,
        As mentioned the login time for my excel @ 9:00 am, i am sure it passing through login page then authorization page at first login on day start, it might be third case as you mentioned.
        I would really appreciate if you could help me in storing the login credentials instead strings in code to as we are storing api key and api secret key in settings window in password hidden format.

  4. What’s Taking place i’m new to this, I stumbled upon this I’ve found It absolutely
    useful and it has aided me out loads. I hope to give a contribution & help
    other customers like its helped me. Great job.

  5. I have noticed you don’t monetize codeadvisor.in,
    don’t waste your traffic, you can earn extra bucks every month with new monetization method.
    This is the best adsense alternative for any type of website (they approve all sites), for
    more info simply search in gooogle: murgrabia’s tools

Leave a Reply to Admin Cancel Reply