How to scrape table data in Excel/VBA?

Question

The page is coded in html5 and my code cannot scrape with this method:

Sub Macro1()
 With ActiveSheet.QueryTables.Add(Connection:= _
 “URL;https://exchange.btcc.com/”, Destination:=Range(“$A$1″))
 .Name = “market_trend”
 .FieldNames = True
 .RowNumbers = False
 .FillAdjacentFormulas = False
 .PreserveFormatting = True
 .RefreshOnFileOpen = False
 .BackgroundQuery = True
 .RefreshStyle = xlInsertDeleteCells
 .SavePassword = False
 .SaveData = True
 .AdjustColumnWidth = True
 .RefreshPeriod = 0
 .WebSelectionType = xlSpecifiedTables
 .WebFormatting = xlWebFormattingNone
 .WebTables = “4″
 .WebPreFormattedTextToColumns = True
 .WebConsecutiveDelimitersAsOne = True
 .WebSingleBlockTextImport = False
 .WebDisableDateRecognition = False
 .WebDisableRedirections = False
 .Refresh BackgroundQuery:=False
 End With`
 End Sub

Any idea how to scrape Price Ticker table from this site ?

Mrig · Accepted Answer · 2016-06-02 07:15:34Z

1

Why not use the same method as in your last question:

Sub Tester()

    Dim IE As Object
    Dim tbl, trs, tr, tds, td, r, c

    Set IE = CreateObject("internetexplorer.application")

    IE.Navigate "https://exchange.btcc.com/"

    Do While IE.Busy = True Or IE.readyState <> 4: DoEvents: Loop

    Set tbl = IE.Document.getElementsByTagName("table")(5)
    Set trs = tbl.getElementsByTagName("tr")

    For r = 0 To trs.Length - 1
        Set tds = trs(r).getElementsByTagName("td")
        If tds.Length = 0 Then Set tds = trs(r).getElementsByTagName("th")

        For c = 0 To tds.Length - 1
            ActiveSheet.Range("A1").Offset(r, c).Value = tds(c).innerText
        Next c
    Next r

End Sub

This will give you result as follows:

edited Jun 2, 2016 at 7:15

answered Jun 1, 2016 at 11:12

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

21 Comments

Mrig Over a year ago

@R.Michael - I am getting result properly, check the format of your cell range.

R. Michael Over a year ago

i did new sheet, add still not working. What do you mean by cell range exactly

Mrig Over a year ago

@R.Michael - By saying cell range I mean the range where your data is being copied.

Mrig Over a year ago

@R.Michael - Will you be able to share your sheet so that I can look into it.

R. Michael Over a year ago

its same like yours Mrig :/

|

Collectives™ on Stack Overflow

How to scrape table data in Excel/VBA?

1 Answer 1

21 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

21 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related