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 · Answer 1 · 2016-06-02T07:15:34.423

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 02 '16 at 07:15

answered Jun 01 '16 at 11:12

Mrig

11,612
2
13
27

@R.Michael - I am getting result properly, check the format of your cell range. – Mrig Jun 01 '16 at 11:22
i did new sheet, add still not working. What do you mean by cell range exactly – R. Michael Jun 01 '16 at 11:33
@R.Michael - By saying cell range I mean the range where your data is being copied. – Mrig Jun 01 '16 at 11:37
@R.Michael - Will you be able to share your sheet so that I can look into it. – Mrig Jun 01 '16 at 11:38
its same like yours Mrig :/ – R. Michael Jun 01 '16 at 11:43
@R.Michael - Not the code, I would like to see the sheet for formatting. – Mrig Jun 01 '16 at 11:51
@R.Michael - One more thing, do not post anything as answer unless it really is an answer. – Mrig Jun 01 '16 at 11:51
Yeah but i cannot post code because here is characters limit :/ – R. Michael Jun 01 '16 at 11:56
Ok how i can show u that? because i dont realy know what u asking for xd? – R. Michael Jun 01 '16 at 12:00
@R.Michael - You can share your file online and put the link here. – Mrig Jun 01 '16 at 12:03
@R.Michael - Okay! – Mrig Jun 01 '16 at 12:22
@R.Michael - Not sure why it's not working at your end. Let me see if I can get it to work using some other method. – Mrig Jun 01 '16 at 12:48
@R.Michael - Your shared file working fine for me. Anyhow, Great that you solved your problem. – Mrig Jun 01 '16 at 12:56
@R.Michael - Delete the other code that you posted as an answer. (the one that's not working) – Mrig Jun 01 '16 at 12:57
I just make bigger delay ( i think it's delay: Application.Wait Now + TimeSerial(0, 0, 5) ) and working ^^. Probably bad connection. – R. Michael Jun 01 '16 at 13:21
i tried with other page and not working aswell could u make some descriptions under each line i have to edit ? :P – R. Michael Jun 01 '16 at 16:25
i think, it's scrap to fast. :p because sometimes when connection is good i receive full data. – R. Michael Jun 02 '16 at 08:02
@R.Michael - Then it's better to increase the time here `Application.Wait Now + TimeSerial(0, 0, 2)` – Mrig Jun 02 '16 at 08:04
@R.Michael - If my answer solved your issue here kindly mark it as an answer. – Mrig Jun 02 '16 at 08:10
@Mrig, Thank you soo much Mrig, your code was perfect and helped me a lot. Thanks once again. I gave up vote to this awesome code. – TCritical Aug 28 '20 at 13:40
@Mrig, how i can copy multiple pages data after every last blank row with your code. could you please suggest me on that. Ex: in your Image 7th is the last row and i want to copy 2nd page data in 8th row, 3rd page data in nth last blank row and so on.. – TCritical Jul 01 '21 at 01:03

How to scrape table data in Excel/VBA?

1 Answers1