单击网站上的按钮并将数据文件下载到 Excel [英] Clicking button on website and downloading data file into Excel

问题描述

我正在尝试从网站下载数据并将其放入我的 excel 文件中.

以下是我要执行的操作的步骤:

1) 访问网站:

您可以将其与

<小时>

示例源 JSON:

在顶级字典 ("JSON") 键 "data"C11 信息>.

I'm trying to download data from a website and put it into my excel file.

Here are the steps to what I'm trying to do:

1) go to website: http://www.housepriceindex.ca/default.aspx

2) click on "Download Historical Data (.xls)" (I'm stuck here)

3)input email address at bottom ([e-mail])

4)click accept

5)transfer the data from the just downloaded .xls file to my file.

Here is the code so far:

Sub GetData()


Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

Set IE = CreateObject("InternetExplorer.Application")

IE.Visible = True
IE.Navigate "http://www.housepriceindex.ca/default.aspx" 

Do While IE.Busy: DoEvents: Loop
Do Until IE.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

IE.Document.GetElementByID(lnkTelecharger2).Click


End Sub

Any help is appreciated!

解决方案

You can target the button by its classname (button small download) :

IE.document.querySelector(".button.small.download").Click

or

IE.document.getElementsByClassName("button small download")(0).Click

---

BUT:

If you know your way around JSON you can avoid login completely. There is a JSON link above the button you are seeking to click:

data-data_url:

You can use that with an XMLHTTPRequest to grab the JSON data and then parse the response with a tool such as JSONConverter.. After you have added the .bas to your project you need to go VBE>Tools>References and add a reference to Microsoft Scripting Runtime.

Here is just an outline showing the process of setting the initial JSON object and extracting some info.

Option Explicit
Public Sub GetInfo()
    Dim strURL As String, strJSON As String, Http As Object, json As Object
    Application.ScreenUpdating = False
    strURL = "https://housepriceindex.ca/_data/indx_data.json?d=4dfb05da"

    Set Http = CreateObject("MSXML2.XMLHTTP")
    With Http
        .Open "GET", strURL, False
        .send
        strJSON = .responseText
    End With
    Set json = JsonConverter.ParseJson(strJSON)

    Dim key As Variant, dictKeys As Variant
    '****************************************
    ' Set json = json("data") ' Array("indx", "spc", "indx_ch", "spc_ch", "Meta", "Data") '<== These are the keys in that dict.

    Set json = json("profiles")                  ' Array("c11", "mc","ab_calgary","ab_edmonton","bc_abbotsford","bc_kelowna" , _
                                                 "bc_vancouver","bc_victoria","mb_winnipeg","ns_halifax","on_barrie" , _
                                                 "on_brantford","on_guelph","on_hamilton","on_kingston","on_kitchener", _
                                                 "on_london","on_oshawa","on_ottawa","on_peterborough","on_st_catharines" , _
                                                 "on_sudbury","on_thunder_bay","on_toronto","on_windsor","qc_montreal","qc_quebec_city") '<==Keys in profile dict

    Dim dict As Object, rowNumber As Long
    Set dict = json("qc_montreal")

    With ThisWorkbook.Worksheets("Sheet1")
        For Each key In dict
             rowNumber = rowNumber + 1
            .Cells(rowNumber, 1) = key
            .Cells(rowNumber, 2) = dict(key)
        Next key
    End With
    Application.ScreenUpdating = True
End Sub

---

Sample sheet output:

---

Sample source JSON:

There is a lot of C11 info in the second level dictionary housed under the top level dictionary ("JSON") key "data".

这篇关于单击网站上的按钮并将数据文件下载到 Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！