Web scraping - printer web interface

loop66

New Member
Joined
Dec 18, 2015
Messages
7
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





epson.jpg



web code - toner level is displayed as height in code

HTML:
</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>
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,132
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.

VBA Code:
'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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,569
Messages
5,770,907
Members
425,651
Latest member
Mark Cashin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top