Web browser question
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?logout=1&.src=quote&.done=http://au.finance.yahoo.com/%3fu&.intl=au"
End With
ie.Quit
End Sub
R.VENKATARAMAN wrote:
thank you. I am only ding the pedestrian way of things.
await reply from Mr. shetty
Sharad Naik wrote in message
...
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" &&& wrote in message
...
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
Shetty wrote in message
oups.com...
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.
|