VBA Code to extract HTML Table data to worksheet

[jhurwitz]

I am new to VBA and having a very difficult time getting this code to work. I am trying to extract the data values from the references Bundesbank page and get them into a worksheet so I can manipulate from there. What I have is below, and I'm looking for help in the [bracketed areas].

Thanks

Sub Get_Data()

Dim IE As New InternetExplorer
IE.Visible = False
IE.navigate "http://www.bundesbank.de/Navigation/EN/Statistics/Time_series_databases/Macro_economic_time_series/its_details_value_node.html?nsc=true&listId=www_s201_b9233&tsId=" & "BBK01.ED0439"
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim BubaWebpage As HTMLDocument
Set BubaWebpage = IE.document
IE.Quit
Dim RawData As HTMLTable
Set RawData = BubaWebpage.getElementsByClassName("valueTable")(0)
ThisWorkbook.Sheets.Add After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet
ActiveSheet.Name = "BubaData"
Dim i As Long
Dim j As Long
i = 1 'rows
j = 1 'columns

    For i = 1 To [Help]
                For j = 1 To 6
                    Sheets("BubaData").Cells(i, j).Value = [SOMETHING LIKE RawData.Rows(i).Cells(j).innerText BUT THIS DOESNT WORK]
                    j = j + 1
                Next
                i = i + 1
    Next


MsgBox "Done"


End Sub

 

[anvg]

For example

    Dim hRow As HTMLTableRow
    Dim hCell As HTMLTableCell
    For Each hRow In RawData.Rows
        For Each hCell In hRow.Cells
            Debug.Print hCell.innerText
        Next hCell
    Next hRow

Or like an index manner

    RawData.rows.item(,0).cells.item(,0).innerText

 

[jhurwitz]

I get an error "Runtime error '438': Object doesn't support this property or method" on this line:

     For Each hRow In RawData.Rows

I have the following available references in the file:
Visual Basic for Applications
Microsoft Excel 14.0 Object library
OLE Automation
Microsoft Office 14.0 Object library
Microsoft Forms 2.0 Object library
Microsoft HTML Object library
Microsoft Internet Controls

 

[taurean]

As an alternative you can use
Data | Get External Data | From Web and then specify this URL.

Once you get the data in Excel then you can manipulate the way you want it to. You can record a macro for this if you want to. Recorded macro will look like this:

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.bundesbank.de/Navigation/EN/Statistics/Time_series_databases/Macro_economic_time_series/its_details_value_node.html?nsc=true&listId=www_s201_b9233&tsId=BBK01.ED0439" _
        , Destination:=Range("$A$1"))
        .Name = _
        "its_details_value_node.html?nsc=true&listId=www_s201_b9233&tsId=BBK01.ED0439"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    
End Sub

It can be tweaked further to suit needs.

 

[jhurwitz]

Got this working today using taurean's method. Many Thanks.

 

[anvg]

I am glad that you have used taurean's code. I looked at a source html-code of the link above. Class="valueTable" is used for DIV tag not for TABLE tag. What is why you have a mistake with For hRow In RawData.rows.
If it is still interesting for you you could use
Set RawData = hDoc.getElementsByClassName("valueTable")(0).getElementsByTagName("TABLE")(0)
for a work with that code.

 

[Kyle123]

I'd prefer: (tag name works since there's only 1 table in the page)

Sub test()
    Dim oDom As Object: Set oDom = CreateObject("htmlFile")
    Dim x As Long, y As Long
    Dim oRow As Object, oCell As Object
    Dim data
    
    y = 1: x = 1
    
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "Deutsche Bundesbank - Macro-economic time series detail view values", False
        .Send
        oDom.body.innerHtml = .responseText
    End With
    
    With oDom.getelementsbytagname("table")(0)
        ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length)
        For Each oRow In .Rows
            For Each oCell In oRow.Cells
                data(x, y) = oCell.innerText
                y = y + 1
            Next oCell
            y = 1
            x = x + 1
        Next oRow
    End With
    
    Sheets(1).Cells(1, 1).Resize(UBound(data), UBound(data, 2)).Value = data
End Sub

 

[jhurwitz]

Thanks guys. I have the macro working now with taurean's method, but do you think the latter two methods would run faster? Given the many web queries in the process, it takes about 10 minutes to run the whole macro.

 

[Kyle123]

My method is faster than a web query, that's why I use it so at a guess mine will be fastest (both mine and anvg's are the same really, it's just how to select an element), then Taurean's web query then your own - automating Internet Explorer is painfully slow

 

[jhurwitz]

Kyle - can you explain how your code works? Maybe a commented version if you have a minute? Is this via xml?
IE is surely slow, but I don't understand how your code retrieves the data from the web without using it. I would like to speed up the code if possible, but I'm really a novice here.

Thanks