Results 1 to 5 of 5

Thread: How to distinguish data from html

  1. #1
    VBAX Regular
    Mar 2024

    How to distinguish data from html

    Hi sirs
    i want get date from this website "",but it too many branch at frame.
    i try to change low & column, it not correct put in excel table
    how to analyze it and if there are three same name table like "table-body-wrapper", if i want to get no.3 how to use getElementsByClassName to get
    attach file is all code, thanks


    Sub Hi_stock()
        'Sheets.Add(After:=ActiveSheet).Name = "Dividend"
        'Dim HTTPRequest As MSXML2.XMLHTTP
        Dim httpRequest As Object
        Dim htmlDoc As Object
        Dim tables As Variant
        Dim name_get As Variant
        Dim dividend As Variant
        Dim dividend_1 As Variant
        Dim colNum As Integer
        Dim rowNum As Integer
        Set httpRequest = CreateObject("MSXML2.XMLHTTP")
        Set htmlDoc = CreateObject("htmlfile")
        url_1 = ""
        httpRequest.Open "GET", url_1, False
        'Check if the request was successful (status code 200)
        If httpRequest.Status = 200 Then
            'Create a new HTML document object
            htmlDoc.body.innerHTML = httpRequest.responseText
            'get stock name
            Set name_get = htmlDoc.getElementsByTagName("div")
            For Each tbl In name_get
                If tbl.className = "D(f) Ai(c) Mb(6px)" Then
                    For Each tblCol In tbl.getElementsByTagName("h1")
                        Cells(1, 1).Value = tblCol.innerText
                        Debug.Print (tblCol.innerText)
                    Next tblCol
                End If
            Next tbl
            'get dividend title
            Set dividend = htmlDoc.getElementsByClassName("table-header Ovx(s) Ovy(h) W(100%)")
            For Each tb_1 In dividend
                rowNum = 2
                For Each tb_1_1 In tb_1.getElementsByTagName("div")
                    colNum = 1
                    For Each tb_1_2 In tb_1_1.getElementsByTagName("div")
                        Cells(rowNum, colNum).Value = tb_1_2.innerText
                        colNum = colNum + 1
                    Next tb_1_2
                Next tb_1_1
                Exit For
            Next tb_1
            'get dividend data
            Set dividend = htmlDoc.getElementsByClassName("table-body-wrapper")
            For Each tb_2 In dividend
                'rowNum = 3
                For Each tb_2_1 In tb_2.getElementsByTagName("ul")
                    rowNum = 3
                    'colNum = 1
                    For Each tb_2_2 In tb_2_1.getElementsByTagName("li")
                        colNum = 1
                        For Each tb_2_3 In tb_2_2.getElementsByTagName("div")
                            Cells(rowNum, colNum).Value = tb_2_3.innerText
                            colNum = colNum + 1
                        Next tb_2_3
                        rowNum = rowNum + 1
                    Next tb_2_2
                Next tb_2_1
                Exit For
            Next tb_2
        End If
    End Sub
    Attached Files Attached Files

  2. #2
    Maybe using Power Query and the URL would be a better option

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Oct 2005
    Surrey UK
    Quote Originally Posted by jdelano View Post
    Maybe using Power Query and the URL would be a better option
    I think so too; I got this into Excel in under 10 minutes with Power Query with NO coding, no writing of M-code or anything, just using the user interface:


    xyz987 seems to be hellbent on using other means and/or has an aversion to Power Query as he has turned down similar suggestions in the past:
    Last edited by p45cal; 04-16-2024 at 03:44 AM.
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Regular
    Mar 2024
    Hi p45cal ,
    thanks for your help, and sorry maybe let you feel i don`t like your answer, any tooling is okay, i just like to practice code skill, so i try to use vba to slove. even sometimes no body answer me,

  5. #5
    VBAX Regular
    Mar 2024

    2 method vba to solve

    Hi, body: already to solve 1. yahoo support api ( need change web view form history to dividend will show at develop mode )
    2. html mode, and calculate the formula


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts