Log into Website Help ?

[razzandy]

The VBA code I am using is below and it runs without any errors BUT the button to log in doesn't actually get clicked.


The HTML Code on the website is here:

<form action="/Account/Login" id="loginForm" method="post" role="form"><input name="__RequestVerificationToken" type="hidden" value="BRqw7ES_53Qj1o9MZr-KpltdUyjNvKGaUQ4xjLdv8H7PVWPE82H6eyTanuOn-wXSi64TWDdaVGT3FDfO-dUNI0Gl3H41" /><div class="form-group">
<label class="control-label" for="Email">Email Address</label>
<input autocomplete="off" class="form-control" data-val="true" data-val-email="Please enter a valid email address." data-val-required="The Email Address field is required." id="Email" name="Email" type="text" value="" />
<span class="field-validation-valid text-primary" data-valmsg-for="Email" data-valmsg-replace="true"></span>
</div>
<div class="form-group">
<label class="control-label" for="Password">Password</label>
<input class="form-control" data-val="true" data-val-required="The Password field is required." id="Password" name="Password" placeholder="********" type="password" />
<span class="field-validation-valid text-primary" data-valmsg-for="Password" data-valmsg-replace="true"></span>
</div>
<button type="submit" class="btn btn-primary btn-block"><i class="fa fa-key"></i> Log in</button>
</form>

VBA Code:

Sub LogInToRoyalMail()

cURL = "https://Website here"
Const cUsername = "Username"
Const cPassword = "Password"

Dim IE As InternetExplorer
Dim doc As HTMLDocument
Dim LoginForm As HTMLFormElement
Dim UserNameInputBox As HTMLInputElement
Dim PasswordInputBox As HTMLInputElement
Dim SignInButton As HTMLInputButtonElement
Dim HTMLelement As IHTMLElement
Dim qt As QueryTable

Set IE = New InternetExplorer

IE.Visible = True
IE.navigate cURL

'Wait for initial page to load

Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

Set doc = IE.Document

Set LoginForm = doc.forms(0)

Set UserNameInputBox = LoginForm.elements("Email")
UserNameInputBox.Value = cUsername

Set PasswordInputBox = LoginForm.elements("Password")
PasswordInputBox.Value = cPassword

Set SignInButton = LoginForm.elements("__RequestVerificationToken")
SignInButton.Click

Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

End Sub

Hope somebody can see where I'm going wrong.

Thanks in advance

 

[razzandy]

Hi Everybody

Just to keep you updated I came across this YouTube video which has helped tremendously: https://www.youtube.com/watch?v=dShR33CdlY8

I now understand more, especially the difference between ID's, Tag's Class etc and how to use them, its made it far easier to get my head round!

I'm now going to see if there are any videos on Johns Suggestion: UIAutomationClient

Hi John

Couldn't really find any good videos so I found one of your past posts and tried integrating my code so the Save Dialogue would work. As soon as I run my code I get a compile error: Sub or function not defined. then it highlights: Download_File

Also there is some code which is red, is this normal (see below code)?

Where am I going wrong?

'References required:
'Microsoft Internet Controls (for InternetExplorer class)
'Microsoft HTML Object Library (for HTMLDocument and related classes)
'UIAutomation (for IUIAutomation and related classes)


Option Explicit

'https://msdn.microsoft.com/en-us/library/office/gg264421.aspx
'64-Bit Visual Basic for Applications Overview

#If  VBA7 Then
    'New VBA version 7 compiler, therefore >= Office 2010
    'PtrSafe means function works in 32-bit and 64-bit Office
    'LongPtr type alias resolves to Long (32 bits) in 32-bit Office, or LongLong (64 bits) in 64-bit Office
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As LongPtr) As LongPtr
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
#Else 
    'Old VBA version 6 or earlier compiler, therefore <= Office 2007
    Private Declare Sub Sleep Lib "kernel32" (ByVal milliseconds As Long)
    Private Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
    Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
#End  If


Public Sub IE_Download_File()

    Dim IE As New SHDocVw.InternetExplorer
    Dim HTMLdoc As MSHTML.HTMLDocument
    Dim htmlInput As MSHTML.IHTMLElement
    Dim htmlButtons As MSHTML.IHTMLElementCollection
    Dim htmlButton As MSHTML.IHTMLElement
    
    Dim htmlAs As MSHTML.IHTMLElementCollection
    Dim htmlA As MSHTML.IHTMLElement
    
    IE.Visible = True
    IE.navigate "xxxxxxxxxx"
    
      Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    
    Set HTMLdoc = IE.document
'Login email
    Set htmlInput = HTMLdoc.getElementById("Email")
    htmlInput.Value = "xxxxxxx"
'Login Pass
    Set htmlInput = HTMLdoc.getElementById("Password")
    htmlInput.Value = "xxxxxxx"
'Login Button
    Set htmlButtons = HTMLdoc.getElementsByTagName("button")
    htmlButtons(1).Click
    
    Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

'Navigate to Reports
    IE.navigate "xxxxxxxx"
    
    Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    Application.Wait (Now + TimeValue("0:00:06"))

'Download Button
    Set htmlButtons = HTMLdoc.getElementsByClassName("controls__btn btn--blue")
    htmlButtons(0).Click    
    
 
[highlight]Download_File[/highlight] IE.hwnd

End Sub


#If  VBA7 Then
Private Sub Download_File(IEhwnd As LongPtr)
#Else 
Private Sub Download_File(IEhwnd As Long)
#End  If

    #If  VBA7 Then
        Dim hwnd As LongPtr
    #Else 
        Dim hwnd As Long
    #End  If
    
    Dim UIAuto As IUIAutomation
    Dim UIAutoElem As IUIAutomationElement, UIAutoElemButton As IUIAutomationElement, UIAutoElemText As IUIAutomationElement
    Dim UIAutoElemToolbar As IUIAutomationElement
    Dim UIAutoCond As IUIAutomationCondition, UIAutoCond1 As IUIAutomationCondition, UIAutoCond2 As IUIAutomationCondition
    Dim UIAutoInvPatt As IUIAutomationInvokePattern
    Dim timeout As Date
    Dim notificationBarText As String, p1 As Long, p2 As Long
    Dim downloadedFileName As String
    
    Const DebugMode As Boolean = False
    
    'Create UIAutomation object
    
    Set UIAuto = New CUIAutomation
        
    'Find the IE11 Frame Notification bar, waiting a maximum of 10 seconds
    
    timeout = DateAdd("s", 10, Now)
    Do
        hwnd = FindWindowEx(IEhwnd, 0, "Frame Notification Bar", vbNullString)
        DoEvents
        Sleep 200
    Loop While hwnd = 0 And Now < timeout
    If DebugMode Then Debug.Print Format(Now, "hh:mm:ss"); " Frame Notification Bar " & hwnd
    
    If hwnd = 0 Then
        MsgBox "IE download Frame Notification bar does not exist"
        Exit Sub
    End If
    
    'Get UIAutomation element
    
    Set UIAutoElem = UIAuto.ElementFromHandle(ByVal hwnd)
    
    'Create conditions to find the "Save" button (split button type)
    'Name      = Save
    'Class     =
    'Ctrl type = 50031
    'Ctrl name = split button
    'Value     =
    
    Set UIAutoCond1 = UIAuto.CreatePropertyCondition(UIA_NamePropertyId, "Save")  'Language
    Set UIAutoCond2 = UIAuto.CreatePropertyCondition(UIA_ControlTypePropertyId, UIA_SplitButtonControlTypeId)
    Set UIAutoCond = UIAuto.CreateAndCondition(UIAutoCond1, UIAutoCond2)
    
    'Find the "Save" button, waiting until it exists
    
    Do
        Set UIAutoElemButton = UIAutoElem.FindFirst(TreeScope_Descendants, UIAutoCond)
        Sleep 200
        If DebugMode Then Debug.Print Format(Now, "hh:mm:ss"); " Find Save"
        DoEvents
    Loop While UIAutoElemButton Is Nothing
        
    'Click the "Save" button, waiting until it doesn't exist
    
    Do
        Set UIAutoInvPatt = UIAutoElemButton.GetCurrentPattern(UIA_InvokePatternId)
        On Error Resume Next  'ignore error if Save is clicked and it doesn't exist
        UIAutoElemButton.SetFocus
        UIAutoInvPatt.Invoke
        On Error GoTo 0
        If DebugMode Then Debug.Print Format(Now, "hh:mm:ss"); " Save clicked"
        Set UIAutoElemButton = UIAutoElem.FindFirst(TreeScope_Descendants, UIAutoCond)
        Sleep 200
    Loop Until UIAutoElemButton Is Nothing
    
    'Create conditions to find "Notification bar Text"
    'Name      = Notification bar Text
    'Class     =
    'Ctrl type = 50020
    'Ctrl name = Text
    'Value     = The xxxxxxx.yyy download has completed.
    
    Set UIAutoCond1 = UIAuto.CreatePropertyCondition(UIA_NamePropertyId, "Notification bar Text")  'Language?
    Set UIAutoCond2 = UIAuto.CreatePropertyCondition(UIA_ControlTypePropertyId, UIA_TextControlTypeId)
    Set UIAutoCond = UIAuto.CreateAndCondition(UIAutoCond1, UIAutoCond2)
    
    'Find the "Notification bar Text" and wait until its Value contains "download has completed"
    
    Do
        Set UIAutoElemText = UIAutoElem.FindFirst(TreeScope_Descendants, UIAutoCond)
        Sleep 200
        DoEvents
        notificationBarText = ""
        If Not UIAutoElemText Is Nothing Then
            notificationBarText = UIAutoElemText.GetCurrentPropertyValue(UIA_LegacyIAccessibleValuePropertyId)
        End If
        If DebugMode Then Debug.Print Format(Now, "hh:mm:ss"); " "; notificationBarText
    Loop Until InStr(notificationBarText, "download has completed")
    
    'Create conditions to find the "Close" button
    
    Set UIAutoCond1 = UIAuto.CreatePropertyCondition(UIA_NamePropertyId, "Close")  'Language
    Set UIAutoCond2 = UIAuto.CreatePropertyCondition(UIA_ControlTypePropertyId, UIA_ButtonControlTypeId)
    Set UIAutoCond = UIAuto.CreateAndCondition(UIAutoCond1, UIAutoCond2)
        
    'Find the "Close" button, waiting until it exists
    
    Do
        Set UIAutoElemButton = UIAutoElem.FindFirst(TreeScope_Descendants, UIAutoCond)
        Sleep 200
        If DebugMode Then Debug.Print Format(Now, "hh:mm:ss"); " Find Close"
        DoEvents
    Loop While UIAutoElemButton Is Nothing
    
    'Click the "Close" button
    
    UIAutoElemButton.SetFocus
    Set UIAutoInvPatt = UIAutoElemButton.GetCurrentPattern(UIA_InvokePatternId)
    UIAutoInvPatt.Invoke
    If DebugMode Then Debug.Print Format(Now, "hh:mm:ss"); " Close clicked"
        
    Set UIAutoInvPatt = Nothing
    Set UIAutoElemButton = Nothing
    Set UIAuto = Nothing
    
    'Extract file name from notification bar text, e.g. "The xxxxxxx.yyy download has completed."
    
    p1 = InStr(notificationBarText, "The ") + 4
    p2 = InStr(p1, notificationBarText, " download has completed")  'Language
    
    downloadedFileName = Mid(notificationBarText, p1, p2 - p1)
    Debug.Print Format(Now, "hh:mm:ss"); " Downloaded " & downloadedFileName

End Sub

 

[John_w]

Hi John

For some reason I couldn't get that code to work on the old site or on the new one I tried every tag I could think of including simple ones which it should find but it finds nothing. Is there an error in the code?

Dim reportingLink As HTMLAnchorElement, i As Long
    Set reportingLink = Nothing
    i = 0
    While i < doc.Links.Length And reportingLink Is Nothing
        If doc.Links(i).innerText = "Log out" Then Set reportingLink = doc.Links(i)
        i = i + 1
    Wend
    If Not reportingLink Is Nothing Then
        reportingLink.Click
        'IE wait loop here
    Else
        MsgBox "Reporting link not found"
    End If

The above code looks correct, based on the HTML you posted which showed "Log out" as the link text. Add this immediately after the While statement to output every link in the VBA Immediate Window (Ctrl+G):

Debug.Print "|" & doc.Links(i).innerText & "|"

Take note of any leading or trailing spaces (after or before the | characters) in the output.

Ensure the doc object (HTMLdocument) is referencing the correct page, by typing ?doc.body.InnerText in the VBA immediate window. Is the output what you would expect? Does the page use frames? Type ?doc.frames.length to find out - if 1 or greater is displayed then the page uses frames and the doc object must reference the HTMLdocument in the correct frame, otherwise the "Log out" link won't be found.

 

[John_w]

As soon as I run my code I get a compile error: Sub or function not defined. then it highlights: Download_File

Also there is some code which is red, is this normal (see below code)?

That's an odd error message, because Download_File is defined here:

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
Private Sub Download_File(IEhwnd As LongPtr)
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
Private Sub Download_File(IEhwnd As Long)
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If

The code in red is normal if you are using Excel 2007 or earlier.

 

[razzandy]

The code in red is normal if you are using Excel 2007 or earlier.

Hi John

I am running Office 2007 (32 bit), Windows 10 (64 bit).

Have I selected the correct Reference: There are a few but I selected: UIAutomationClient

 

[John_w]

Correct. You also need the other references noted at the top of the code, otherwise the code won't compile or run.

 

[razzandy]

Correct. You also need the other references noted at the top of the code, otherwise the code won't compile or run.

Sorry John I've not explained myself properly. Microsoft Internet Controls and Microsoft HTML Object Library are enabled but there are more than just UIAutomationClient. There are also UIAutomationClientPriv, UIAutomationCore, UIAutomationCorePriv and UIAutomationCoreRes so I'm not 100% sure I have enabled the correct UIAutomation.

Hope this makes more sense

 

[razzandy]

I've sussed it, changed:
Public Sub IE_Download_File()

to:
Sub IE_Download_File()

 

[John_w]

Sorry John I've not explained myself properly. Microsoft Internet Controls and Microsoft HTML Object Library are enabled but there are more than just UIAutomationClient. There are also UIAutomationClientPriv, UIAutomationCore, UIAutomationCorePriv and UIAutomationCoreRes so I'm not 100% sure I have enabled the correct UIAutomation.

Hope this makes more sense

Only UIAutomationClient is needed. My comment at the top of the code in post #21 is a bit misleading, sorry.

I've sussed it, changed:
Public Sub IE_Download_File()

to:
Sub IE_Download_File()

Those 2 lines mean the same thing, because 'Public' is the default procedure scope. I think changing any line in the code would 'reset' the compiler.

 

[razzandy]

Hi John

Tried running it by Task Scheduler on my home server but it doesn't complete. When I log into my server to check its run I can see from Task Manager that both Excel and IE are still running so its tried doing something but have no idea where its got stuck. Due to this I am thinking that I will have to manual set it running on a desktop PC. The only issue I can see is with the directory where the downloaded file goes to. Is there a way to click the 'Save As' option and pass the directory to UIAutomation? My father is hopeless at directing files to the correct location so this would have to be automatic.

Thanks in advance, all your help is really apprenticed

 

[razzandy]

Hi John

Thanks in advance, all your help is really apprenticed

Ooooops I meant to say Appreciated but I suppose you could call me your apprentice? Heeee Heeee