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

 

[md_iffu]

Hi Kyle,

I have opened a new thread:

http://www.mrexcel.com/forum/excel-...sword-protected-excel-2013-a.html#post4179889

 

[tahirsatti]

Sir..Mr taurean,
You code is very nice and working for me.Now is this possible that i will be able to extract only lowest offer price,condition and seller information.Present code is extracting complete data which is very time consuming.Please help.
Thanks.

 

[tahirsatti]

Sir..Mr taurean,
You code is very nice and working for me.Now is this possible that i will be able to extract only lowest offer price,condition and seller information.Present code is extracting complete data which is very time consuming.Please help.
Thanks.

 

[taurean]

Tahir,
That was the idea. Fetch all data in Excel and then get the information you need.
Regards,

 

[slipandfall99]

Kyle123, I like your approach to extracting data off of websites but am having an issue with it on yahoo finance website. I'm trying to extract the table data from GOOG Historical Prices | Alphabet Inc. Stock - Yahoo! Finance
using your approach but get Run-Time Error '91': object variable or with block variable not set on line ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length). Can you review this and see if you can see why this error may be getting thrown?

 

[VBABEGINER]

Hi Taurean,

I am exactly looking for the same code which are displayed here in the post. Only there some parameters are change in my case.

I have put 02 new post on board from last 2 weeks, but no one replies me or unable assist.

I request you, please try to provide me some code which solve my problem.

I have one excel sheet, which contains some unique number in col A.
Also, I have one website, from where I have to find out the details against the each cell value.
this is the website - Air cargo tracking - track-trace

when you will open the website, you can find one text box wherein, I have to enter my number from excel sheet. and besides to the that there is button named "Track with option".

My work is like, copy the cell value from excel and paste it into in text box and then click on button. After clicking on the button, we will get the details about the shipment code on the next web page.

Here, this is the next web page image.
Sr no. 1 is the excel value, copying and pasting in the text box and then clicking the button will jump on this page.
Sr no. 2 is the location which i need to trace out whether the shipment is arrived or not.
From, Sr No. 3 and Sr No. 4, I get to understand that the shipment are received are not.
Therefore, this are the two main points I have to copy and paste it into an excel sheet.

I request you, could you pls help me out for the code please. Bcoz I a, not aware about how to play with website download code.

Thanks in advance.

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.

 

[BoyDrone]

I know this is an old thread, and this may be a long shot, but I have a question for @Kyle123

I'm a long time user of excel and I have a project I'm working on, but cant seem to get over pulling data from a website through VBA. Havent touched code for almost 10 years now.
I'm able to use a VBA macro to pull data off of the site, but the table pull request has been challenging. Regarding your post here:

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

if I simply copy/Paste this into a macro in VBA, I get a debug error:
"Object variable or With block variable not set"
and when I click on Debug, the following line is highlighted

ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length)

Do I need to activate a reference tool to make this work?

I liked your variation of the tool since it was so fast compared to other variations. I have A LOT of data to scrape from the internet, so I need something thats relatively efficient.

Hoping for your response!

 

[Kyle123]

It means that there are no tables on the resulting webpage

 

[BoyDrone]

It means that there are no tables on the resulting webpage

Thanks for responding Kyle!
well, I guess what I'm having trouble learning is what DOM class (i think thats the right term), to use to extract the correct table.

Private Sub CommandButton2_Click()

    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", "http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=GNTX&region=USA&culture=en-CA&ops=clear", False
        .send
        oDom.body.innerHTML = .responseText
    End With
    
    With oDom.getElementsByTagName("r_table1")(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

Thats what I'm working on, and I think its r_table1 or r_table1.text2 that I'm supposed to grab, but its not pulling anything.

Any direction would be VERY helpful.

 

[Kyle123]

The table isn't in the html, this isn't simply a case of tweaking the code. You'll need to start your own thread, this requires a completely different solution