Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick data from web page (no query)
I want to pick a certain streaming value on a webpage and drop it into my
excel sheet, but following testcode won't work properly : =================== Sub Getquotes() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://www.binck.com/gekko/default.aspx? Redir=/gekko/common/ researchennieuws/fondsdetails/overzicht.aspx?binc=1068754" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Set last = IE.document.getElementByID("1068754|LAST") Range("A1") = last End Sub ================== This code fails at line : "Range("A1")=last" (failure 1004) In the html of the webpage i could find : ID="1068754|LAST" NAME="1068754|LAST" style="width:68px;"356,50<span class='c4d'00</span</td<td align="right" ID="1068754|TIME" NAME="1068754|TIME" style="width:68px;"9:42</td<td align="right" ID="1068754|LASTVOL" NAME="1068754|LASTVOL" style="width:68px;"1</td </tr<tr class="bkgnd_1" <td align="left"+/-</td<td class="cQuoteUp" align="right" etcetc so i guess the syntax of "Set last= ...etc" is correct ? What did i do wrong ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick data from web page (no query)
I'm not sure whnat you are looking for? You can use either
getelementsbytagname which returns a tag item like <Form ...................... /Form Or an Id Set last = IE.document.getElementByID("objInloggenScreen") <table id="objInloggenScreen" cellSpacing="0" cellPadding="4" border="0" First, I think you need to look at the source page from Internet Explorer go to menu View - Source. Second I think you need to set a break point and look at the objects. Add a break point just after the SET LAST statement. Click on the instruction and Press F9. Next add a Watch. highlight LAST with the mouse and select ADD WATCH. Now in the Watch window open up the plus (+) sign next to Last. Then do the same for ITEM if you are using a TAG. When referencing these object you will use like Last.item(1).innertext (for TAG) or for ID Last.innertext See code below Sub Getquotes() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://www.binck.com/gekko/default.aspx?" & _ "Redir=/gekko/common/researchennieuws/fondsdetails/overzicht.aspx?binc=1068754" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Set last = IE.document.getelementsbytagname("Form") 'or Set last = IE.document.getElementByID("objInloggenScreen") End Sub "rik" wrote: I want to pick a certain streaming value on a webpage and drop it into my excel sheet, but following testcode won't work properly : =================== Sub Getquotes() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://www.binck.com/gekko/default.aspx? Redir=/gekko/common/ researchennieuws/fondsdetails/overzicht.aspx?binc=1068754" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Set last = IE.document.getElementByID("1068754|LAST") Range("A1") = last End Sub ================== This code fails at line : "Range("A1")=last" (failure 1004) In the html of the webpage i could find : ID="1068754|LAST" NAME="1068754|LAST" style="width:68px;"356,50<span class='c4d'00</span</td<td align="right" ID="1068754|TIME" NAME="1068754|TIME" style="width:68px;"9:42</td<td align="right" ID="1068754|LASTVOL" NAME="1068754|LASTVOL" style="width:68px;"1</td </tr<tr class="bkgnd_1" <td align="left"+/-</td<td class="cQuoteUp" align="right" etcetc so i guess the syntax of "Set last= ...etc" is correct ? What did i do wrong ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick data from web page (no query)
I'm sorry, but i'm not with you anymore :
"objInloggenScreen" ? Does this mean : the ID that i found in the source of the web page (1068754|LAST, see pasted source in my first post) add a Watch : i think i could do this although i couldn't find the plus (+) you mentioned. Running with break point and watch gives me : Watch : : last : Nothing : Variant/Object : Module1.Getquotes so value = nothing on website it is = 350,30 I also do not understand your sentence "When referencing these objects you will use Last.innertext" Basically, what i want to do is putting this value (350,30) into cell A1. What do i do wrong ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick data from web page (no query)
You are return nothing because it didn' tfind an ID= matching your criteria.
Try one of my two instruction and you will see it will return a value Set last = IE.document.getelementsbytagname("Form") 'or Set last = IE.document.getElementByID("objInloggenScreen") Try this code which will get all the ID's. Then in the Internet Explorer view the Source code from the menu VIEW - SOURCE. You'll see the items in column A on the spreadsheet will match the ID= in the source Sub Getquotes2() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://www.binck.com/gekko/default.aspx?" & _ "Redir=/gekko/common/researchennieuws/fondsdetails/overzicht.aspx?binc=1068754" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Set last = IE.document.all RowCount = 1 For Each itm In IE.document.all If itm.ID < "" Then With ActiveSheet .Range("A" & RowCount) = itm.ID RowCount = RowCount + 1 End With End If Next itm End Sub "rik" wrote: I'm sorry, but i'm not with you anymore : "objInloggenScreen" ? Does this mean : the ID that i found in the source of the web page (1068754|LAST, see pasted source in my first post) add a Watch : i think i could do this although i couldn't find the plus (+) you mentioned. Running with break point and watch gives me : Watch : : last : Nothing : Variant/Object : Module1.Getquotes so value = nothing on website it is = 350,30 I also do not understand your sentence "When referencing these objects you will use Last.innertext" Basically, what i want to do is putting this value (350,30) into cell A1. What do i do wrong ? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick data from web page (no query)
Thanks for your answer but running your code does not bring anything in the
excel sheet ... It runs without any failure code but it seems it cannot find any ID's ??? In the web page source though, i found all codes (as shown before) in the pasted part of the code. Do you have a solution ? "Joel" wrote: You are return nothing because it didn' tfind an ID= matching your criteria. Try one of my two instruction and you will see it will return a value Set last = IE.document.getelementsbytagname("Form") 'or Set last = IE.document.getElementByID("objInloggenScreen") Try this code which will get all the ID's. Then in the Internet Explorer view the Source code from the menu VIEW - SOURCE. You'll see the items in column A on the spreadsheet will match the ID= in the source Sub Getquotes2() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://www.binck.com/gekko/default.aspx?" & _ "Redir=/gekko/common/researchennieuws/fondsdetails/overzicht.aspx?binc=1068754" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Set last = IE.document.all RowCount = 1 For Each itm In IE.document.all If itm.ID < "" Then With ActiveSheet .Range("A" & RowCount) = itm.ID RowCount = RowCount + 1 End With End If Next itm End Sub "rik" wrote: I'm sorry, but i'm not with you anymore : "objInloggenScreen" ? Does this mean : the ID that i found in the source of the web page (1068754|LAST, see pasted source in my first post) add a Watch : i think i could do this although i couldn't find the plus (+) you mentioned. Running with break point and watch gives me : Watch : : last : Nothing : Variant/Object : Module1.Getquotes so value = nothing on website it is = 350,30 I also do not understand your sentence "When referencing these objects you will use Last.innertext" Basically, what i want to do is putting this value (350,30) into cell A1. What do i do wrong ? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick data from web page (no query)
Check the ative sheet in the workbook starting at row 1. Here is what I got.
I discovered some of these items are hidden and can't be seen in the source. objInloggenScreen frmInloggen __EVENTTARGET __EVENTARGUMENT __LASTFOCUS __VIEWSTATE txtJava lnkLogo lblWelkom lblInloggen lblTaal DdlLanguage lblRekNrOfAdviseur txtLogin valTxtLogin txtPassword btnLogIn lnkbtnWachtwoordVergeten __EVENTVALIDATION oCapsLockWarn You can get the value of one of these items with Set last = IE.document.getElementByID("txtLogin") Data = last.value "rik" wrote: Thanks for your answer but running your code does not bring anything in the excel sheet ... It runs without any failure code but it seems it cannot find any ID's ??? In the web page source though, i found all codes (as shown before) in the pasted part of the code. Do you have a solution ? "Joel" wrote: You are return nothing because it didn' tfind an ID= matching your criteria. Try one of my two instruction and you will see it will return a value Set last = IE.document.getelementsbytagname("Form") 'or Set last = IE.document.getElementByID("objInloggenScreen") Try this code which will get all the ID's. Then in the Internet Explorer view the Source code from the menu VIEW - SOURCE. You'll see the items in column A on the spreadsheet will match the ID= in the source Sub Getquotes2() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://www.binck.com/gekko/default.aspx?" & _ "Redir=/gekko/common/researchennieuws/fondsdetails/overzicht.aspx?binc=1068754" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Set last = IE.document.all RowCount = 1 For Each itm In IE.document.all If itm.ID < "" Then With ActiveSheet .Range("A" & RowCount) = itm.ID RowCount = RowCount + 1 End With End If Next itm End Sub "rik" wrote: I'm sorry, but i'm not with you anymore : "objInloggenScreen" ? Does this mean : the ID that i found in the source of the web page (1068754|LAST, see pasted source in my first post) add a Watch : i think i could do this although i couldn't find the plus (+) you mentioned. Running with break point and watch gives me : Watch : : last : Nothing : Variant/Object : Module1.Getquotes so value = nothing on website it is = 350,30 I also do not understand your sentence "When referencing these objects you will use Last.innertext" Basically, what i want to do is putting this value (350,30) into cell A1. What do i do wrong ? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick data from web page (no query)
I get the same results
Can you explain me what this means for the page i try to work with ? "Joel" wrote: Check the ative sheet in the workbook starting at row 1. Here is what I got. I discovered some of these items are hidden and can't be seen in the source. objInloggenScreen frmInloggen __EVENTTARGET __EVENTARGUMENT __LASTFOCUS __VIEWSTATE txtJava lnkLogo lblWelkom lblInloggen lblTaal DdlLanguage lblRekNrOfAdviseur txtLogin valTxtLogin txtPassword btnLogIn lnkbtnWachtwoordVergeten __EVENTVALIDATION oCapsLockWarn You can get the value of one of these items with Set last = IE.document.getElementByID("txtLogin") Data = last.value "rik" wrote: Thanks for your answer but running your code does not bring anything in the excel sheet ... It runs without any failure code but it seems it cannot find any ID's ??? In the web page source though, i found all codes (as shown before) in the pasted part of the code. Do you have a solution ? "Joel" wrote: You are return nothing because it didn' tfind an ID= matching your criteria. Try one of my two instruction and you will see it will return a value Set last = IE.document.getelementsbytagname("Form") 'or Set last = IE.document.getElementByID("objInloggenScreen") Try this code which will get all the ID's. Then in the Internet Explorer view the Source code from the menu VIEW - SOURCE. You'll see the items in column A on the spreadsheet will match the ID= in the source Sub Getquotes2() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://www.binck.com/gekko/default.aspx?" & _ "Redir=/gekko/common/researchennieuws/fondsdetails/overzicht.aspx?binc=1068754" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Set last = IE.document.all RowCount = 1 For Each itm In IE.document.all If itm.ID < "" Then With ActiveSheet .Range("A" & RowCount) = itm.ID RowCount = RowCount + 1 End With End If Next itm End Sub "rik" wrote: I'm sorry, but i'm not with you anymore : "objInloggenScreen" ? Does this mean : the ID that i found in the source of the web page (1068754|LAST, see pasted source in my first post) add a Watch : i think i could do this although i couldn't find the plus (+) you mentioned. Running with break point and watch gives me : Watch : : last : Nothing : Variant/Object : Module1.Getquotes so value = nothing on website it is = 350,30 I also do not understand your sentence "When referencing these objects you will use Last.innertext" Basically, what i want to do is putting this value (350,30) into cell A1. What do i do wrong ? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick data from web page (no query)
There are a few methods of getting the next page of a webpage. You can
activate the NEXT button, you can active the sheet number on the webpage, you can enter a new URL with the page number as part of the URL. I think you are refereing to the last method. Here is some code that I used on a different project I put "page=1&" at the end of the request. The only problem is determining how many pages there are for the Webpage. I checked the TAG "B" and looked at the property nextsibling to find out if there was more pages. URL = "http://www.config.nissanusa.com/Dispatch.jsp" Request = "?changeModel=execute&" & _ "currentBodyType=" & Bodytype(body) & "&" & _ "_scrollPos=158&" & _ "locateConfig=true&" & _ "__action4=&" & _ "state_token=2%3A17%3Anissan%7Calt%7CALL%7C0%7CA4A AAAAAAAAA%7C%3A&" & _ ".CurrentState=DealerMatchingVehiclesBrowse&" & _ "unselectVehicle=null&" & _ "tool=null&" & _ "sdealerID=" & dealerNumber & "&" & _ "sdealerContactable=true&" & _ "error_noResults=&" Page = 0 Do PageRequest = Request & "page=" & Page & "&" IE.Navigate2 URL & PageRequest Do While IE.readyState < 4 DoEvents Loop Do While IE.busy = True DoEvents Loop Call GetCarDetails(IE, DealerName, City, Distance, BodyStyle) If IE.document.getelementsbytagname("B").Length < 0 Then Set PageNumbers = _ IE.document.getelementsbytagname("B") _ .Item(0).nextsibling If Not PageNumbers Is Nothing Then Page = Val(PageNumbers.innertext) - 1 End If Else Set PageNumbers = Nothing End If Loop While Not PageNumbers Is Nothing "rik" wrote: I get the same results Can you explain me what this means for the page i try to work with ? "Joel" wrote: Check the ative sheet in the workbook starting at row 1. Here is what I got. I discovered some of these items are hidden and can't be seen in the source. objInloggenScreen frmInloggen __EVENTTARGET __EVENTARGUMENT __LASTFOCUS __VIEWSTATE txtJava lnkLogo lblWelkom lblInloggen lblTaal DdlLanguage lblRekNrOfAdviseur txtLogin valTxtLogin txtPassword btnLogIn lnkbtnWachtwoordVergeten __EVENTVALIDATION oCapsLockWarn You can get the value of one of these items with Set last = IE.document.getElementByID("txtLogin") Data = last.value "rik" wrote: Thanks for your answer but running your code does not bring anything in the excel sheet ... It runs without any failure code but it seems it cannot find any ID's ??? In the web page source though, i found all codes (as shown before) in the pasted part of the code. Do you have a solution ? "Joel" wrote: You are return nothing because it didn' tfind an ID= matching your criteria. Try one of my two instruction and you will see it will return a value Set last = IE.document.getelementsbytagname("Form") 'or Set last = IE.document.getElementByID("objInloggenScreen") Try this code which will get all the ID's. Then in the Internet Explorer view the Source code from the menu VIEW - SOURCE. You'll see the items in column A on the spreadsheet will match the ID= in the source Sub Getquotes2() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://www.binck.com/gekko/default.aspx?" & _ "Redir=/gekko/common/researchennieuws/fondsdetails/overzicht.aspx?binc=1068754" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Set last = IE.document.all RowCount = 1 For Each itm In IE.document.all If itm.ID < "" Then With ActiveSheet .Range("A" & RowCount) = itm.ID RowCount = RowCount + 1 End With End If Next itm End Sub "rik" wrote: I'm sorry, but i'm not with you anymore : "objInloggenScreen" ? Does this mean : the ID that i found in the source of the web page (1068754|LAST, see pasted source in my first post) add a Watch : i think i could do this although i couldn't find the plus (+) you mentioned. Running with break point and watch gives me : Watch : : last : Nothing : Variant/Object : Module1.Getquotes so value = nothing on website it is = 350,30 I also do not understand your sentence "When referencing these objects you will use Last.innertext" Basically, what i want to do is putting this value (350,30) into cell A1. What do i do wrong ? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick data from web page (no query)
I guess your last post was not relevant because it seems there's only 1 page,
each different page has its own URL. I found out with code below, i tested either with homepage or with the page that i try to work with. My conclusion is : the data that i want to transfer into excel are hidden (you can't refer to them by trheir ID ??). I'm afraid there's no solution. Hope that i'm wrong ??? =============================== Sub pages() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True 'test with either next line URL = "https://www.binck.com/gekko/default.aspx?Redir=/gekko/common/researchennieuws/fondsdetails/overzicht.aspx?binc=1068754" 'or next line 'URL = "https://www.binck.nl/gekko/common/inloggen.aspx" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Set last = IE.document.all teller = 0 RowCount = 1 For Each itm In IE.document.all If itm.ID < "" Then With ActiveSheet .Range("A" & RowCount) = itm.ID RowCount = RowCount + 1 End With Else 'do nothing End If teller = teller + 1 If teller = 5 Then GoTo line1 Else End If Next itm line1: Range("K1") = 1 If IE.document.getelementsbytagname("B").Length < 0 Then Set PageNumbers = _ IE.document.getelementsbytagname("B") _ .Item(0).nextsibling If Not PageNumbers Is Nothing Then Page = Val(PageNumbers.innertext) - 1 Range("d1") = Page End If Else Set PageNumbers = Nothing Range("d2") = Page End If End Sub ================================================== == |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick data from web page (no query)
Everything can be obtained on the webpage. You can also press any button or
object on the page . Enter data in Textboxes go to remote links. Anything you can do with the keyboard and mouse can be done in the code. There are items like ONCLICK. Run code below and you will see. If you can't find a label with the item you are looking for you can look in the innertext. Sub DumpData() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://www.binck.com/gekko/default.aspx?" & _ "Redir=/gekko/common/researchennieuws/fondsdetails/overzicht.aspx?binc=1068754" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Set last = IE.document.all RowCount = 1 For Each itm In IE.document.all If itm.ID < "" Then With ActiveSheet .Range("A" & RowCount) = itm.classname .Range("B" & RowCount) = itm.tagname .Range("C" & RowCount) = itm.onclick .Range("D" & RowCount) = itm.ID .Range("E" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 End With End If Next itm End Sub "rik" wrote: I guess your last post was not relevant because it seems there's only 1 page, each different page has its own URL. I found out with code below, i tested either with homepage or with the page that i try to work with. My conclusion is : the data that i want to transfer into excel are hidden (you can't refer to them by trheir ID ??). I'm afraid there's no solution. Hope that i'm wrong ??? =============================== Sub pages() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True 'test with either next line URL = "https://www.binck.com/gekko/default.aspx?Redir=/gekko/common/researchennieuws/fondsdetails/overzicht.aspx?binc=1068754" 'or next line 'URL = "https://www.binck.nl/gekko/common/inloggen.aspx" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Set last = IE.document.all teller = 0 RowCount = 1 For Each itm In IE.document.all If itm.ID < "" Then With ActiveSheet .Range("A" & RowCount) = itm.ID RowCount = RowCount + 1 End With Else 'do nothing End If teller = teller + 1 If teller = 5 Then GoTo line1 Else End If Next itm line1: Range("K1") = 1 If IE.document.getelementsbytagname("B").Length < 0 Then Set PageNumbers = _ IE.document.getelementsbytagname("B") _ .Item(0).nextsibling If Not PageNumbers Is Nothing Then Page = Val(PageNumbers.innertext) - 1 Range("d1") = Page End If Else Set PageNumbers = Nothing Range("d2") = Page End If End Sub ================================================== == |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick data from web page (no query)
I'm sorry to say but i tried "dumpdata" with 3 web pages :
1. the page to log in (which you can see as well) : it works perfectly well 2. the home page you get on once you're logged in : it runs without failure but nothing is dumped into the excel sheet : he finds 9 empty items 3. the page i wanted to work with : same as 2 : also 9 empty items, so it seems to be the same page, although a different page is seen on the screen There must be something tricky that i don't understand ??? Does it make sense to try to go to the wanted page by filling in name and password, and then click 2 buttons automatically ? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick data from web page (no query)
there are two possible reasons that I can immediately think of that may be
causing the problem. 1) That the items are not in the IE.Document. It is something else beside a document. 2) The code isn't waiting for the webpage to get the data before it reads the data. I usually set break points a first get the code working by stepping through the instructions. I can wait until the webpage is updated before continue to step through the code. Then I go back and try to get it to work automatically. Here is a sample of code where I couldn't get the IE READY to wrok. I had to use a return value from the page to force the code to wait. 'get search button Set but = IE.document.getElementById("mainSearchButton") 'put distance in listbox on webpage Set radius = IE.document.getElementById("radius") radius.Value = "100" 'search again a larger distance 'Select Search button and activate but.Select but.Click On Error Resume Next ' Defer error handling. Do Err.Clear Set PageNumber = IE.document.getElementById("pageNumber") Pages = PageNumber.Value DoEvents Loop While Err.Number < 0 On Error GoTo 0 "rik" wrote: I'm sorry to say but i tried "dumpdata" with 3 web pages : 1. the page to log in (which you can see as well) : it works perfectly well 2. the home page you get on once you're logged in : it runs without failure but nothing is dumped into the excel sheet : he finds 9 empty items 3. the page i wanted to work with : same as 2 : also 9 empty items, so it seems to be the same page, although a different page is seen on the screen There must be something tricky that i don't understand ??? Does it make sense to try to go to the wanted page by filling in name and password, and then click 2 buttons automatically ? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick data from web page (no query)
It's not reason nr. 2 : i changed the code a little bit (see below) and got
as a result : nothing in colums AE cell F1 = 1 cell G1 = 1, G2=2 etc untill G9=9 Setting a break point didn't change anything. So probably it's reason nr. 1 Guess there's nothing to do about it ? =========================================== Sub DumpData() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://www.binck.com/gekko/default.aspx?" & _ "Redir=/gekko/common/researchennieuws/fondsdetails/overzicht.aspx?binc=1068754" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Set last = IE.document.all RowCount = 1 For Each itm In IE.document.all If itm.ID < "" Then With ActiveSheet .Range("A" & RowCount) = itm.classname .Range("B" & RowCount) = itm.tagname .Range("C" & RowCount) = itm.onclick .Range("D" & RowCount) = itm.ID .Range("E" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 End With Else Range("G" & RowCount) = RowCount RowCount = RowCount + 1 End If Next itm Range("F1") = 1 End Sub ===================================== "Joel" wrote: there are two possible reasons that I can immediately think of that may be causing the problem. 1) That the items are not in the IE.Document. It is something else beside a document. 2) The code isn't waiting for the webpage to get the data before it reads the data. I usually set break points a first get the code working by stepping through the instructions. I can wait until the webpage is updated before continue to step through the code. Then I go back and try to get it to work automatically. Here is a sample of code where I couldn't get the IE READY to wrok. I had to use a return value from the page to force the code to wait. 'get search button Set but = IE.document.getElementById("mainSearchButton") 'put distance in listbox on webpage Set radius = IE.document.getElementById("radius") radius.Value = "100" 'search again a larger distance 'Select Search button and activate but.Select but.Click On Error Resume Next ' Defer error handling. Do Err.Clear Set PageNumber = IE.document.getElementById("pageNumber") Pages = PageNumber.Value DoEvents Loop While Err.Number < 0 On Error GoTo 0 "rik" wrote: I'm sorry to say but i tried "dumpdata" with 3 web pages : 1. the page to log in (which you can see as well) : it works perfectly well 2. the home page you get on once you're logged in : it runs without failure but nothing is dumped into the excel sheet : he finds 9 empty items 3. the page i wanted to work with : same as 2 : also 9 empty items, so it seems to be the same page, although a different page is seen on the screen There must be something tricky that i don't understand ??? Does it make sense to try to go to the wanted page by filling in name and password, and then click 2 buttons automatically ? |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick data from web page (no query)
There are 3 things I would still do.
1) Put break point before the loop to load data on the wroksheet. Wait 2 minutes and run code. Then check again if there is data in the worksheet 2) Add as a watch IE. Go back to break point in i1) above and set what objects are under IE. You may be able to figure out if it is not a document what it really is. Maybe an Excel or Access Object 3) View source in Internet explorer of the new webpage you are viewing. Maybe there are some comments to help you figure out what to do. "rik" wrote: It's not reason nr. 2 : i changed the code a little bit (see below) and got as a result : nothing in colums AE cell F1 = 1 cell G1 = 1, G2=2 etc untill G9=9 Setting a break point didn't change anything. So probably it's reason nr. 1 Guess there's nothing to do about it ? =========================================== Sub DumpData() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://www.binck.com/gekko/default.aspx?" & _ "Redir=/gekko/common/researchennieuws/fondsdetails/overzicht.aspx?binc=1068754" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Set last = IE.document.all RowCount = 1 For Each itm In IE.document.all If itm.ID < "" Then With ActiveSheet .Range("A" & RowCount) = itm.classname .Range("B" & RowCount) = itm.tagname .Range("C" & RowCount) = itm.onclick .Range("D" & RowCount) = itm.ID .Range("E" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 End With Else Range("G" & RowCount) = RowCount RowCount = RowCount + 1 End If Next itm Range("F1") = 1 End Sub ===================================== "Joel" wrote: there are two possible reasons that I can immediately think of that may be causing the problem. 1) That the items are not in the IE.Document. It is something else beside a document. 2) The code isn't waiting for the webpage to get the data before it reads the data. I usually set break points a first get the code working by stepping through the instructions. I can wait until the webpage is updated before continue to step through the code. Then I go back and try to get it to work automatically. Here is a sample of code where I couldn't get the IE READY to wrok. I had to use a return value from the page to force the code to wait. 'get search button Set but = IE.document.getElementById("mainSearchButton") 'put distance in listbox on webpage Set radius = IE.document.getElementById("radius") radius.Value = "100" 'search again a larger distance 'Select Search button and activate but.Select but.Click On Error Resume Next ' Defer error handling. Do Err.Clear Set PageNumber = IE.document.getElementById("pageNumber") Pages = PageNumber.Value DoEvents Loop While Err.Number < 0 On Error GoTo 0 "rik" wrote: I'm sorry to say but i tried "dumpdata" with 3 web pages : 1. the page to log in (which you can see as well) : it works perfectly well 2. the home page you get on once you're logged in : it runs without failure but nothing is dumped into the excel sheet : he finds 9 empty items 3. the page i wanted to work with : same as 2 : also 9 empty items, so it seems to be the same page, although a different page is seen on the screen There must be something tricky that i don't understand ??? Does it make sense to try to go to the wanted page by filling in name and password, and then click 2 buttons automatically ? |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick data from web page (no query)
1) same results, 9 empty items found
2)IE="Windows Internet Explorer" type=variant/object// AddressBar=true// Application:no variables type=Object/Webbrowser2// Busy=false// Container=nothing// Document:no variabloes type=Object/HTMLDocument// FullName=c:/programfiles/internetexplorer/iexplore.exe// etcetc LocationName="BinckBank" // LocationURL : "https://www.binck.com/gekko/default.aspx?Redir=/gekko/common/researchennieuws/fondsdetails/overzicht.aspx?binc=1068754" // MenuBar=true // Name="WindowsInternetExplorer // Offline=false // Parent:novariables type=Object/WebBrowser2 // Readystate=READYSTATE_COMPLETE RegisterAsBrowser = false // RegisterAsDropTarget=true // etc Silent=false Statusbar=true Statustext="ready" etcetc Type="HTML-document" etc 3) i printed this from the start (8 pages) : no more ideas as far as i'm concerned ... Thanks for your help anyway !! "Joel" wrote: There are 3 things I would still do. 1) Put break point before the loop to load data on the wroksheet. Wait 2 minutes and run code. Then check again if there is data in the worksheet 2) Add as a watch IE. Go back to break point in i1) above and set what objects are under IE. You may be able to figure out if it is not a document what it really is. Maybe an Excel or Access Object 3) View source in Internet explorer of the new webpage you are viewing. Maybe there are some comments to help you figure out what to do. "rik" wrote: It's not reason nr. 2 : i changed the code a little bit (see below) and got as a result : nothing in colums AE cell F1 = 1 cell G1 = 1, G2=2 etc untill G9=9 Setting a break point didn't change anything. So probably it's reason nr. 1 Guess there's nothing to do about it ? =========================================== Sub DumpData() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://www.binck.com/gekko/default.aspx?" & _ "Redir=/gekko/common/researchennieuws/fondsdetails/overzicht.aspx?binc=1068754" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Set last = IE.document.all RowCount = 1 For Each itm In IE.document.all If itm.ID < "" Then With ActiveSheet .Range("A" & RowCount) = itm.classname .Range("B" & RowCount) = itm.tagname .Range("C" & RowCount) = itm.onclick .Range("D" & RowCount) = itm.ID .Range("E" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 End With Else Range("G" & RowCount) = RowCount RowCount = RowCount + 1 End If Next itm Range("F1") = 1 End Sub ===================================== "Joel" wrote: there are two possible reasons that I can immediately think of that may be causing the problem. 1) That the items are not in the IE.Document. It is something else beside a document. 2) The code isn't waiting for the webpage to get the data before it reads the data. I usually set break points a first get the code working by stepping through the instructions. I can wait until the webpage is updated before continue to step through the code. Then I go back and try to get it to work automatically. Here is a sample of code where I couldn't get the IE READY to wrok. I had to use a return value from the page to force the code to wait. 'get search button Set but = IE.document.getElementById("mainSearchButton") 'put distance in listbox on webpage Set radius = IE.document.getElementById("radius") radius.Value = "100" 'search again a larger distance 'Select Search button and activate but.Select but.Click On Error Resume Next ' Defer error handling. Do Err.Clear Set PageNumber = IE.document.getElementById("pageNumber") Pages = PageNumber.Value DoEvents Loop While Err.Number < 0 On Error GoTo 0 "rik" wrote: I'm sorry to say but i tried "dumpdata" with 3 web pages : 1. the page to log in (which you can see as well) : it works perfectly well 2. the home page you get on once you're logged in : it runs without failure but nothing is dumped into the excel sheet : he finds 9 empty items 3. the page i wanted to work with : same as 2 : also 9 empty items, so it seems to be the same page, although a different page is seen on the screen There must be something tricky that i don't understand ??? Does it make sense to try to go to the wanted page by filling in name and password, and then click 2 buttons automatically ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pick a cell to go to a sheet in the workbook (greeting page) | Excel Discussion (Misc queries) | |||
Web Query not downloading data filtered in web page | Excel Programming | |||
Extracting data from web page with an imbedded DB query | Excel Programming | |||
GIF image on web-page corrupts data fetched with Web Query | Excel Discussion (Misc queries) | |||
excel web query problem, data not on actual page? | Excel Programming |