Web browser question

[Tim Coddington]

So I made a form to use for browing the WWW. In the form initialization, I
navigate to a target. In the form activation, I set the window size and
position. The next thing I need to do is send some data to the form, as
there is a log on screen which I must go through. I use 'Sendkeys' to send
'usrnam' and 'passwrd'. But if I place it in the initialization or
activation subroutines, it never reaches the browser window. I apparently
must complete the activation phase to use 'Sendkeys'? How do I get my name
and pw sent to the browsed object?

 

[Sharad]

When it asks for username and password, excel will hold the code
execution. So no use putting code for SendKeys in excel itself.
I made simillar automation program, of checking for new data on a web
site and updating the new data in execl. This program runs 1:00 AM in
the night. What I did is
at 1:00 AM I launch a Winword document (Task Scheduler).
This winword document in it's open even launches the excel workbook
through Shell command, so that the excel application ID is known.
Allowing some time for the username and password dialog box to appear,
winword sends username and password to the excel using AppActivate
first.

Code in Winword is somplething like this:
(In the winword document, there is a single table with single cell, to
store the ApplicationID).

Private Sub Document_Open()
Application.OnTime Now + TimeValue("00:00:02"), "OpMyWeb"
End Sub

Then the procedures are in modules as under:

Sub OpMyWeb()
Dim myIDnum As Long
myIDnum = Shell("C:\Program Files\Microsoft Office\OFFICE11
\EXCEL.EXE D:\IntraTrack\WebQuery.xls", vbNormalFocus)
ThisDocument.Tables(1).Cell(1, 1).Select
Selection.Text = myIDnum
Application.OnTime Now + TimeValue("00:00:10"), "EnQuer"
End Sub

So allowing 10 seconds, Procedure EnQuer runs, which is only for
clicking on 'Enable Auto Web Query'. (You may not need this, and you can
jump to SendPass.)

Sub EnQuer()
Dim myIDnum As Long
ThisDocument.Tables(1).Cell(1, 1).Select
myIDnum = Left(Selection.Text, Len(Selection.Text) - 2)
AppActivate myIDnum
SendKeys "{TAB}", True
SendKeys "{ENTER}", True
ThisDocument.Activate
Application.OnTime Now + TimeValue("00:00:30"), "SendPass"
End Sub

So allowing 30 seconds for username & pwd box to appear, I run SendPass
procedure, which sends the user name and passowrd, and then winword
document is closed.

Sub SendPass()
Dim myIDnum As Long
ThisDocument.Tables(1).Cell(1, 1).Select
myIDnum = Left(Selection.Text, Len(Selection.Text) - 2)
AppActivate myIDnum
SendKeys "username", True
SendKeys "{TAB}", True
SendKeys "password", True
SendKeys "{ENTER}", True
ThisDocument.Activate
ThisDocument.Saved = True
If Application.Windows.Count = 1 Then Application.Quit
ThisDocument.Close
End Sub

Sharad

 

[Shetty]

Hi all,

When it asks for username and password, excel will hold the code
execution.

It is not necessary to create a word doc to enter username and
password.(Atleast in some cases). I had similar problem.
Thru VBA, create a new instance of IE and navigate to the URL which is
asking username and password.
Next, What you need to do is to view the source of the wab page and
findout the codename of the username field and password field.(If you
have Forntpage installed, you can find it out almost instantly. In
frontpage, select normal tab, select the username field (where you will
be entering the username) and then click on the html tab. The html code
for the selected field will be highlighted. Note down the code name of
the username. Repeate the same procedure for the password.

Now, with the help of this data, you will be open the wab page without
manually entering the username and password.

I am not a expert but it works for me.
Hope it works for you also.

Regards,
Shetty.

 

[R.VENKATARAMAN]

I am also interested in this. I edited the concerned webpage thrugh
frontpage and I got these lines(hope they are the relevant portions)
------
<td align="right" nowrap class="bodywhite">Yahoo! ID:</td>
<td align="right"><input name="login" size="17" value=""></td>
</tr>
<tr>
<td align="right" nowrap class="bodywhite">Password:</td>
<td align="right"><input name="passwd" size="17" value=""
type="password"></td>

 

[Shetty]

YA, IT IS LOGIN AND PASSWD.
YOU CAN USE IT IN YOUR VBA CODE TO SUPPLY THE VALUES.
ALSO YOU NEED TO FIND OUOT THE CODE NAME FOR SUBMIT BUTTON TO SUBMIT
THE FROM PROGRAMATICALLY.

REGARDS,
SHETTY.

 

[Sharad]

Well Shetty, if username and passowrd entry is in the web page script
then these can be supplied through the VBA code (or even in the worst
case, SendKeys will also work since Excel will not hold the code
execution.)

But if the site uses windows login, a seperate dialogue box asking for
username and passowrd pops up. Here it is not possible to supply the
username and password through VBA code. Excel will hold the execeution
of code. That's when you need to use SendKeys from another application
(one of the ways, there could be better way which I would like to know.)

Since OP mentioned SendKeys I assumed that that web site uses Windows
login.

Sharad

 

[Shetty]

YES, YOU ARE RIGHT.
SINCE I WAS INVOLVED IN WEB PAGE SCRIPTS IN MY CURRENT PROJECT, I
REPLIED ACCORDINGLY. I WILL ALSO SAVE YOUR PREVIOUS POST FOR REFERANCE
IN FUTURE.

IS THERE ANYWAY TO FINDOUT WEATHER IE HAS GENERATED ANY MESSAGEBOX?
I CREATE A INSTANCE OF IE AND THEN NAVIGATE TO THE REQUIRED URL WITH
USERNAME AND PASSWORD. IF THERE IS ANY ERROR, THEN IE WILL GENERATE A
MESSAGE BOX OF THE ERROR AND WILL RETURN TO THE LOGIN PAGE.
IN SUCH CASE I WOULD LIKE TO ASK USER TO AGAIN INPUT THER USERNAME AND
PASSWORD, PROGRAMATICALLY CLICK ON OK BUTTON OF MESSAGEBOX AND AGAIN
INITIATE THE LOGIN PROCESS. USER WILL NOT BE ABLE TO SEE THE ERROR
MESSAGEBOS COZ I KEPT IE HIDDEN.

REGARDS,
SHETTY.

 

[Sharad]

There is no any direct way to find out if IE has generated a message box
(I am talking about Reference to MS Internet Control).

However, if you are launching IE with a URL first time, if you give
command to GoForward, it will normall generate error "Unspecified
error", if no any dialogue box is active in IE.
If a dialogue box is active in IE, then on GoForward, it gives another
error "resource is in use."

Using this, it is possible to determine if a IE has generated a dialogue
box.
Below is a sample code (Reference to MS Internet Control)
In the below code I allow 10 seconds before giving the GoForard command
and checking what error it has generated. If your site takes longer you
can adjust the number of seconds.

You can check the below code by purposefully giving wrong password.

Regards
Sharad
Sub Rediculous()
Dim IE As InternetExplorer, nCount As Long
Set IE = New InternetExplorer
IE.Navigate "Your website url"
'your code for username/pwd but give WRONG PWD to test
IE.Visible = True
'below I allow 10 seconds to load
'you can change the seconds as per your site speed
nCount = Timer
Do While Timer < nCount + 10
DoEvents
Loop

On Error Resume Next
IE.GoForward
If InStr(1, Err.Description, "resource is in use", vbTextCompare) >
0 Then
'Now this means a dialog box is active in IE
AppActivate IE
'activate IE which by default activates the dailogue window
SendKeys "{ENTER}", True
' your further code to send username and pwd
End If
On Error GoTo 0
'Further code if no any dialogue box is active in IE
End Sub

 

[Tim Coddington]

I mentioned SendKeys because I'm new at this. I guess now, I
have my work cut out for me.

Thanks all; this will get me started.
-Tim

 

[Tim Coddington]

OK. I got my username and password field names (userName & userPassword).
From the source, <input type="image" src="images/button-login-t.gif">, I
think this is the submit button. Then there is another line,

<form method="POST" action="/com.alphatrade.servlet.http.HttpLoginServlet"
target="_top" ENCTYPE="application/x-www-form-urlencoded" name="loginForm"
onsubmit="doLogin()">

that must capture the 'Login' submit button action to perform the
'doLogin()' routine.
How do I use this information to open this web page without manually
entering the login information?
-Thanks!!!

 

[R.VENKATARAMAN]

thank you Mr.shetty;. I got the code for submit also. but I do not know how
to dovetail thee codes into the vba statement

my code call the url b this statement
====
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://in.finance.yahoo.com/p?v&k=pf_2&d=v6",
Destination:=Range("B5"))
.Name = "p?v&k=pf_2&d=v6"
etc.
..FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
etc etc
yahoo sometimes call for logging in.
==================
I tried to place the following code statements in the beginning before the
above statements. that does not seem to help.

Dim login
Dim passwd
Dim submit As Boolean
login = "venkat1926"
passwd = InputBox("type password")
submit = True

thanks for all the help

 

[Sharad Naik]

What Mr. Shetty was talking about was navigationg with instance of IE.
You are trying it for the WebQuery.
It won't work with WebQuery.

Your choices are.
1. First manually open the site and login. Keep the site open, then run
webquery. (Which I think you do at present, is that correct?)

2. If above is correct then before the web query refresh, through VBA code
you start IE, navigate to the url. As Mr. Shetty says, you supply the
username and password through the code. (I don't know exactly how, I hope
Mr. Shetty will inform this.). Leave the navigated site open.
Then run your web query with Worksheets("xyz").QueryTables(1).Refresh

For opening the webpage through excel:
In your VBA project add reference to "Microsoft Internet Control".

Code could be as under:-

Dim IE As InternetExplorer, nCount as Long
Set IE = New InternetExplorer
IE.Navigate "http://in.finance.yahoo.com/p?v&k=pf_2&d=v6"
'code for username/pwd , I don't know how.
IE.Visible = True

'now allow some time for IE to navigate and login
'below loop will give it 10 sec.

nCount = Timer
Do While Timer < nCount + 10
DoEvents
Loop

'Then refresh your webquery

Worksheets("xyz").QueryTables(1).Refresh

'Allow about 30s for query refresh

nCount = Timer
Do While Timer < nCount + 30
DoEvents
Loop

'Then close IE
IE.Quit

 

[R.VENKATARAMAN]

thank you. I am only ding the pedestrian way of things.
await reply from Mr. shetty

 

[Shetty]

Sharad,
Thanks for reply. Since its weekend, I will try it on monday at my
office.

Tim,
Sharad is right. I was talking about an instance of the IE. I have not
checked if it works for WebQuery. Your approach to submit the
username/password will not work. I will post the code once I am back to
my office on monday.

Merry Christmas!!!

Shetty

 

[R.VENKATARAMAN]

wish you a happy newyear. take your own time. there is nothing urgent for me

 

[Shetty]

Hi VENKATARAMAN,

From your post, it seems that you are working to get stock quotes from

the Yahoo finance. I am not sure how much it will help you in the
WebQuery.

Here is the code which
opens yahoo login page
enters username and password
goes to finance page
gives co code to get quotes
opens quotes page
closes IE

You will need to modify it as per your requirement.
Watch for the word wrap.

Regards,


copy and paste following code in a module.
Sub Yahoo_Finance()

' Open Internet Explorer application
Set ie = CreateObject("InternetExplorer.Application")

With ie
..Visible = True
' Go to Yahoo login page
..Navigate "http://login.yahoo.com/"

' Loop until the page is fully loaded
Do Until .ReadyState = 4
DoEvents
Loop

' Make the desired selections on the web page and click the submit
Button
Set ipf = ie.document.all.Item("login")
ipf.Value = "myusername"
Set ipf = ie.document.all.Item("passwd")
ipf.Value = "mypassword"
Set ipf = ie.document.all.Item(".save")
ipf.Click

' Loop until the page is fully loaded
Do Until .ReadyState = 4
DoEvents
Loop
'ask info about company named bombril whose code is bobr3.sa
..Navigate "http://au.finance.yahoo.com/q?m=z&s=bobr3.sa&d=v1"
'for basic view
Do Until .ReadyState = 4
DoEvents
Loop

..Navigate "http://au.finance.yahoo.com/q?s=BOBR3.SA&d=2b" 'for
detailed view
Do Until .ReadyState = 4
DoEvents
Loop
..Navigate
"http://login.yahoo.com/config/login....done=http://au.finance.yahoo.com/?u&.intl=au"



End With

ie.Quit
End Sub

 

[Tim Coddington]

First, if I havn't said it before, I really appreciate all your help.
That being said, now I am back to where I was before reading about the
WebBrowser object (but in better shape). Have tried out your code as
follows:

Public Sub InitAlphaTrade()
Dim ieAlphaTrade As InternetExplorer

Set ieAlphaTrade = New InternetExplorer
ieAlphaTrade.Visible = True
ieAlphaTrade.Navigate "https://www.alphatrade.com/login"
Do Until ieAlphaTrade.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop

'Log on
Set ipf = ieAlphaTrade.Document.all.Item("userName")
ipf.Value = "Jeannie"
Set ipf = ieAlphaTrade.Document.all.Item("userPassword")
ipf.Value = "mypw"
ifp.Click
ieAlphaTrade.Quit
Set ieAlphaTrade = Nothing

End Sub

I haven't been able to figure out how the ifp.Click can be modified to work
for my case. Something to do with not understanding IE or web pages or
JavaScript or some such. Perhaps someone can help me. Here is a snip from
the HTML ...

<tr>
<td bgcolor="#3366cc">
<form method="POST"
action="/com.alphatrade.servlet.http.HttpLoginServlet" target="_top"
ENCTYPE="application/x-www-form-urlencoded" name="loginForm"
onsubmit="doLogin()">
<input type="hidden" name="stageNumber" value="0">
<input type="hidden" name="AppletCodeBase"
value="/E_Gate/tails/java/classes/">
<input type="hidden" name="SiteName" value="AlphaTrade.com">
<input type="hidden" name="logoURL" value="www.alphatrade.com">
<input type="hidden" name="badLoginPage"
value="http://www.alphatrade.com/badlogin.html">
<input type="hidden" name="thanksPage" value="ads.txt">
<table width="450" border="0" cellpadding="8">
<tr>
<td width="75" align="right" class="white1"><B>Login Name</b></td>
<td><input type="text" name="userName" size="10"
style="background-color:#dfefff"></td>
<td rowspan="3" valign="top"><p
style="font-size:9pt;color:#ffffff;"><b>Disclaimer</b>: &nbsp; As your
agreement for the receipt and use of market data provides, the securities
markets (1) reserve all rights to the market data that they make available;
(2) do not guarantee that data; and (3) shall not be liable for any loss due
either to their negligence or to any cause beyond their reasonable
control.</p></td>
</tr>
<tr>
<td align="right" class="white1"><B>Password</B></td>
<td><input type="password" name="userPassword" size="10"
style="background-color:#dfefff"></td>
</tr>
<tr>
<td>&nbsp;</td>
<td align="center"><input type="image"
src="images/button-login-t.gif"></td>
</tr>
</table>
</form>
<script language="JavaScript" type="text/javascript">
document.loginForm.userName.focus();
</script>
</td>
</tr>

Again, my appreciation and Merry Christmas!
-Tim

<snip>

 

[R.VENKATARAMAN]

thank you all for clearing the cobwebs in my brain.
I am already downloading yahoo finance (my customised portfolio) by
creating a query and refreshing it a number of times daily for the past one
year. . normally there is no problem because I have configured yahoo to
remember my user id and password in "this" computer and I am able to easily
refresh data. but very occasionally there is a glitch- I don't know whether
this is due to my computer, or I inadvertedly signed out or whether it is
yahoo problem -while refreshing data it asks for user id and password. I
wanted an automation in such circumstances. now I got out of the problem in
my own pedestrian newbie way. In the excel spreadsheet where the
getexternaldata query is located and the data refreshed I inserted a
hyperlink
somewhere which download directly the particular webpage in my default
browser (IE or opera or slimbrowser) . if necessary and if the webpage asks
for it I fill up my user id and password. Only if there is a glitch in
routine refreshing I use this hyperlink and then refresh data. this works.
this is also some sort of automation though in this case I do not use VBA.

thanks once again to all of you for clarifications. It is always useful to
have discussions with those who know more than what I know to clarify some
points.

 

[Shetty]

Tim,
Sorry, I am not expert in vba or ie.
I have seen your login page and the html source also. This site is
using the method "POST" to login. I never came across this method. The
code I posted will work if the site uses click event or javascript for
login process.
I have done some trials but could not workout the solution.

There are experts here who may help you solving this.

Regards,
Shetty.

 

[Tim Coddington]

Thanks anyway, Mr Shetty.