.iqy syntax

[Mark_Leh]

I am having a general problem in getting data into Excel from the web. There is something wrong either with my syntax or my understanding of the HTML Page Source (quite possible since I have but a cursory knowledge of HTML). For example, here is a sample iqy file:

WEB
1
http://www.bts.gov/xml/air_traffic/src/datadisp.xml
category=1&service=1&scheduleservice=1&comparison=total_rpms&stmm=01&stmm=1996&edmm=02&edyyyy=2011
Selection=AllTables
Formatting=RTF
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False


I do not understand why this .iqy file does not return the same table to Excel that the web site returns when the parameters are chosen there.

Any help would be warmly appreciated. Thanks in advance.

 

[xenou]

This might be a shot in the dark but try a question mark between the end of the site address and the rest of the parameters:

http://www.bts.gov/xml/air_traffic/src/datadisp.xml[B][COLOR="#FF0000"]?[/COLOR][/B]category=1&service=1&scheduleservice=1&comparison=total_rpms&stmm=01&stmm=1996&edmm=02&edyyyy=2011

 

[Mark_Leh]

Thanks, but that doesn't work. I think it doesn't work because the method of the form on the website is POST not GET.

Incidentally I violated the posting rules earlier by forgetting to provide the following info: I am using Excel 2010 on Windows 7. Sorry for the oversight.

 

[xenou]

Yes, I think you are right. You can't send the parameters in the URL - you have to post the form using the form's submit button. I've only done this kind of thing rarely. You might like to give Norie a Private Message with a link to this post - he's generally very good at this kind of thing.

 

[Kyle123]

iqy's will handle POST data without issue, in your case however, the issue is that your parameters are incorrect, you've listed the start month twice rather than the start month and start year:

category=1&service=1&scheduleservice=1&comparison=total_rpms&stmm=01&stmm=1996&edmm=02&edyyyy=2011

Should be:

category=1&service=1&scheduleservice=1&comparison=total_rpms&stmm=01&styyyy=1996&edmm=02&edyyyy=2011

Quick check in code:

Sub AddTable()

With Sheet1.QueryTables.Add("url;http://www.bts.gov/xml/air_traffic/src/datadisp.xml", Range("a1"))
    .PostText = "category=1&service=1&scheduleservice=1&comparison=total_rpms&stmm=01&styyyy=1996&edmm=02&edyyyy=2011"
    .WebFormatting = xlWebFormattingNone
    .EnableRefresh = True
    .Refresh
End With
End Sub

 

[xenou]

Thanks Kyle - nice!

 

[Kyle123]

Glad I could help

I've never really understood the need for .iqy files but it seems that they have a use - non VBA "POST" query tables

 

[Mark_Leh]

Thanks!

Well, that was a really stupid mistake on my part. Your repaired code worked great.

I think there are more substantive issues with .iqy files that I still don't understand, so I hope that you don't mind if I ask some more questions over the next few days. I'll try to compose my thoughts and point out sample websites where I've encountered roadblocks.

 

[Mark_Leh]

Kyle, is it possible to feed parameters to a Javascript on a web page in a iqy file? For example, look at American FactFinder - Search . Is it possible to feed one of the choices (or, better still, prompt the user for a choice) on this page? In other words, is it possible to feed the Javascript EC0752SSSZ1 the way it would be on a simpler non-Javascript form and then have the iqy file do the formatting for the table in Excel? I can't even figure out where to begin on this.

Thanks for your help.

 

[Kyle123]

That's more difficult. It's not possible with an iqy file, it's doable with code. The reason is that a webquery will only parse the html of the source page and the table isn't in the source, it's populated from a JSON object sent by the server - you can see the results of a search for "town" here - http://factfinder2.census.gov/rest/...Index=0&results=25&&Ntt=town&_ts=369807064735

Javascript will then take this data and create a table from it, none of which the webquery will see, it will just see an empty table. You wouldn't pass a parameter to the JavaScript instead you'd just send the same request to the server that the JavaScript does.

Now it's possible in Excel with code to parse the JSON object and display the results on a worksheet. However there's an added complication, the JSON object only contains a portion of the rows for the entire dataset, so if the search spans more than one page of results, you have to do multiple calls, incrementing the URL to return the next block of data. This will then need appending to the data returned from the previous calls. You'd then need to reconstruct the hyperlinks if you want to be able to click on them

So, you can send a request from Excel, return the data and display in a table, but not with an .iqy file, you'd need to use VBA code.

EDIT Having had a play around with the URL you can set the results number very high, so this should be doable in a single call