none
[SOLVED] Help for extract content of class tag from web RRS feed

  • Question

  • hi guys i need your help, i need to adapt this code

    Dim IE As New InternetExplorer
    IE.navigate "Site address"
    IE.Visible = True
    Do
    DoEvents
    Loop Until IE.readyState = READYSTATE_COMPLETE
    Application.Wait (Now() + TimeValue("00:00:016")) ' For internal page refresh or loading
    Dim doc As HTMLDocument 'variable for document or data which need to be extracted out of webpage
    Set doc = IE.document
    Dim dd As Variant
    dd = doc.getElementsByClassName("class name of HTML ")(0).innerText 
    MsgBox dd

    found in this discussion https://social.msdn.microsoft.com/Forums/en-US/ee288a3a-1768-4c0c-bd34-16a64e50e5c6/get-content-of-class-tag-in-webpage-using-vba?forum=isvvba, what i've to do is extract the contenent of a class,and this code work, but my class is used in all the page so i've got a lot of valours and i want to see them printed on my Excel document, i also want that after a bit of time this macro restart and print the new valours under the first ones(if it's possible i want to see printed also date and time) someone can help me?



    • Edited by urealm Thursday, October 8, 2015 5:04 PM
    Wednesday, October 7, 2015 4:01 PM

All replies

  • You can loop like this

    Sub GetIEValues()
        Dim IE As InternetExplorer
        Dim dd As Variant
        
        
        Set IE = CreateObject("InternetExplorer.Application")
        
        With IE
            .Visible = True
            .Navigate "Site address"
            
            Application.Wait (Now + TimeValue("0:00:016"))
            
            Do Until .ReadyState = READYSTATE_COMPLETE
                DoEvents
            Loop
            
            dd = .Document.getElementsByClassName("class name of HTML ")(0).innerText
        End With
        
        With ThisWorkbook.Worksheets(1)
            lRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
            .Cells(lRow, "A").Value = Now()
            .Cells(lRow, "B").Value = dd
        End With
        
        Application.OnTime Now() + TimeValue("0:30:00"), "GetIEValues"  'Run again in 30 minutes
        
    End Sub


    Wednesday, October 7, 2015 4:45 PM
  • Thanks man but unfortunately doesn't work :( it just print ######## on 2b and when i duoble click it show me date and time, how we can fix this?

    EDIT if i change the number after class name it print also a valor, the first on website with this class and after the time it said to me it can't execute it again, suggestions?


    • Edited by urealm Wednesday, October 7, 2015 5:27 PM
    Wednesday, October 7, 2015 5:19 PM
  • Add the line

    IE.Quit

    as the last line

    Wednesday, October 7, 2015 6:01 PM
  • ok now i can execute this again but i still got printed just the first value of the html class

    exemple: my class is numbers, in the site are 2 3 4 5 6 7 all with numbers class but the macro print just the numer 2, how to solve it?

    Wednesday, October 7, 2015 7:00 PM
  •                                      

     Maybe you need to increment through the class...

    Sub GetIEValues()
        Dim IE As InternetExplorer
        Dim dd As Variant

        Set IE = CreateObject("InternetExplorer.Application")

        With IE
            .Visible = True
            .Navigate "Site address"

            Application.Wait (Now + TimeValue("0:00:016"))

            Do Until .ReadyState = READYSTATE_COMPLETE
                DoEvents
            Loop

            With ThisWorkbook.Worksheets(1)
                lRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
                .Cells(lRow, "A").Value = Now()
                .Cells(lRow, "B").Value = .Document.getElementsByClassName("class name of HTML ")(0).innerText
                .Cells(lRow, "C").Value = .Document.getElementsByClassName("class name of HTML ")(1).innerText
                .Cells(lRow, "D").Value = .Document.getElementsByClassName("class name of HTML ")(2).innerText
                .Cells(lRow, "E").Value = .Document.getElementsByClassName("class name of HTML ")(3).innerText
                .Cells(lRow, "F").Value = .Document.getElementsByClassName("class name of HTML ")(4).innerText
            End With
             .Quit
        End With

        Application.OnTime Now() + TimeValue("0:30:00"), "GetIEValues"  'Run again in 30 minutes

    End Sub

    Wednesday, October 7, 2015 10:48 PM
  • it say to me run time error 438 and highlight .Cells(lRow, "B").Value = .Document.getElementsByClassName("class name of HTML ")(0).innerText of course after i've changed with my html class
    Thursday, October 8, 2015 10:33 AM
  • Ooops - I nested my With statements incorrectly (sorry about that).  Try this



    Sub GetIEValues()
        Dim IE As InternetExplorer
        Dim dd As Variant

        Set IE = CreateObject("InternetExplorer.Application")

        IE.Visible = True
        IE.Navigate "Site address"

        Application.Wait (Now + TimeValue("0:00:016"))

        Do Until IE.ReadyState = READYSTATE_COMPLETE
            DoEvents
        Loop

        With ThisWorkbook.Worksheets(1)
            lRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
            .Cells(lRow, "A").Value = Now()
            .Cells(lRow, "B").Value = IE.Document.getElementsByClassName("class name of HTML ")(0).innerText
            .Cells(lRow, "C").Value = IE.Document.getElementsByClassName("class name of HTML ")(1).innerText
            .Cells(lRow, "D").Value = IE.Document.getElementsByClassName("class name of HTML ")(2).innerText
            .Cells(lRow, "E").Value = IE.Document.getElementsByClassName("class name of HTML ")(3).innerText
            .Cells(lRow, "F").Value = IE.Document.getElementsByClassName("class name of HTML ")(4).innerText
        End With
        
        IE.Quit

        Application.OnTime Now() + TimeValue("0:30:00"), "GetIEValues"  'Run again in 30 minutes

    End Sub


    • Proposed as answer by Rajesh123raj Friday, October 18, 2019 6:21 AM
    • Unproposed as answer by Rajesh123raj Friday, October 18, 2019 6:22 AM
    Thursday, October 8, 2015 2:04 PM
  • ok now it works :D my last question (i know that you hate me xD) is how i can print some valors into this coloumns but in different lines?
    Thursday, October 8, 2015 2:22 PM
  • If you want to stack your values in a column, change this

          .Cells(lRow, "D").Value = IE.Document.getElementsByClassName("class name of HTML ")(2).innerText
            .Cells(lRow, "E").Value = IE.Document.getElementsByClassName("class name of HTML ")(3).innerText

    to, for example, this

          .Cells(lRow, "D").Value = IE.Document.getElementsByClassName("class name of HTML ")(2).innerText
            .Cells(lRow + 1, "D").Value = IE.Document.getElementsByClassName("class name of HTML ")(3).innerText

    but you need to be careful how you set the initial row - choose to check what is your longest column

     lRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1

    For example, if you put everything into column D, use

     lRow = .Cells(.Rows.Count, "D").End(xlUp).Row + 1

    Thursday, October 8, 2015 3:12 PM
  • it works thanks man you are the best !
    Thursday, October 8, 2015 5:04 PM
  • 

    how do i get the inner text by class name where my code is

    <Span Class="Price" id="old-price-11760">

    $930.00         </span> ==$0

    Where id keeps on changing

    i tried this code but its not working

    sTD = Doc.getElementsByClassName("Price")(0).innerText

    Throwing run time error 91

    Please help

    Friday, October 18, 2019 6:20 AM
  • You should create a new post.  IE is dead.  Even Microsoft recommends not using it.  I've had problems with IE internet controls many years ago.  I switched to Selenium.  Someone did a version, SeleniumBasic, that works with Excel.  It is dated and Firefox and Edge no longer work but if you update ChromeDriver it still works.  Selenium supports XPath which is a very powerful technique for selecting elements.  Here is an example:

    Dim text As String
    Dim ele As Selenium.WebElement
    Dim drv As Selenium.ChromeDriver
    
    
    Sub GetPrice()
       '' Add reference to selenium type library
      Set drv = New Selenium.ChromeDriver
      Dim xPath As String
      Dim browser As String
      Dim price As String
    
      drv.Get "https://mywebsite.com"
      xPath = "//span[contains(@class, 'Price')]"
      If GetElement(xPath) Then
        price = ele.text
      End If
    End Sub
    
    Function GetElement(xPath As String) As Boolean
      On Error GoTo Handler
      
      text = ""
      Set ele = drv.FindElementByXPath(xPath)
      text = ele.text
      GetElement = True
      Exit Function
    Handler:
      Err.Clear
      GetElement = False
    End Function
    
    Function GetElementClick(xPath As String) As Boolean
      On Error GoTo Handler
      
      text = ""
      Set ele = drv.FindElementByXPath(xPath)
      ele.Click
      drv.Wait (1000)
      GetElementClick = True
      Exit Function
    Handler:
      Err.Clear
      GetElementClick = False
    End Function
    
    

    Friday, October 18, 2019 12:34 PM