Sending Keys from Excel to Internet Explorer

[matt]

I have very limited programming knowledge and am a beginner.

I have code to open Internet Explorer from Excel. The website I am
accessing requires me to input a username and password. I want to know

if there is a way to do that from Excel.


I have an example of some code that opens an application, names the
application as MyScreen, and then runs the following:


If MyScreen.Search("Userid:",14, 5) = "Userid:" Then
MyScreen.SendKeys("tpx101<Enter>")
.....


I'm assuming that the "Search" looks for the ("Userid:") text, but I
have no idea what the ",14, 5) = "Userid:" is for.


My thinking is that I can do a similar thing with the Internet Explorer

Browser--name it, and then send keys to the username and password
boxes. I'm sure there is a better way, but I'm working with what I
have.


Once I've logged into the internet site I then have to click on two
separate links to get to a point where I can input information, click a

submit button, and then obtain results from a separate browser that
opens with the results output in the form of a hyperlink. I click on
the results output hyperlink and I have what I'm looking for.


Any ideas for using Excel code to run through these steps is much
appreciated.


Thanks,


Matt

 

[Gonzo]

Not quite following where the example code comes from etcetera...
It might be easier to use Excel 2003's built-in web query
functionality. Go to Data > Import external data > New web query.
Check Excel's help file or online tutorials to use web queries in Excel
and you might just find that it's what you need.

matt schreef:

 

[matt]

Below you'll find the code for which I'm basing my stream of thought.
I'm thinking that you can do something similar with Internet Explorer.
I have tried web querying (and I'm familar with how to do that), but
webquering doesn't do well with websites that require you to login. It
will follow you up to the point that you login and then it won't go any
further. So, if you have any other ideas then I'm open for
suggestions. Thanks.

Option Explicit
Global MySystem As Object
Dim MyEDPThing As Object
Dim MySessions As Object
Dim MySession As Object
Dim MFlag As Integer
Global MyScreen As Object
Global MyUserID As String
Global MyPassword As String

Sub LOGONEXTRA()

Screen.MousePointer = 11

'OPEN EXTRA!:
Set MySystem = CreateObject("EXTRA.System")
Set MyEDPThing = MySystem.Sessions.Open("c:\Program
Files\E!pc\Sessions\Session2.EDP")

'THE MAINFRAME IS OPEN:
MFlag = 1

'VERIFY SYSTEM OBJECT:
If (MySystem Is Nothing) Then
MsgBox "Could not create the EXTRA System object. Stopping
Connection to Extra!."
End
End If

'CREATE A SESSIONS OBJECT (a collection of your sessions):
Set MySessions = MySystem.Sessions
If (MySessions Is Nothing) Then
MsgBox "Could not create the Sessions collection object.
Stopping Connection to Extra!"
End
End If

'SET YOUR SESSIONS OBJECT:
Set MySession = MySystem.ActiveSession
If (MySession Is Nothing) Then
MsgBox "Could not create the Session object. Stopping
Connection to Extra!"
End
End If

'MINIMIZE EXTRA!:
' MySession.WindowState = 0

'CHECKS TO SEE THAT THE SESSION IS VISIBLE:
If Not MySession.Visible Then MySession.Visible = True

'CREATE THE SCREEN OBJECT AND MAKE SURE IT IS A VALID OBJECT:
Set MyScreen = MySession.Screen
If (MyScreen Is Nothing) Then
MsgBox "Could not create the Screen object. Stopping
Connection to Extra!"
End
End If

'WAIT FOR SCREEN:
MyScreen.WaitHostQuiet (3000)

'MOVE TO TPX SCREEN:
If MyScreen.Search("HOST", 2, 1) = "HOST" Then
MyScreen.SendKeys ("tpx101<Enter>")
MyScreen.WaitHostQuiet (3000)

'Send user id and password to tpx screen:
If MyScreen.Search("Userid:", 14, 5) = "Userid:" Then
'MyScreen.SendKeys (Left(CurUser, 7))
MyScreen.SendKeys (MyUserID)
MyScreen.SendKeys ("<TAB>")
'Get Password:
'MyPassword = InputBox("Enter Your Mainframe
Password For " & Left("ACUS345", 7) & ":", "DLP - Update IMS")
MyScreen.SendKeys (MyPassword)
MyScreen.SendKeys ("<Enter>")
MyScreen.WaitHostQuiet (3000)
End If
End If

Screen.MousePointer = 0

End Sub

 

[Tim Williams]

You will not have good results using sendKeys to do this.

Plenty of past posts in this group if you search "automate IE".
Eg:
http://groups.google.com/group/micr...57a75?lnk=st&q=&rnum=2&hl=en#aa20ed994ec57a75

Since IE has a COM interface you have direct access to the page document and all of the items in the page: this will be a much
better approach.

Tim

 

[matt]

Tim,

Thanks for sending the link. I was able to find some code that will
open Internet Explorer and enter my username and password in the
desired boxes. My hang-up now is trying to figure out how to "click"
the login button. This is the code I'm currently using:

Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")

ie.Visible = True
ie.navigate "https://wrds.wharton.upenn.edu/wrdsauth/members.cgi"

Do While ie.Busy And Not ie.ReadyState = 4:
Application.Wait (Now + TimeValue("0:00:02"))
DoEvents
Loop

ie.document.all("USER_NAME").Value = "bm415"
ie.document.all("USER_PASSWORD").Value = "....."

I get to this point and I don't know how to make Excel let Internet
Explorer know to log me into the site. You can open the URL and view
the source code if you so desire. A lot of this (HTML and VBA
interaction with IE) is new to me, but I was able to find two lines of
HTML that I figured would be helpful in trying to find a way to
"submit" the web-page.

The HTML that I think is relevant to my case is as follows:

<FORM action="/cgi-bin/login.cgi" method=post>,
<TD><INPUT type=submit value=login></TD>.

As you my already know, the second HTML code is for the button that
says "login" and the first set of code adds onto the existing URL to
route me to the following URL:
https://wrds.wharton.upenn.edu/cgi-bin/login.cgi.

From there the HTML looks like this

<form name="acc_form" action="/cgi-bin/termofUse_existing.cgi"
method="post">
<table align="right"><tr>
<td>
<input type='hidden' name='URI'
value='http://wrds.wharton.upenn.edu/home/index.shtml'>
<input type='hidden' name='uname'
value='bm415'>
<input type='hidden' name='school' value='byu'>
<input type="submit" value="I Agree">
<input type="button" value="I Disagree"
onclick="window.location='/wrdsauth/members.cgi';"></td></tr>
</table>
</form>

and I have to click the "I Agree" button. After doing so I am in the
website where I can then click on two or three subsequent hyperlinks to
get to a point where I can enter some input, hit submit, and get my
data. I know that I'll probably have trouble with clicking the
hyperlinks once I get into the system, but I'll worry about that when I
get to that point. Right now I'm just trying to get past the "login"
button on the initial page.

If you have any ideas, or know where I can look/search for additional
help with this, let me know. I barely signed onto Google Groups two
days ago, so I'm still getting familiar with Google Groups. Thanks for
the help.

Matt

 

[Tim Williams]

Try

ie.document.forms(0).submit

don't forget you'll need to use the "wait" loop after doing that.

To click on the "I agree" input on the next page you can do the same thing.

Tim

 

[matt]

Tim,

Thank you for the help. It works great! Now I'm going to search
around for how to make Excel click on a hyperlink to go to the next
page.

Matt

 

[Tim Williams]

Eg:

For Each l In ie.document.links
Debug.Print l.innerText
If l.innerText = "Click Here" Then
l.Click
Exit For
End If
Next l

 

[matt]

Thanks a ton! My question should probably be where did you learn how
to do all of this? I'm noticing that a lot of this is learning the VBA
code that interacts with the HTML tags. For example, knowing that in
order for Excel to execute the form you code
".document.forms(0).submit" or to click a link you code
".document.links.click", and so on. If you know of any places I can go
to learn the actual language it would probably save me from having to
ask you so many questions. I say this is because I'm to the point now
where I have to click radio buttons and select an option from a
dropdown menu before I can submit the form. I have included the HTML
for the radio button and dropdown menu below. I'm going to search the
internet some and just use trial and error to see if I can't get some
kind of ".document.nobr" or "document.option" to work for selecting my
desired options. The help is greatly appreciated. Thank you for your
time and most of all your patience.

Matt

select a radio button
<tr>
<td>
Frequency
<nobr><INPUT TYPE="radio" NAME="freq" VALUE="ann"
CHECKED>Annually</nobr>
<nobr><INPUT TYPE="radio" NAME="freq" VALUE="qtr">Quarterly</nobr>
</td>
</tr>

select options from a dropdown menu

<FORM action="/cgi-bin/wrds.cgi" name="regform" method="post"
ENCTYPE="multipart/form-data">

<INPUT TYPE="hidden" NAME="library" VALUE="comp">
<INPUT TYPE="hidden" NAME="qvards"VALUE="namesann">
<INPUT TYPE="hidden" NAME="method" VALUE="1">
<INPUT TYPE="hidden" NAME="format" VALUE="htm">
<INPUT TYPE="hidden" NAME="var" VALUE="gvkey">

<table border="0" cellpadding="3" cellspacing="4" width="95%">
<tr><td colspan="2">
<b>Step One: Statement</b>
</td></tr>
<tr>
<td width="60%">
<table border="1" cellpadding="6" cellspacing="0" width="100%">
<tr>
<td>
Statement
<select name="file" onChange="UpdateFreq(this.form)">
<option selected value="finstate.balance">Balance Sheet (long
format)</option>
<option value="finstate.balance2">Balance Sheet (tabular
format)</option>
<option value="finstate.bisect">BISECT</option>
<option value="finstate.complete">Complete Financial
Statements</option>
<option value="finstate.fsbalan">Financial Services Balance
Sheet</option>
<option value="finstate.fsincome">Financial Services Income
Statement</option>
<option value="finstate.fsratio">Financial Services Ratio
Report</option>
<option value="finstate.income">Income Statement (long
format)</option>
<option value="finstate.income2">Income Statement (tabular
format)</option>
<option value="finstate.ratios1">Financial Ratios (tabular
format)</option>
<option value="finstate.cashflow">Statement of Cash Flows (long
format)</option>
<option value="finstate.cashflo2">Statement of Cash Flows (tabular
format)</option>
</select>
</td>
</tr>
</table>

 

[Tim Williams]

Matt,

I'm at work, so will post later on your specific code, but in general my code is the result of having done web page scripting using
javascript: the object model is the same whatever language you're using and the syntax just changes a bit.

If you want to learn how to interact with web form elements then there are many javascript resources available which will help you
out.

 

[Tim Williams]

'*************************************************
Dim ie As Object, els As Object, e As Object, opt As Object

'set ie to IE application.....

Set els = ie.document.getElementsByName("freq")
For Each e In els
If e.Type = "radio" And e.Value = "qtr" Then
e.Checked = True
Debug.Print "Checked option: '" & e.Value & "'"
Exit For
End If
Next e

'assuming there's only one "file" form element (zero-based collection)
Set e = ie.document.getElementsByName("file")(0)
For Each opt In e.Options
If opt.Value = "finstate.ratios1" Then
opt.Selected = True
Debug.Print "Selected: '" & opt.Text & "'"
Exit For
End If
Next opt
'************************************************

Tim

 

[matt]

Tim,

Thanks again for the help. I'm in the middle of moving, so it's been
hard for me to get back to the internet to do some testing, but I'll be
up and running next week. I really do appreciate the help. I might
have some more questoins as I encounter new situations, so hopefully me
asking doesn't get you too irritated.

Matt

 

[Tim Williams]

No problem. If you want to continue "offline" then you can mail me at:

tim j williams at gmail dot com (no spaces and make the obvious substitutions)