CODEWITHSUNDEEP
vbaexcelinternet-explorerweb-scraping
RageAgainstheMachine
RageAgainstheMachine

Reputation: 949

Clicking button on website and downloading data file into Excel

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 ([email protected])

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!

Upvotes: 1

Views: 7937

Answers (2)

QHarr
QHarr

Reputation: 84465

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:

data

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:

Data sample

Sample source JSON:

Sample

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

data

Upvotes: 1

TheSilkCode
TheSilkCode

Reputation: 366

So this is what I would do:

Sub GetData()
Dim IE As InternetExplorer
Dim HTMLDoc As HTMLDocument
Dim objElement As HTMLObjectElement

Set IE = New InternetExplorer
With IE
    .Visible = True
    .Navigate "http://www.housepriceindex.ca/default.aspx"
    While .Busy Or .ReadyState <> READYSTATE_COMPLETE: Wend
    .Document.getElementById("lnkTelecharger2").Click
    While .Busy Or .ReadyState <> READYSTATE_COMPLETE: Wend
    Set HTMLDoc = .Document
    Set objElement = HTMLDoc.getElementById("txtEmailDisclaimerEN")
    objElement.Value = "Email Address"
    Set objElement = HTMLDoc.getElementById("lnkAcceptDisclaimerEN")        
    objElement.Click

    ' ... Get CSV somehow ...

    .Quit
End With

Set IE = Nothing
End Sub

Now obviously that still leaves you with the issue of capturing the CSV- I tried a number of different ways to set a workbook variable to the CSV with no luck... the issue is that the button that you click doesn't contain the URL for the CSV you are going to be downloading, so unless someone else knows how to capture a file that's being prompted to open or save this is as far as I can help you..

Regards, TheSilkRoad

Upvotes: 1

Related Questions