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

 

[John_w]

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]

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.

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]

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]

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]

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]

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

 

[razzandy]

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]

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

 

[razzandy]

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]

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