# Log into Website Help ?



## razzandy (Sep 20, 2018)

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 (Sep 21, 2018)

I think it maybe something to do with the token part of the HTML, it seems to change with every visit to the website. So maybe once this part of my code runs:

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

I then need to receive the token code then perform another action with the token to submit it to allow the login procedure on the website?

Anybody got any ideas?


----------



## John_w (Sep 21, 2018)

Re your PM, try this:

```
doc.getElementById("Email").Value = "user@useremail.com"
    doc.getElementById("Password").Value = "pass1234"    
    doc.getElementsByTagName("BUTTON")(0).Click
    
    With IE
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
    End With
    
    MsgBox doc.body.innerText, Title:=IE.LocationURL
```
With the above credentials I get the expected error, "The username and password combination could not be verified...."


----------



## razzandy (Sep 21, 2018)

Hi John

Can I kiss you? 

Where was I going wrong? Why did I not see the Tag Name BUTTON?

I am now working on the next screen after login. This screen has a pop up which asks to confirm a printer profile. I am giving it my best shot.

Following this page I have to click on a Hyperlink which when viewing element shows a shortened link, again I will have a go before I ask for more help.

Thank you so much again, this is great, thank you


----------



## razzandy (Sep 21, 2018)

Hi Guys

Tried to navigate to the next page which is at: "https://proshipping.royalmail.com/Reports". To navigate to this I have to click a link called reporting. This link is part of an UL (Unordered List), a LI Tag. I simply tried: cURL = "https://proshipping.royalmail.com/Reports" but nothing happens.

HTML Code when inspecting the Reporting link is below:

```
<ul class="nav metismenu" id="side-menu">
    <li>
        <a href="/">
            <span class="nav-label">Dashboard</span>
        </a>
    </li>

        <li class="active">
            <a href="/Shipments/ShipmentProcessing">
                <span class="nav-label">Shipment Processing</span>
            </a>
        </li>

        <li class="  ">
            <a href="/Reports">
                <span class="nav-label">Reporting</span>
            </a>
        </li>

    <li class="pre-second-level ">
        <a href="/Maintenance">
            <span class="nav-label">Maintenance</span>
        </a>
        <ul class="nav nav-second-level              collapse" aria-expanded="false">
            <li>
                <a href="/Maintenance/AddressBook">
                    <i class="fa fa-angle-right"></i><span> Address Book</span>
                </a>
            </li>


            <li>
                <a href="/Maintenance/GoodsDescriptions">
                    <i class="fa fa-angle-right"></i><span> Goods Descriptions</span>
                </a>
            </li>


            <li>
                <a href="/Maintenance/PackageTypes">
                    <i class="fa fa-angle-right"></i><span> Package Types</span>
                </a>
            </li>


                <li>
                    <a href="/Maintenance/BulkMailingTemplates">
                        <i class="fa fa-angle-right"></i><span> Shipment Import Templates</span>
                    </a>
                </li>

                <li>
                    <a href="/Maintenance/SilentPrint">
                        <i class="fa fa-angle-right"></i><span> Silent Printing </span>
                    </a>
                </li>
            
        </ul>
    </li>
</ul>
```

Hope you can help

Thanks Again

Ry


----------



## razzandy (Sep 21, 2018)

No Worries, after searching more I realised I needed to do this:

    cURL = "https://webaddress"
    IE.navigate cURL

Now I have one last Button to press, I will keep you posted 

Thanks Again John



razzandy said:


> Hi Guys
> 
> Tried to navigate to the next page which is at: "https://proshipping.royalmail.com/Reports". To navigate to this I have to click a link called reporting. This link is part of an UL (Unordered List), a LI Tag. I simply tried: cURL = "https://proshipping.royalmail.com/Reports" but nothing happens.
> 
> ...


----------



## John_w (Sep 21, 2018)

That was my first thought - you need to Navigate to the URL!

In case the URL changes, this looks for the 'Reporting' link and clicks it:

```
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 = "Reporting" 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
```


----------



## razzandy (Sep 22, 2018)

Hi John

I am now stuck on clicking the button to generate the report. I just don't know where I'm going wrong? I have tried: doc.getElementsByTagName("button")(0).Click but it will not work I've also tried other tags names but nothing. The only one that gets no error is: doc.getElementsByTagName("button")(0).Click but it does not click it. HTML below.

Once the above problem works then my real problem starts because the Save As Dialogue pops up so I'm not sure how to deal with that!

Thanks Again John




```
<form class="form-horizontal" action="/Reports/DailyShippingReport" method="post" novalidate="novalidate" data-submittedtimeout="5000"><input name="__RequestVerificationToken" type="hidden" value="bHynmrRr1aC04X2Doxby4PbvYxf7qgSL6Ro9Y3Vh1UNLCE-3ul3xqUE7fbv8gMXRoWUVJ2LlSTSesHt2eIfz8ODf1Vk3WjF-JD78QIQG7mLbLvMLvYkGYt1XP8DIv_mLL2MtHg2">            <div class="row">
                <div class="col-xl-6 col-lg-8 col-md-10 col-sm-12">
                    <div class="form-group">
                        <label class="col-sm-3 control-label" for="ShippingDate">Shipping Date</label>
                        <div class="col-sm-9">
                            <div class="input-group" style="max-width: 200px;">
                                <input name="ShippingDate" class="form-control hasDatepicker" id="ShippingDate" type="text" placeholder="select date..." value="22/09/2018" data-val-required="The Shipping Date field is required." data-val="true" data-val-date="The field Shipping Date must be a date.">
                                <span class="input-group-addon" style="cursor: pointer;" *******="$('#ShippingDate').focus();">
                                    <i class="fa fa-calendar"></i>
                                </span>
                            </div>
                            <span class="field-validation-valid" data-valmsg-replace="true" data-valmsg-for="ShippingDate"></span>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-3 control-label" for="GroupBy">Group By</label>
                        <div class="col-sm-9">
                            <select name="GroupBy" class="form-control focusOnMeFirst valid" id="GroupBy" aria-invalid="false" aria-required="true" aria-describedby="GroupBy-error" style="max-width: 300px;" data-val-required="The Group By field is required." data-val="true"><option value="0">Manifest Number</option>
<option value="1">Country</option>
<option value="2">Department</option>
<option selected="selected" value="3">Service</option>
<option value="4">Posting Location</option>
</select>
                            <span class="field-validation-valid" data-valmsg-replace="true" data-valmsg-for="GroupBy"></span>
                        </div>
                    </div>
                    <div class="form-group">
                        <div class="col-sm-offset-3 col-sm-9">
                            *<button class="btn btn-primary btn-lg m-r-lg" type="submit">Create Report</button>*
                            <a class="btn btn-default btn-lg" href="/Reports">Cancel</a>
                        </div>
                    </div>
                </div>
            </div>
</form>
```


----------



## razzandy (Sep 22, 2018)

I have sussed out the clicking of the Generate Button with:     doc.getElementsByClassName("btn btn-primary btn-lg m-r-lg").Item.Click

Now I really need help with the: 'Do you want to open or save this file' pop up. I've not got a clue with this! 

I would like it to just go ahead and save the file to a directory of my choice.

Cheers

Ry




razzandy said:


> Hi John
> 
> I am now stuck on clicking the button to generate the report. I just don't know where I'm going wrong? I have tried: doc.getElementsByTagName("button")(0).Click but it will not work I've also tried other tags names but nothing. The only one that gets no error is: doc.getElementsByTagName("button")(0).Click but it does not click it. HTML below.
> 
> ...


----------



## Kyle123 (Sep 22, 2018)

You can't easily or reliably code around that. If this only needs to work on your machine, you could try using windows api calls, however depending on how you have internet explorer configured, this may prove very difficult.


----------



## razzandy (Sep 20, 2018)

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


----------



## John_w (Sep 22, 2018)

razzandy said:


> Now I really need help with the: 'Do you want to open or save this file' pop up. I've not got a clue with this!
> 
> I would like it to just go ahead and save the file to a directory of my choice.


Downloading a file from a web site is quite difficult.  There are 4 methods I've used successfully:

1. IE automation and Windows API functions to click the download dialogue buttons and handle the various windows which appear - e.g. see http://www.mrexcel.com/forum/excel-...ile-download-dialog-box-without-sendkeys.html. This worked successfully with Windows XP, but I've never tried it with Windows 11.

2. WinHttp/XMLhttp requests - see http://www.mrexcel.com/forum/excel-...n-internet-explorer-web-site.html#post3404965 and https://www.mrexcel.com/forum/excel-questions/749860-msxmlhttp-post-paramaters.html

The VBA code must exactly emulate the requests and form data (including hidden fields), and maybe cookies, that a browser sends when you manually download the file, and handle the response(s) correctly.  Multiple GET or POST requests may be required.  For this technique to work you will probably need to do the whole website login sequence and request the Reporting page with WinHTTP/XMLhttp requests, instead of your current IE automation method. Use your browser's developer tools (press the F12 key), or Fiddler, to examine the requests in detail.  

3. IE automation with UIAutomationClient - see http://www.mrexcel.com/forum/excel-...ual-basic-applications-website-accessing.html

4. URLDownloadToFile - the easiest method, but requires a direct URL to the file to be downloaded.

The links above are just a few of the examples of code using the above techniques on this forum, and there are other examples on other forums.

Given your current code, I would see if there is a direct URL to the download file and if so try URLDownloadToFile.  If not, try calls to UIAutomationClient methods.


----------



## razzandy (Sep 22, 2018)

John_w said:


> Downloading a file from a web site is quite difficult.  There are 4 methods I've used successfully:
> 
> 1. IE automation and Windows API functions to click the download dialogue buttons and handle the various windows which appear - e.g. see http://www.mrexcel.com/forum/excel-...ile-download-dialog-box-without-sendkeys.html. This worked successfully with Windows XP, but I've never tried it with Windows 11.
> 
> ...



Thanks John, I was just playing around with UIAutomationClient based on what I found on other sites. I will look into everything you have posted, thanks again.

Is there not a way to simply set IE to accept any downloads? Excel VBA will be the only think using EI for this one purpose as it will sit on a server.

Thanks Again


----------



## John_w (Sep 22, 2018)

razzandy said:


> Is there not a way to simply set IE to accept any downloads? Excel VBA will be the only think using EI for this one purpose as it will sit on a server.


I've never been asked that before, but see if any of the answers at  https://superuser.com/questions/246553/how-to-disable-file-download-popup-in-internet-explorer work for you.


----------



## razzandy (Sep 24, 2018)

John_w said:


> I've never been asked that before, but see if any of the answers at  https://superuser.com/questions/246553/how-to-disable-file-download-popup-in-internet-explorer work for you.



Thanks John but it turns out the ability to switch off the Open Save Dialogue has been removed in a security update for IE11!

Just to give a quick update as to where I am: I now have my finished code working fine when manually running it so I created a schedule on my home server to auto run it, it runs and completes the code but for some strange reason it doesn't download the file!

I am wondering if I need to change IE somewhere so it can run when not logged in? I am only guessing though because when creating the task to run my workbook I have to select the check box in Task Scheduler to 'Run whether user is logged on or not'. I cant select that option for IE because I am not scheduling that, I am only scheduling my Excel Workbook if that makes sense.

Cheers


----------



## razzandy (Sep 24, 2018)

razzandy said:


> I am wondering if I need to change IE somewhere so it can run when not logged in? I am only guessing though because when creating the task to run my workbook I have to select the check box in Task Scheduler to 'Run whether user is logged on or not'. I cant select that option for IE because I am not scheduling that, I am only scheduling my Excel Workbook if that makes sense.
> 
> Cheers



Just done some more testing on my server by running it from the task scheduler and found by viewing the Task Manager that IE does actually open but for some strange reason it doesn't then close like it does when I manually run the Workbook. I am wondering if Sendkeys don't work because it all runs as invisible under Task Scheduler.


----------



## John_w (Sep 26, 2018)

SendKeys is unreliable.  It sometimes doesn't work even with a visible application.  With UIAutomationClient you shouldn't need SendKeys.


----------



## razzandy (Sep 26, 2018)

John_w said:


> SendKeys is unreliable.  It sometimes doesn't work even with a visible application.  With UIAutomationClient you shouldn't need SendKeys.



Thanks for the advice John.  You wouldn't believe it we are being shifted to another system and I have got everything working apart from clicking the Log Out Button.

The html Element is:


```
<a ng-if="::navItem.click" ng-click="vm.onHandleClick(navItem)" role="menuitem" onfocus="this.blur();" translate="SECONDARY_NAVIGATION.LOGOUT" automated-test-id="top-logout-link">Log out</a>
```

I've tried all-sorts, one being:     
	
	
	
	
	
	



```
doc.getElementById("top-logout-link")(0).Click
```


----------



## John_w (Sep 26, 2018)

Try the code in post 7, changing "Reporting" to "Log out", maybe changing reportingLink to a more appropriate name for completeness.


----------



## razzandy (Sep 26, 2018)

John_w said:


> Try the code in post 7, changing "Reporting" to "Log out", maybe changing reportingLink to a more appropriate name for completeness.



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


----------



## razzandy (Sep 27, 2018)

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


----------



## razzandy (Sep 20, 2018)

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 (Sep 27, 2018)

razzandy said:


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



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 (Sep 27, 2018)

razzandy said:


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


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 (Sep 27, 2018)

razzandy said:


> 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 (Sep 27, 2018)

John_w said:


> 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 (Sep 27, 2018)

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


----------



## razzandy (Sep 27, 2018)

John_w said:


> 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 (Sep 27, 2018)

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

to: 
Sub IE_Download_File()


----------



## John_w (Sep 27, 2018)

razzandy said:


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



razzandy said:


> 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 (Sep 28, 2018)

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 (Sep 28, 2018)

razzandy said:


> 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


----------



## razzandy (Sep 20, 2018)

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 (Sep 28, 2018)

Just wondering if all my code could run in Userform with the Webbrowser Object.  This way a separate instance of IE wouldn't need to be opened. It would all run within the Userform?

what do you think John?


----------



## John_w (Sep 29, 2018)

razzandy said:


> Is there a way to click the 'Save As' option and  pass the directory to UIAutomation?


I know that clicking 'Save'  works, but I've never managed to get 'Save As' to work using  UIAutomation.  'Save As' requires the down arrow next to 'Save' to be  clicked first. 



razzandy said:


> Just wondering if all my code could run in Userform with the Webbrowser Object.  This way a separate instance of IE wouldn't need to be opened. It would all run within the Userform?
> 
> what do you think John?


The WebBrowser object is basically the same as IE - it has the same properties and methods - so you would still have the same problem of automating the download.


----------



## razzandy (Oct 2, 2018)

Hi John

Is there a property in UIAutomation where you can say find Application that is invisible?  I am asking because it seems UIAutomation will not run if IE is not visible which is what happens when its run as a scheduled task.  The other way to experience this is by changing IE.Visaible to False and running the code. Debugging shows the UIAutomation never really gets going.


----------



## John_w (Oct 2, 2018)

My use of the UIAutomation class is very limited, so I don't know if it has something which can find an invisible application.  Since UIAutomation emulates manual clicking, etc., I would think it needs the application to be visible.

If I was approaching your task from scratch I would try the XMLhttp method of emulating the browser's requests.


----------



## razzandy (Oct 3, 2018)

John_w said:
			
		

> If I was approaching your task from scratch I would try the XMLhttp method of emulating the browser's requests.



Will XMLhttp work with the type of Download link I am using? The file doesn't reside within a fixed URL, its dynamically generated when the download button is clicked.

Cheers


----------



## John_w (Oct 3, 2018)

In theory, yes, as long as the XMLhttp requests emulate exactly what a browser does. I should warn you though that this technique can be time-consuming (a lot of trial and error) to develop and code.


----------



## razzandy (Oct 3, 2018)

John_w said:


> In theory, yes, as long as the XMLhttp requests emulate exactly what a browser does. I should warn you though that this technique can be time-consuming (a lot of trial and error) to develop and code.



Thanks John, I will let you know what I decide


----------

