Consulting

Results 1 to 3 of 3

Thread: Web scraping from printer web interface

  1. #1

    Web scraping from printer web interface

    Hi,

    in my company there are over 100 network printers,i need to retrive toner levels from their web interface.
    I need help with this


    excel looks like this

    Location IP address BK Y M C Toner waste
    Room1 https://XX.XX.XX.XX/
    Room2 https://XX.XX.XX.XX/
    Room3 https://XX.XX.XX.XX/
    Room4 https://XX.XX.XX.XX/

    Book1-printers.xlsx


    epson.jpg


    Part of code from web interface

    HTML Code:
    </script></fieldset><div class="information-last clearfix " ><ul class="inksection"><li class='tank'>
    <div class='tank'>
    <img class='color' src='../../IMAGE/Ink_K.PNG' height='48' style=''>
    </div>
    <div class='clrname'>BK</div>
    </li><!--
    --><li class='tank'>
    <div class='tank'>
    <img class='color' src='../../IMAGE/Ink_Y.PNG' height='13' style=''>
    </div>
    <div class='clrname'>Y</div>
    </li><!--
    --><li class='tank'>
    <div class='tank'>
    <img class='color' src='../../IMAGE/Ink_M.PNG' height='12' style=''>
    </div>
    <div class='clrname'>M</div>
    </li><!--
    --><li class='tank'>
    <div class='tank'>
    <img class='color' src='../../IMAGE/Ink_C.PNG' height='9' style=''>
    </div>
    <div class='clrname'>C</div>
    </li><!--
    --><li class='tank'>
    <div class='tank'>
    <img class='color' src='../../IMAGE/Ink_Waste.PNG' height='9' style=''>
    </div>
    <div class='mbicn'><img src='../../IMAGE/Icn_Mb.PNG' height='18' width='18'></div>
    </li>

  2. #2
    UPDATE

    this code partially works:

    HTML Code:
    Dim IE As InternetExplorer, values As Object, i As Long, ws As Worksheet, t As Date
    Const MAX_WAIT_SEC As Long = 5
    
    Set IE = New InternetExplorer
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    With IE
    .Visible = True
    .Navigate2 "https://XX.XX.XX.XX/">
    While .Busy Or .readyState <> 4: DoEvents: Wend
    
    t = Timer
    Do
    Set values = .document.querySelectorAll(".tank")
    If Timer - t > MAX_WAIT_SEC Then Exit Do
    Loop While values.Length = 0
    
    If values.Length > 0 Then
    For i = 0 To values.Length - 1
    With ws
    .Cells(i + 1, 1) = values.Item(i).innerText
    
    
    End With
    Next
    End If
    .Quit
    End With

  3. #3
    VBAX Regular
    Joined
    Mar 2018
    Posts
    10
    Location
    Hi.

    It would help to know: (1) specifically, what it is you need help with; and (2) when you have code only "partially works", if you could explain which part of it is working and which part is not.

    Just looking at it, I'm not quite sure how you get the remaining ink values from the innerText property. From the HTML code you've posted, it seems almost as though the height attribute of the IMG tag might give the necessary information. Assuming that this is the case, I tried to access the height attribute values, but for whatever reason it just wasn't working, so I gave up and decided that the quicker and simpler route would just be to run a regular expression over the text.

    The code below will do that - you just need to decide how you want to put the html code into the HTMLCode variable. The results will be assigned to each of the corresponding variables (Black, Cyan, Magenta, Yellow, Waste). Hope that helps.

    Sub GetData()
    Dim Results As Object
    Dim HTMLCode As String
    Dim RegEx As Object
    Dim RoomNo As Long
    Dim IPAddress As String
    
    
    Dim Black As Long
    Dim Cyan As Long
    Dim Magenta As Long
    Dim Yellow As Long
    Dim Waste As Long
    
    
    Set RegEx = CreateObject("VBScript.RegExp")
    
    ' RoomNo = INSERTROOMNO
    ' IPAddress = INSERTIPADDRESS
    HTMLCode = INSERTCODE
    
    With RegEx
        .Pattern = "\<img class=\'color\' src=\'\.\.\/\.\.\/IMAGE\/Ink_(\w{1,5})\.PNG\' height=\'(\d{1,3})\'"
        .MultiLine = False
        .Global = True
        .ignorecase = True
            If .test(HTMLCode) Then
                Set Results = .Execute(HTMLCode)            
                Black = CLng(Results(0).Submatches(1))
                Cyan = CLng(Results(1).Submatches(1))
                Magenta = CLng(Results(2).Submatches(1))
                Yellow = CLng(Results(3).Submatches(1))
                Waste = CLng(Results(4).Submatches(1))
            End If
    End With
    
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' INSERT CODE TO WRITE THE RESULTS TO THE WORKSHEET
    
    End Sub

Posting Permissions

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