Excel VBA Automation of Internet Explorer

[EDGE99]

Hey guys I have an Excel workbook that connects to a website and processes data based on the user that is using the workbook. I just found out that they are moving the website app to another company and the login is just a tad bit different. I was just notified today that they are going to move this on Friday so I am scrambling to find a solution that will work.

The following code works on the existing system however when I run this on the new system it doesn't execute the submit correctly. I get the ID and password passed into the correct inputs on the webpage but the submit part I cant get to work.

Current Code:

 Set objie = CreateObject("InternetExplorer.Application") 
With objie 
    .navigate strLink 
    Do While .Busy: DoEvents: Loop 
        Do While .readyState <> 4: DoEvents: Loop 
            .Visible = True 
        End With 
         
         'Check for login screen
        If objie.document.forms(0).name = "Login" Then 
             'Send login info
            objie.document.forms("Login").User.Value = loginName 
            objie.document.forms("Login").password.Value = passwd 
            objie.document.forms("Login").submit 
             
             'Needs a small wait in order for the sendkeys to work
            newHour = Hour(Now()) 
            newMinute = Minute(Now()) 
            newSecond = Second(Now()) + 2 
            waitTime = TimeSerial(newHour, newMinute, newSecond) 
            Application.Wait waitTime 
             ' Send Ok
            SendKeys "{Enter}", True 
        End If

Current Login HTML: The objie.document.forms("Login").submit above works with the <INPUT TYPE="submit" VALUE="Log In" NAME="Login">. This all works great on the current system. However there is no input TYPE="submit" on the new login page it is a reference to an image. See below for the new form information.


HTML Code:

<FORM NAME=Login ACTION="/SAMLogin" METHOD="post">
<CENTER>
<SPAN CLASS="redtext"></SPAN> <SPAN CLASS="blacktext"></SPAN>
<P>
<TABLE CELLSPACING="0" CELLPADDING="5" BORDER="0">
<TR>
<TD ALIGN="right" CLASS="blacktext">
<SPAN CLASS="bluetext">*&nbsp;</SPAN>User ID:
</TD>
<TD ALIGN="left" CLASS="blacktext">
<input type="text" name="user" size="40" />
</TD>
</TR>
<TR>
<TD ALIGN="right" CLASS="blacktext">
<SPAN CLASS="bluetext">*&nbsp;</SPAN>Password:
</TD>
<TD ALIGN="left" CLASS="blacktext">
<INPUT TYPE="password" AUTOCOMPLETE="off" SIZE="10" NAME="password" onfocus="formInUse = true;">
<INPUT TYPE="hidden" NAME="request_uri" VALUE="[URL unfurl="true"]http://thewebsite.com/">[/URL]
</TD>
</TR>
<TR ALIGN="middle">
<TD COLSPAN="2" CLASS="blacktext">
<INPUT TYPE="submit" VALUE="Log In" NAME="Login">
</TD>
</TR>
</TABLE>
</CENTER>
</FORM>

New Form:


HTML Code:

<form action="/ssoHtmls/login.fcc" method = "POST">
<div id='login-message'>
<input type="hidden" value = "true" id="hidden-auth-reason"/>
</div>
 
<label for="email">Email Address <b>*</b></label>
<p class="input-wrapper">
<input class="txt" type="text" id="email" name="email" maxlength="100" TABINDEX="1"/>
</p>
<input type="hidden" id="USER" name="USER" >
<label for="password">Password 
<a href="#" id="forgot-password-open-button">Forgot Password?</a>
</label>
<p class="input-wrapper">
<input class="txt" type="password" id="password" name="PASSWORD" maxlength="50" TABINDEX=2/>
</p>
<input class="img" type="image" src="img/btn_submit.jpg" border="0" height="30" width="88" alt="Submit &gt;&gt;" title="Submit &gt;&gt;" TABINDEX=3 name = "Submit" onClick="return CXPcheckLoginFields(this.form);" />
<input type="hidden" name="target" value="$SM$[URL unfurl="true"]http://thewebsite.com/dosomething.aspx">[/URL]
<input type="hidden" name="smauthreason" value="0"></form>

Does anyone know how I can trigger this input type? I have tried a number of things but it just failing to get the correct item. I would truly appreciate some help on this one guys.

Thanks,

 

[Gruuuu]

you can use IHTMLDocument.getElementsByTag("input") to return a collection, then loop through the collection of IHTMLElements until you find the element with a .onClick = "return CXPcheckLoginFields(this.form);"

So

Dim inputElements as Object
Dim inputElement as IHTMLElement
Set inputElements = objie.document.getElementsByTag("input")

For each inputElement in inputElements
  If inputElement.onClick = "return CXPcheckLoginFields(this.form);" Then
  inputElement.Click
Next inputElement

Or

Dim inputElements as Object
Dim inputElement as IHTMLElement
Set inputElements = objie.document.getElementsByTag("input")

For each inputElement in inputElements
  If Not inputElement.onClick Is Nothing Then
  inputElement.Click
Next inputElement

 

[EDGE99]

Gruuuu,

Thanks for the information. I have been playing around with something similar all morning to try and resolve this but it still doesn't process the element.

So I added the part to check the onclick value and for some reason it is not picking up the onclick value. So I put a msgbox within the if logic to validate if we are finding it and the msgbox never appears .

 

[Gruuuu]

Use your watch window to track the properties of your IHTMLElement.
As you loop through the collection, the properties of the IHTMLElement will change (because it is a new element)
Try to find the element you are interested in (the input img) and determine what property makes it different from the others.

Let me know what you come up with!

 

[EDGE99]

So when I cycle through the elements it appears that even though there is a onclick in the input string the properties are not showing anything. So I am assuming that is why the .click doesn't do anything

<input class="img" type="image" src="img/btn_submit.jpg" border="0" height="30" width="88" alt="Submit &gt;&gt;" title="Submit &gt;&gt;" TABINDEX=3 name = "Submit" onClick="return CXPcheckLoginFields(this.form);" />

Properties:
: outerHTML : "<INPUT class=img title="Submit >>" onclick="return CXPcheckLoginFields(this.form);" tabIndex=3 type=image height=30 alt="Submit >>" width=88 src="

https://thewebsite/img/btn_sub"

OnClick: - : onclick : : Variant/Object : Module3.Login_2_Website

The OnClick states No Variable. I have also tried to use the TabIndex to set focus to the image and send an enter via SendKeys but that also failed as I could not ever get the focus on the image.

 

[Gruuuu]

Hmm, ok this is getting annoying
I suppose you could try Instr(upper(inputElement.outerhtml), "ONCLICK")

 

[EDGE99]

Yes I agree. I am getting a bit frustrated at this. My mind is about fried at this point so please forgive this next question. How would I use this new statement?

 

[Gruuuu]

Dim inputElements as Object
Dim inputElement as IHTMLElement
Set inputElements = objie.document.getElementsByTag("input")

For each inputElement in inputElements
  If Instr(upper(inputElement.outerhtml), "ONCLICK") <> 0 Then
  inputElement.Click
Next inputElement

Just keep in mind, if you reuse this technique in other places, you gonna be clickin on everything that wants to be clicked on.

 

[EDGE99]

Another swing and a miss... I tried using upper but it failed I used the UCase in its place. Am I missing something here?

For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
    If InStr(UCase(oHTML_Element.outerHTML), "ONCLICK") <> 0 Then
    oHTML_Element.Click: Exit For
    End If
Next

 

[Gruuuu]

Sorry, UPPER() is a the workbook function. UCase, as you have witnessed, is the correct function.

Well, I was trying to help you write code that could be reused in other instances. If you just want to get this thing working for this one login page, and if the login page never changes, you may have some luck with:

HTMLDoc.getElementsByTagName("input").Items(3).Click

This just finds all html elements with the input tag, and clicks on the fourth one in the series. (as per the HTML provided above)

Possible failpoints:
1: getElementsByTagName("input") does not correctly pull your element and set it as an object in a collection
2: the .Click method won't work on this element

I truly do not see either of those as likely, however you will have to determine what's going on if this doesn't work.

I strongly suggest using that watch window, and stepping through the code (F8) line by line, and see what happens. Particularly during the assigning objects to the collection and .Click methods.

Good Luck!

 

[EDGE99]

Gruuu,

Thanks for all of the help. I truly appreciate it! If this doesn't work I am going to have to reach out to the IT folks and see if they have something blocking this ability.

 

[EDGE99]

Gruuu,

It didn't work out. Thanks again for the information I did learn a bit more about manipulating IE with so your time was not wasted!

Thanks again!

 

[Gruuuu]

I doubt there's a security function that would disable this sort of interaction.

Hmm. Perhaps it would be useful to investigate if you can run javascript code contained in the HTMLDocument.