data from web (1 Viewer)

[bhelmy]

need to link data from web to tabel

the web is --- http://www.egx.com.eg/English/marketsummry.aspx

and need to auto update when open table

Please help

 

[sneuberg]

I don't believe there is any easy way to do this. The hard way would involve using the Microsoft HTML Object. For example if you add the Microsoft HTML Object Library reference, put the follow code (I found here) in a module, and run it you will see the contents of the Web page in the Immediate window.

Private Sub Form_Load()
    Dim doc1 As New HTMLDocument, doc2 As New HTMLDocument
    Dim col1 As IHTMLElementCollection, iLoop As Integer
    Dim sPoints As String, sScore As String, sTotal As String
    Set doc1 = doc2.createDocumentFromUrl("http://www.egx.com.eg/English/marketsummry.aspx", "null")
    Do Until doc1.ReadyState = "complete"
        DoEvents
    Loop
    Set col1 = doc1.all.tags("TD")
    For iLoop = 0 To col1.length Step 3
        If iLoop + 2 < col1.length Then
            sPoints = col1.Item(iLoop).outerText
            sScore = col1.Item(iLoop + 1).outerText
            sTotal = col1.Item(iLoop + 2).outerText
            Debug.Print sPoints & "," & sScore & "," & sTotal
        End If
    Next iLoop
End Sub

This trick is to extract the parts you want. If you look at the Web page source you will see that the data seems to have a consistent system of ids. For example the row of stock information is like:

        <tr id="ctl00_C_M_RpList_ctl00_rldata">

and the cells are like:

	<td align="left" class="Borderleft BorderRight"><span id="ctl00_C_M_RpList_ctl00_lblList1">Stocks</span></td>

So you might be able to use this regularity to your advantage but no matter what you do you I think you could end up with maintenance issues. Whenever they change this page you might have to update your code.

 

[The_Doc_Man]

I agree with Steve. Taking data from a web page is a wide open invitation to be at the whims of a new web designer every time your data source hires one.