Copy data from HTML table to Excel

Hi,

I am trying to execute some code that will open a website, submit a form and retrieve some data from that same website.

So far I have been able to accomplish two of the three things mentioned above, but I seem stuck now.

Basically what my code does so far is: opens IE, waits for it to load, copies a value from a cell to a website form and submits the form.

So far so good, problem is I don't know how to copy the value I want and paste it back to a cell in Excel.

Can anybody help me?

The website I am testing this on is: "http://www.abc.es/loteria-de-navidad/buscardecimo.asp"

What I would like to do is retrieve the 20 euro value with the red background when the value submitted to the web form is: "11750"

Here's what i got this far:

Sub Macro1()

Dim IE As Object
Dim rng As Range
Dim frm As Object


    Set IE = CreateObject("InternetExplorer.Application")
 
    Set rng = Range("A1")
 
    IE.Visible = True
 
    IE.Navigate "http://www.abc.es/loteria-de-navidad/buscardecimo.asp"
 
    Do
        DoEvents
    Loop Until IE.ReadyState = 4
    
'fill form
    IE.Document.forms("form1").all("numero").Value = rng

'submit form
    Set frm = IE.Document.getElementById("form1")
    frm.Submit
    
End Sub

If you find any problem with the code, please do not hesitate to let me know

Originally Posted by Lalo1977

If you find any problem with the code, please do not hesitate to let me know

Lalo1977,

Your solution runs smoothly! It's brilliant!

Since I am completely new to anything related to javascript and HTML, could you explain how you came up with the website you are accessing? How can I reproduce this in other websites with HTML tables?

I have tried your solution in the website I provided and it does not seem to detect the element in the table, the for each...next loop does not detect the element I want, is there a reason to this?

Also, I am trying to create a more sophisticated solution which needs to loop through the same page several times, would you know how to activate a window/tab that is already open in IE instead of closing it and opening it again?

Thanks A LOT for your help! You are the best!

Hello javi-xls,
I am glad to know that the code worked.
The website that you provided (http://www.abc.es/loteria-de-navidad/buscardecimo.asp) uses iframe tags. "iframes" are HTML documents inside another HTML document. In this case the iframe with source "http://loteria.abc.es/fcgi-bin/premio.pl?numero=&importe=" is inside the main document.
I realized this by using Chrome´s HTML element inspector. You can activate this inspector by first going to the website address with Chrome, then pressing CTRL+Shift+C, then selecting the element with the mouse pointer and finally clicking at it. After clicking the element you will see the element position in the HTML source code. By analysing this HTML code you will be that the able to see that the span element you re looking for is inside a td element which is inside a table element and this element is inside anothet, and so on.. and all these elements are inside an "iframe" tag that has an "src" attribute, and the value of this src attribute is = http://loteria.abc.es/fcgi-bin/premi...mero=&importe= which is the URL of another page. By filling the numero= and the importe= parameters your browser makes a request to a web server that returns a page with the information submitted by the parameters.
Could you please mark your thread as 'Solved' to keep the moderators happy You can do that in thread tools, the button is on the right, just above the first post. Have a nice day!