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

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:

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

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


Gonzo wrote:
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:

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

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/micro...ed994e c57a75

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" wrote in message ups.com...

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.

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 wrote:
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/micro...ed994e c57a75

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" wrote in message ups.com...

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.

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

--
Tim Williams
Palo Alto, CA


"matt" wrote in message oups.com...
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 wrote:
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/micro...ed994e c57a75

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" wrote in message ups.com...

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.

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 wrote:
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

--
Tim Williams
Palo Alto, CA


"matt" wrote in message oups.com...
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 wrote:
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/micro...ed994e c57a75

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" wrote in message ups.com...

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.