Web Query - Select Table not being captured

[CodyMonster]

Maybe someone can help me with this one..

I'm trying to get the data from this site

http://www.cmegroup.com/clearport/p...scending&SortType=text&FilterOn=1&PageStart=1

When going through the Web Query in Excel and pulling data with the above address. I get the option to pull the data from the products table..the table with the code/name/product group/sub group..etc etc...

But when I launch the query, it doesn't pull the table data..I get everything but the product list.

Can anyone suggest a way to get this data through a query into excel?

Thanks for your help!!!!

Cody

 

[Legacy 98055]

I don't see an option on that page to specify a date.

 

[CodyMonster]

If you go to the original webpage

http://www.cmegroup.com/clearport/p...scending&SortType=text&FilterOn=1&PageStart=1

And look under "view green products"

 

[CodyMonster]

Bump...

If you go to the original webpage

http://www.cmegroup.com/clearport/p...scending&SortType=text&FilterOn=1&PageStart=1

And look under "view green products"

 

[Legacy 98055]

There is no date listed. I have tried several times....

 

[CodyMonster]

There is no date listed. I have tried several times....

If you go to the original page..

http://www.cmegroup.com/clearport/p...scending&SortType=text&FilterOn=1&PageStart=1

Its right under View Green Products..

Last Updated February 1, 2010 6:15:02 PM Chicago Time

 

[Legacy 98055]

http://mzz6pa.blu.livefilestore.com...ysuaTLHcn_G8NalM7x2VjW4YDd/MrExcel2200887.jpg

 

[CodyMonster]

http://i650.photobucket.com/albums/uu222/KCYR/CME.jpg

I think something is wrong with your browser...

 

[Legacy 98055]

Probably not the browser but timing out because I am using a mobile broadband connection. Try this long winded approach that is untested. Run Example. The last update time should appear in a message box...

Sub Example()
    Dim Res As String
    Res = GetLastUpdate
    MsgBox Trim(Replace(Replace(Res, "Last Updated ", ""), "Chicago Time", ""))
End Sub

Function GetLastUpdate(Optional TimeOutSeconds As Double = 10) As String
    Dim ie As New InternetExplorer, doc As HTMLDocument, b As HTMLGenericElement
    Dim TimeOutTime As Date
    
    ie.navigate "http://www.cmegroup.com/clearport/product-slate.html#sortKey=1&sortOrder=ascending&SortType=text&FilterOn=1&PageStart=1"
    Do Until Not ie.Busy And ie.readyState >= READYSTATE_INTERACTIVE: DoEvents: Loop
    
    TimeOutTime = DateAdd("n", TimeOutSeconds, Now)
    Do
        Set doc = ie.document
        For Each b In doc.getElementsByTagName("B")
            If InStr(b.innerText, "Last Updated") Then
                GetLastUpdate = b.innerText
                Exit Do
            End If
        Next
    Loop Until Now > TimeOutTime
    ie.Quit

End Function

 

[Legacy 98055]

Here is a latebound example.

Sub Example()
    Dim Res As String
    Res = GetLastUpdate(30)
    MsgBox Trim(Replace(Replace(Res, "Last Updated ", ""), "Chicago Time", ""))
End Sub

Function GetLastUpdate(Optional TimeOutSeconds As Double = 10) As String
    Dim ie As Object, doc As Object, b As Object
    Dim TimeOutTime As Date
    
    Set ie = CreateObject("InternetExplorer.Application")
    ie.navigate "http://www.cmegroup.com/clearport/product-slate.html#sortKey=1&sortOrder=ascending&SortType=text&FilterOn=1&PageStart=1"
    Do Until Not ie.Busy And ie.readyState >= 3: DoEvents: Loop
    
    TimeOutTime = DateAdd("n", TimeOutSeconds, Now)
    Do
        Set doc = ie.document
        For Each b In doc.getElementsByTagName("B")
            If InStr(b.innerText, "Last Updated") Then
                GetLastUpdate = b.innerText
                Exit Do
            End If
        Next
    Loop Until Now > TimeOutTime
    ie.Quit

End Function

 

[alexanderduder]

HELP ME, PLEASE.. =) Tom Schreiner

Tom Schreiner,

Can you help me with this one..

I'm trying to get the data from this site

http://datasuite.cmegroup.com/dataS....cmegroup.com/trading/fx/&selected_tab=fx_tab

maybe you know another link, because this link with real-time info and I want to see this info with changes in my excel, but above's link can't be accepted by excel, I need another link fot this, thank you very much, waiting your reply there, later