Trouble with Web Query in Excel

Question

I´m having some trouble with two different web queries in Excel.

1) Website: http://www.danielsoper.com/statcalc3/calc.aspx?id=44

Trouble: I can´t seem to obtain the result value of the query. Basically, I´m inputting the parameters in an excel spreadsheet and I want the result. I don´t know if is related, but the result only exists after you press calculate. Seems easy, but I having some trouble...

2) Website: http://www.iea.org/statistics/statisticssearch/report/?&country=USA&year=2011&product=Balances

Trouble: I believe that maybe this one is not related to the query itself, since I do get data, but the results seems to be encrypted.. I simply can´t tell if it is something I'm doing wrong or if there is some way to overcome this.

From my perspective, even though the first one SEEMS to be easier to fix, the second one would be extremely helpful for my research.

I appreciate any help you can give me.

Thanks,

Answer 1

For the iea, they want you to subscribe to their 'online data services' so they encode all of the search result data.

For danielsoper, the results are generated via ASP, so you won't be able to get them form a web query. You can get them by using VBA and MSXML, but you'd be better off just replicating the algorithm in VBA or fining a different website.

Answer 2

The following code works for me at your first website. Place the inputs in cells "A1" and "A2" and the results are placed in "A3" and "A4". Modify to fit your worksheet.

' Open IE, navigate to the website of interest and loop until fully loaded
Set ie = CreateObject("InternetExplorer.Application")

With ie
    .Visible = True
    .navigate "http://www.danielsoper.com/statcalc3/calc.aspx?id=44"
    .Top = 50
    .Left = 530
    .Height = 400
    .Width = 400

Do Until Not ie.Busy And ie.ReadyState = 4
    DoEvents
Loop

End With

' Insert data from cells "A1" and "A2" into the webpage and click "Calculate!"
ie.Document.getElementById("pageContent_gridParameters_txtParameterValue_0").Value = Range("A1")
ie.Document.getElementById("pageContent_gridParameters_txtParameterValue_1").Value = Range("A2")
ie.Document.getElementById("pageContent_btnCalc").Click

' Collect the results and place them on the activesheet
my_var = ie.Document.body.innerhtml

pos_1 = InStr(1, my_var, "Result_0", vbTextCompare)
pos_2 = InStr(pos_1, my_var, ">", vbTextCompare)
pos_3 = InStr(pos_1, my_var, "<", vbTextCompare)
One_Tailed = Mid(my_var, 1 + pos_2, pos_3 - (1 + pos_2))

pos_4 = InStr(pos_3, my_var, "Result_1", vbTextCompare)
pos_5 = InStr(pos_4, my_var, ">", vbTextCompare)
pos_6 = InStr(pos_5, my_var, "<", vbTextCompare)
Two_Tailed = Mid(my_var, 1 + pos_5, pos_6 - (1 + pos_5))

Range("A3") = One_Tailed
Range("A4") = Two_Tailed