Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
data from external webpage
I searched the googles groups to get some insight into this. But I could
not get exactly what I want. I know some experts do not recomment using <sendkeys however I want to get data from a webpage into excel . It is is not simple using <new web query. In the webpage I have to click one of the two option(or are they checkboxes?) boxes and then enter a text in the small window. finally I have to click something like a command button <submit I do have some codes wherein you have to simply enter the user id and password in the code and run it. . The EXTRACT ONLY of the sub is While appIE.busy DoEvents Wend appIE.navigate "https://access.leggmason.com/" While appIE.ReadyState < READYSTATE_COMPLETE DoEvents Wend SendKeys "excel", True '----------this is user id SendKeys "{TAB}", True SendKeys "hello", True '------------this is password SendKeys "{ENTER}", True Of course this code also gives problem at READYSTATE_COMPLETE but I used a variant dim for this. it works in some cases but it does not work in some other cases where due to security reason the webpage asks again for the password(example-yahoo mail). I tried to modify the same code in the mouse clicking case also. B ut I am stumped as I could not find the keyboard short cut for clicking the mouse. In the case of <submit button i presume that I can use <enter key. It is obvious I have not understood fully the sub Highly thankful for any suggestion how to go about . I am fairly familiar with excel and VBA. mine excel 2000/windows 98 SE the relevant url is http://www.bseindia.com/histdata/stockprc.asp regarads |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
data from external webpage
Hi RV,
If I read this correctly, you're trying to do something I just figured out (and with your help, by the way, from past posts). ' Open Internet Explorer application Set ie = CreateObject("InternetExplorer.Application") With ie ..Visible = True ' Go to login page ..Navigate "http(etc)" ' 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 = "your username" Set ipf = ie.Document.all.Item("passwd") ipf.Value = "your password" Set ipf = ie.Document.all.Item("login_form") ipf.submit ' Loop while web site homepage loads Do Until ie.Document.URLUnencoded = "http(etc)" DoEvents Loop End With I interpreted the Item names from the login web page HTML code (using ViewSource). For some pages, I've had to replace "ipf.submit" with "ipf.click". I also had trouble with getting loops to work while the web pages loaded. In the above case, assigning the procedure to wait for the post-login page to load worked. Otherwise I've generally been using Application.Wait Now + TimeValue("00:00:05") setting the time allowed according to how quickly pages for a given site usually load. I had 3 sites in particular I wanted to use this on and it's now been working without fail for a couple weeks. Good luck, Brian "R.VENKATARAMAN" wrote in message ... I searched the googles groups to get some insight into this. But I could not get exactly what I want. I know some experts do not recomment using <sendkeys however I want to get data from a webpage into excel . It is is not simple using <new web query. In the webpage I have to click one of the two option(or are they checkboxes?) boxes and then enter a text in the small window. finally I have to click something like a command button <submit I do have some codes wherein you have to simply enter the user id and password in the code and run it. . The EXTRACT ONLY of the sub is While appIE.busy DoEvents Wend appIE.navigate "https://access.leggmason.com/" While appIE.ReadyState < READYSTATE_COMPLETE DoEvents Wend SendKeys "excel", True '----------this is user id SendKeys "{TAB}", True SendKeys "hello", True '------------this is password SendKeys "{ENTER}", True Of course this code also gives problem at READYSTATE_COMPLETE but I used a variant dim for this. it works in some cases but it does not work in some other cases where due to security reason the webpage asks again for the password(example-yahoo mail). I tried to modify the same code in the mouse clicking case also. B ut I am stumped as I could not find the keyboard short cut for clicking the mouse. In the case of <submit button i presume that I can use <enter key. It is obvious I have not understood fully the sub Highly thankful for any suggestion how to go about . I am fairly familiar with excel and VBA. mine excel 2000/windows 98 SE the relevant url is http://www.bseindia.com/histdata/stockprc.asp regarads |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
data from external webpage
thank you for the comprehensive reply. shall try it and post back
Brian Delaney wrote in message ... Hi RV, If I read this correctly, you're trying to do something I just figured out (and with your help, by the way, from past posts). ' Open Internet Explorer application Set ie = CreateObject("InternetExplorer.Application") With ie .Visible = True ' Go to login page .Navigate "http(etc)" ' 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 = "your username" Set ipf = ie.Document.all.Item("passwd") ipf.Value = "your password" Set ipf = ie.Document.all.Item("login_form") ipf.submit ' Loop while web site homepage loads Do Until ie.Document.URLUnencoded = "http(etc)" DoEvents Loop End With I interpreted the Item names from the login web page HTML code (using ViewSource). For some pages, I've had to replace "ipf.submit" with "ipf.click". I also had trouble with getting loops to work while the web pages loaded. In the above case, assigning the procedure to wait for the post-login page to load worked. Otherwise I've generally been using Application.Wait Now + TimeValue("00:00:05") setting the time allowed according to how quickly pages for a given site usually load. I had 3 sites in particular I wanted to use this on and it's now been working without fail for a couple weeks. Good luck, Brian "R.VENKATARAMAN" wrote in message ... I searched the googles groups to get some insight into this. But I could not get exactly what I want. I know some experts do not recomment using <sendkeys however I want to get data from a webpage into excel . It is is not simple using <new web query. In the webpage I have to click one of the two option(or are they checkboxes?) boxes and then enter a text in the small window. finally I have to click something like a command button <submit I do have some codes wherein you have to simply enter the user id and password in the code and run it. . The EXTRACT ONLY of the sub is While appIE.busy DoEvents Wend appIE.navigate "https://access.leggmason.com/" While appIE.ReadyState < READYSTATE_COMPLETE DoEvents Wend SendKeys "excel", True '----------this is user id SendKeys "{TAB}", True SendKeys "hello", True '------------this is password SendKeys "{ENTER}", True Of course this code also gives problem at READYSTATE_COMPLETE but I used a variant dim for this. it works in some cases but it does not work in some other cases where due to security reason the webpage asks again for the password(example-yahoo mail). I tried to modify the same code in the mouse clicking case also. B ut I am stumped as I could not find the keyboard short cut for clicking the mouse. In the case of <submit button i presume that I can use <enter key. It is obvious I have not understood fully the sub Highly thankful for any suggestion how to go about . I am fairly familiar with excel and VBA. mine excel 2000/windows 98 SE the relevant url is http://www.bseindia.com/histdata/stockprc.asp regarads |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External Data Warning Message - I have No External Data in wrkbk | Excel Discussion (Misc queries) | |||
Problem importing external data / Automatic publishing webpage | Excel Discussion (Misc queries) | |||
Getting External Data based on criteria insde of the external data | Excel Discussion (Misc queries) | |||
extracting non-tabular data from webpage | Excel Discussion (Misc queries) | |||
Saving data to a webpage | New Users to Excel |