Solved: Using VBA to fill in Web Login Form

[Rollin_Again]

Hello All,

I have an Excel sheet that contains thousand of our customers records. Each record consists of various info such as company ID, user ID, password, etc. As part of my job I am often required to log into our company website using our customer's log in credentials to troubleshoot their orders. Currently I have to open our website login page and enter all the info manually. I am trying to automate the entire process by selecting a row of data from the Excel sheet and then using VBA to open and automatically fill in the web login form with the data contained in the current record. I am able to create and display the Internet Explorer object and populate three text fields on the web page using code but I am unable to select from 3 sets of radio buttons on the form. Each set of radio buttons represents a YES/NO answer to each of 3 statements at the bottom of the form. Can anyone help me figure out how to select the radio buttons that are required.

So far my VB code is as follows:

 Set oIE = CreateObject("InternetExplorer.Application")
oIE.Visible = True
oIE.Navigate ("www.websitename.com")
While oIE.Busy: Wend
oIE.Document.Forms("passlogon").All("UserID").Value = "jsmith"
oIE.Document.Forms("passlogon").All("NodeID").Value = "M1234567"
oIE.Document.Forms("passlogon").All("Password").Value = "myPass"

Below is a small portion of the web page source code from the website showing the form name and button names. I assume the radio button names are rbFCRA, rbClaimsUse, and rbHaveConsent but I can't figure out how to set their values. If you want to see what I am talking about just go to cplink2 dot com and look at the 3 statements at the bottom of the form.

aFm=document.passlogon
if(!IsSupport())
if(!IsRBEmpty(aFm.rbFCRA, "FCRA"+SQ+"3)") )
if ( GetValue( aFm.rbFCRA )!="YES" )
SetFcraError( aFm.rbFCRA[0], "FCRA acceptance is required." );
if(!IsSupport())
if(!IsRBEmpty(aFm.rbClaimsUse, "Usage"+SQ+"1)") )
if( GetValue(aFm.rbClaimsUse)=="YES" )
if(!IsRBEmpty(aFm.rbHaveConsent, "Consent"+SQ+"2)") )
	if ( GetValue(aFm.rbHaveConsent)!="YES")
	 SetFcraError( aFm.rbHaveConsent[0], "Please obtain written authorization from the subject consumer before attempting to place orders for claims purposes." )
if ( (aFm.NEWPASSWORD1.value.length+aFm.NEWPASSWORD2.value.length)> 0 ) {
IsBlankCheck(aFm.NEWPASSWORD1, NP );
IsBlankCheck(aFm.NEWPASSWORD2, NP+" Confirmation");

Thanks
Rollin

[alimcpill]

For those radio buttons you can select 'Yes' by doing this:

[vba]
oIE.Document.getElementsByName("rbClaimsUse").Item(0).Click
[/vba]

and 'No' by doing this
[vba]
oIE.Document.getElementsByName("rbClaimsUse").Item(1).Click
[/vba]

Just change the name of the radio button in the expression, you're correct in what you assume about the names!

These are quite hard-coded in that they assume the names won't change and the order of the options is always Yes then No.

Hope that helps!

[Zack Barresse]

Ooh! Alimcpill, ya got to it before I could finish it up. Nice one! I'll post my (assimilated) version anyway, heck with it..

[vba]Option Explicit

Sub TestIE_Rollin()

Dim oIE As InternetExplorer
Dim ieForm
Dim i As Long

SetRefs
Set oIE = New InternetExplorer
oIE.Visible = True
oIE.Navigate "https://www2.cplink2.com/bin/web_loginout.exe"
Do Until oIE.ReadyState = READYSTATE_COMPLETE: Loop

With oIE.Document
.forms("passlogon").All("UserID").Value = "jsmith"
.forms("passlogon").All("NodeID").Value = "M1234567"
.forms("passlogon").All("Password").Value = "myPass"
.GetElementsByName("rbFCRA").Item(0).Click
.GetElementsByName("rbClaimsUse").Item(0).Click
.GetElementsByName("rbHaveConsent").Item(0).Click
End With

Set oIE = Nothing
Set ieForm = Nothing

End Sub

Sub SetRefs()
On Error Resume Next
' Adds Internet Controls Ref (SHDocVw.dll)
ThisWorkbook.VBProject.References.AddFromGuid "{EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}", 1, 1
End Sub[/vba]

[Rollin_Again]

Thanks to both of you! I'm just starting to tinker with writing code to work with IE and would like to learn more if possible. Where is the best place to learn about using VBA with IE? Where can I read about the IE object model and is there any application that can easily tell you control names on a web page without having to look through the source code?

I also finally figure out how to click the login button name "STYPE" on the form.

oIE.document.forms("passlogon").all("STYPE").Click

Thanks again for any advise you can give on learning IE's object model.

Thanks,
Rollin