Excel VBA Automation of Internet Explorer

[EDGE]

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.


<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="http://thewebsite.com/">
</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:


<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$http://thewebsite.com/dosomething.aspx">
<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,

[Kenneth Hobs]

IF submit does not work, you may need to use Click. See this thread for an example. http://vbaexpress.com/forum/showthread.php?t=25696

[EDGE]

Kenneth,

I did play around with the click event yesterday but couldn't get that to work either. I will go check out the other thread to see if there is something that I missed. I appreciate the help!

[EDGE]

The other thread really didn't get me any closer. Anyone have another solution to try?

[JP2112]

This might help:

http://www.codeforexcelandoutlook.com/blog/2010/06/an-exploration-of-ie-browser-methods-part-ii/

Basically, you set an object reference to all the page elements that are "input" type:

Set inputCollection = ie.document.getElementsByTagName("input")

Then you can loop through the collection and look for the attribute(s) that uniquely identify the submit button. For example, "src", "title" or "name".


For Each inputElement In inputCollection
If inputElement.getAttribute("name") = "Submit" Then
inputElement.Click
Exit For
End If
Next inputElement

HTH

[EDGE]

JP,

Thanks for the info. I found something like this this morning and have been trying to get it to work but for some reason the .click is not working.

What I did is cycle through to try and find the onClick="return CXPcheckLoginField(this.form)" however it could never locate it. I reviewed the properties in the watch window while looping through the elements and when I hit the img the onclick stated there was No Variable.

I also tried going of the TabIndex as the tab order for this img is 3. Once I hit this tab order I initiated the .click on the element but yet again nothing happened.

Maybe if I tried to set the focus to the img and then send the enter command via SendKeys that would do it. I am not sure how to set the focus of this based on the TabIndex or if it is possible.

[JP2112]

Post your code!

[EDGE]

I have cleaned it up a bit with some code I found this morning. I have tried the following plus about 100 different for loops that try to get the click event to work.

Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Login_2_Website()

Dim oHTML_Element As IHTMLElement
Dim sURL As String


sURL = "http://thewebsite.com/somestuff.aspx"
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
'oBrowser.timeout = 60
oBrowser.navigate sURL
oBrowser.Visible = True

Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE

Set HTMLDoc = oBrowser.Document

HTMLDoc.all.Email.Value = "someone@somewhere.com"
HTMLDoc.all.password.Value = "*****"

For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.Type = "img" Then oHTML_Element.Click: Exit For
Next

End Sub
Tried this:

For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.onclick = "return CXPcheckLoginFields(this.form);" Then oHTML_Element.Click: Exit For
Next
This also

For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.TabIndex = 3 Then oHTML_Element.Click: Exit For
Next
I am not sure why but the click event just seems to be jacked up on this one.

[Shred Dude]

Have you attempted to submit the procedure that the onclick attribute refers to?

I've sometimes found that you're able to directly initiate such procedures, thus eliminating the need to "click" an image.

You might try things like:

oBrowser.navigate "return CXPcheckLoginFields(this.form);"
'Or variations therof...
oBrowser.navigate "CXPcheckLoginFields(this.form)"
'etc.


another suggestion...

Have you confirmed whether this item is in fact contained within your reference to HTMLDoc? One thing to verify is the existence of Frames. If there are Frames on the page, they could be shielding your ability to get at the item with the HTMLDoc.getElementsByTagName("input") syntax you've employed below.

Verify in the Immediate window with ?HTMLDoc.frames.length

If there are frames, you might need to set you collection with something like:

HTMLDoc.frames(1).getelementsbytagname("input")

You said you tried to "submit" the form but that doesn't work on the new web page design?

[EDGE]

Shred,

Thanks for the info. I will give them a shot. I don't believe there are frames involved but will double check.

Correct the submit dosn't work in the new system as the form doesn't contain the Input Type = Submit.

I will let you know if the other items work out. I am wondering if on the new system they have some setting/security turned on that is blocking me from doing this? Any Ideas on that?

[EDGE]

Shred,

I tried different variations and they all didn't work. The CXPcheckLoginFields is a java script at the beginning of the html code to validate login data is not null. I am sure it has to do with either the java script or a cookie script that they have in the main html that is causing this to bomb. After you login you get redirected to the actual site so something has to be keeping it from executing correctly.

I also verified that there are no frames. I am all out of ideas on how to tackle this beast.

[mctabish]

Hey Edge,
Couple of things....
First, is the form name still "Login", or on the new form, is the form name different?
Normally, either formname.submit,formname.post, Or clicking the button works.
However, in this case, the button's "onclick" calls a script. There maybe something in there. Look at the script code, you may also try calling it direct (I have had to do this once or twice, I dont remember how I did it, but it was simple... I will see if I can find my code)

Another method you may want to try is using findwindow API, and click the button (this will only work if it is similar to a "popup" window style login


'Add the following line to your declaration section:
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

And add the following to where you want to "click" the button

cmbIndex = FindWindow("#32770", "Microsoft Internet Explorer") ' the "microsoft i e" title could be "Windows Interent Explorer" or similiar
If Not cmbIndex = 0 Then
SendMessage FindWindowEx(cmbIndex, 0, "Button", "Submit"), &HF5, 0, 0 ' button click
End If

Hope one of these suggestions lead you to a viable and clean resolution!
Bruce