Web Query - Select Table not being captured

C

CodyMonster

Board Regular
Joined
Sep 28, 2009
Messages
159
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Hi Cody. I don't think that you are going to be able to access the table using Excel's query because the it is loaded dynamically after the page is loaded. What exactly do you need? "All"? How often will you be checking this site?
 
Upvote 0
Here is the GET information. You might be able to query directly. Try this URL for you query...

Code: 
http://www.cmegroup.com/CmeWS/mvc/xsltTransformer.do?xlstDoc=/XSLT/md/CPCslate.xsl&file=/www/data/incoming/slate/slate.xml&sortKey=1&sortOrder=ascending&SortType=text&FilterOn=1&PageStart=1&Time=Thu Jan 28 19:23:04 2010
 
Upvote 0
That's fantastic, can I ask one more question..
How did you figure this out?

Trying to avoid having to come back to the well every time I have a similar problem..

Any insight would be appreciated....
 
Upvote 0
Ok, don't think I can justify buying a software package for one thing that might or might not happen.

Just hope the well stays full, because I'm might have to come back...

Thanks again, this really was a huge help!!!
 
Upvote 0
Hi Cody. Record a macro while creating your webquery and update the URL string with the current date and time.

URL = "http://www.cmegroup.com/CmeWS/mvc/xsltTransformer.do?xlstDoc=/XSLT/md/CPCslate.xsl&file=/www/data/incoming/slate/slate.xml&sortKey=1&sortOrder=ascending&SortType=text&FilterOn=1&PageStart=1&Time=" & Format(Now, "DDD MMM DD HH:MM:SS YYYY")

Here is an example:

Code: 
Sub Macro1()
    Dim URL As String

    URL = "http://www.cmegroup.com/CmeWS/mvc/xsltTransformer.do?xlstDoc=/XSLT/md/CPCslate.xsl&" & _
        "file=/www/data/incoming/slate/slate.xml&sortKey=1&sortOrder=ascending&SortType=text&FilterOn=1&" & _
        "PageStart=1&Time=" & Format(Now, "DDD MMM DD HH:MM:SS YYYY")
    
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("A1"))
        .WebFormatting = xlWebFormattingNone ' xlWebFormattingAll
        .WebTables = """dyn_slateTable"""
        .Refresh False
        .Delete
    End With
    
End Sub
 
Upvote 0
That would work if it would provide historical data..
But it doesn't.. if you change the data after Time=
It just gives the current data and the data doesn't change.

Looks like I will need to download this daily to get a time series. Not a big deal...I'm sure I can schedule a routine. I was wanting to get the date from the same page instead of doing a manual input. But, just like the data table, you can't get this information by just using the page URL and doing a web query. You need the specific page URL, like the data table.

So to get the date for the data table, I need the specific URL...

Make sense?

Tom Schreiner said:
Hi Cody. Record a macro while creating your webquery and update the URL string with the current date and time.

URL = "http://www.cmegroup.com/CmeWS/mvc/xsltTransformer.do?xlstDoc=/XSLT/md/CPCslate.xsl&file=/www/data/incoming/slate/slate.xml&sortKey=1&sortOrder=ascending&SortType=text&FilterOn=1&PageStart=1&Time=" & Format(Now, "DDD MMM DD HH:MM:SS YYYY")

Here is an example:

Code: 
Sub Macro1()
    Dim URL As String

    URL = "http://www.cmegroup.com/CmeWS/mvc/xsltTransformer.do?xlstDoc=/XSLT/md/CPCslate.xsl&" & _
        "file=/www/data/incoming/slate/slate.xml&sortKey=1&sortOrder=ascending&SortType=text&FilterOn=1&" & _
        "PageStart=1&Time=" & Format(Now, "DDD MMM DD HH:MM:SS YYYY")
    
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("A1"))
        .WebFormatting = xlWebFormattingNone ' xlWebFormattingAll
        .WebTables = """dyn_slateTable"""
        .Refresh False
        .Delete
    End With
    
End Sub
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
create a report summary page by group
Replies
4
Views
199
PippaThePointer
P
K
  • Question Question
Import Data from Multiple Web Pages
Replies
0
Views
235
Ka001
K
B
  • Question Question
GETPIVOTDATA Sum of multiple results
Replies
5
Views
321
BlitzTwig
B
Y
  • Question Question
Pivot Table deletes on opening of workbook
Replies
3
Views
221
cooleomter
C
T
  • Question Question
VBA to run when a table with a SQL query is refreshed?
Replies
0
Views
173
Tarver
T

Forum statistics

Threads
1,223,713
Messages
6,174,043
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top