.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.

 

[Kyle123]

If VBA is acceptable, here's a starter for 10:

Option Explicit


Sub GetJson()




    Dim JsonString      As String
    Dim objJson         As Object
    Dim objSE           As Object
    Dim TopLeftCell     As Range
    Dim SearchTerm      As String
    Dim x               As Long
    
    'Set The search Term
    SearchTerm = "town"
    
    'set the top left cell of our results table
    Set TopLeftCell = Sheets("sheet1").Range("B5")


    'Clear the table if one exists
    TopLeftCell.CurrentRegion.ClearContents
    
    'Create the control that will do our JSON parsing
    Set objSE = CreateObject("ScriptControl")


    'Add Functions to do the JSON parsing to the created control
    With objSE
        .Language = "JScript"
        'This will get the details from the JSON object in the Results, for a given key
        .AddCode "function getProp(jsonObj, id, propertyName) { return jsonObj.AllRecords.SearchRecords.Records.Results[id][propertyName]; } "
        'This will return the total results returned
        .AddCode "function getRecords(jsonObj) { return jsonObj.AllRecords.SearchRecords.Records.Results.length;}"
        'This will URL Decode the strings returned so that they are readable in Excel
        .AddCode "function urlDecode(str) {return decodeURIComponent(str); }"
    End With


    'Create the object to send the Web Request and Send it
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "http://factfinder2.census.gov/rest/topicsNav/nav?N=0&Ne=&startIndex=0&results=1000&&Ntt=" & SearchTerm, False
        .send
        JsonString = .responsetext
    End With
    
    'Convert the returned JSON string into a JS object
    Set objJson = objSE.Eval("(" + JsonString + ")")
    
    With TopLeftCell
        'Loop from 0 to the number of records returned
        For x = 0 To Val(objSE.Run("getRecords", objJson)) - 1
            'Add the Product Code to the First Column
            .Offset(x, 0).Value = objSE.Run("getProp", objJson, x, "p_product_code")
            'Add a hyperlink to the second column that links to the data
            .Hyperlinks.Add .Offset(x, 1), _
                            "http://factfinder2.census.gov/faces/tableservices/jsf/pages/productview.xhtml?pid=" & objSE.Run("getProp", objJson, x, "p_product_key") & _
                            "&prodType=" & objSE.Run("getProp", objJson, x, "d_record_subtype"), _
                            TextToDisplay:=Replace(objSE.Run("urlDecode", objSE.Run("getProp", objJson, x, "p_record_name")), "+", " ")
            'Add the dataset property to the 3rd column
            .Offset(x, 2).Value = Replace(objSE.Run("urlDecode", objSE.Run("getProp", objJson, x, "d_dataset")), "+", " ")
        Next x 'Next Record
    End With


    'Resize the columns to fit the data
    TopLeftCell.Resize(, 3).Columns.EntireColumn.AutoFit
    
End Sub

 

[Mark_Leh]

This is great. Thanks so much! I think I'm beginning to understand the issues.

Let me noodle around with this for a while and see if this will do everything I need.

I really appreciate your helping me on this. I was just totally lost. Your ideas let me jump over half a dozen stumbling blocks.

 

[Kyle123]

No problem, when I made the above I noticed that it's faster than actually searching via the website