Web scraping - printer web interface

[loop66]

Hi,

in my company there are over 100 network printers,i need to retrieve toner levels from their web interface in excel book

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/

web code - toner level is displayed as height in 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' [B]height='48'[/B] style=''>
</div>
<div class='clrname'>[B]BK[/B]</div>
</li><!--
--><li class='tank'>
<div class='tank'>
<img class='color' src='../../IMAGE/Ink_Y.PNG' [B]height='13'[/B] style=''>
</div>
<div class='clrname'>[B]Y[/B]</div>
</li><!--
--><li class='tank'>
<div class='tank'>
<img class='color' src='../../IMAGE/Ink_M.PNG' [B]height='12'[/B] style=''>
</div>
<div class='clrname'>[B]M[/B]</div>
</li><!--
--><li class='tank'>
<div class='tank'>
<img class='color' src='../../IMAGE/Ink_C.PNG' [B]height='9'[/B] style=''>
</div>
<div class='clrname'>[B]C[/B]</div>
</li><!--
--><li class='tank'>
<div class='tank'>
<img class='color' src='../../IMAGE/Ink_Waste.PNG' [B]height='9'[/B] style=''>
</div>
<div class='mbicn'><img src='../../IMAGE/Icn_Mb.PNG' height='18' width='18'></div>
</li>

 

[John_w]

Try this macro. You must set the two references listed at the top of the code, via Tools -> References in the VBA editor, otherwise the code won't compile or run.

'References
'Microsoft Internet Controls
'Microsoft HTML Object Library

Option Explicit

Public Sub IE_Printers_Ink_Levels()
    
    Dim IE As InternetExplorer
    Dim HTMLdoc As HTMLDocument
    Dim inkLevels As IHTMLDOMChildrenCollection
    Dim inkLevel As HTMLImg
    Dim height As String
    Dim lastRow As Long, r As Long
    
    Set IE = New InternetExplorer
    IE.Visible = True
    
    With ActiveSheet
    
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For r = 2 To lastRow
            .Range("C" & r & ":G" & r).Clear
            IE.navigate .Cells(r, "B").Value
            While IE.Busy And IE.readyState <> READYSTATE_COMPLETE: DoEvents: Wend
            Set HTMLdoc = IE.document
            
            '<img class='color' src='../../IMAGE/Ink_K.PNG' height='60' style=''>
            
            Set inkLevels = HTMLdoc.querySelectorAll("img.color")
            
            For Each inkLevel In inkLevels
                height = inkLevel.getAttribute("height")
                Select Case True
                    Case InStr(inkLevel.src, "Ink_K"): .Cells(r, "C").Value = height
                    Case InStr(inkLevel.src, "Ink_Y"): .Cells(r, "D").Value = height
                    Case InStr(inkLevel.src, "Ink_M"): .Cells(r, "E").Value = height
                    Case InStr(inkLevel.src, "Ink_C"): .Cells(r, "F").Value = height
                    Case InStr(inkLevel.src, "Ink_Waste"): .Cells(r, "G").Value = height
                End Select
            Next
        
        Next
        
    End With
    
End Sub

 

[loop66]

thnxxx