1
I have a spreadsheet, in which I am implementing a script, which will monitor the toner level of each printer. each printer I can access via the web via an IP.
With this script I will be able to monitor the 20 printers I am responsible for.
According to the attached worksheet the data will be displayed by Range F4 to F24.
Below follows the script being created, but this compiling with errors.
Build error Link: http://1drv.ms/1MEWLIM
Option Explicit
Public Sub spuRetornarStatusToner()
Dim wbk As Excel.Workbook
Set wbk = Excel.ThisWorkbook
Dim wsh As Excel.Worksheet
Set wsh = wbk.Sheets("CONTROLE")
Dim nvgInternetExplorer As InternetExplorer
Dim objIeDoc As Object
Dim htmLinha As HTMLTableRow
Dim htmColuna As HTMLTableCol
Dim i As Integer
For i = 1 To 20
Set nvgInternetExplorer = New InternetExplorerMedium
nvgInternetExplorer.Navigate ("http://10.10.10.10")
nvgInternetExplorer.Visible = True
While nvgInternetExplorer.ReadyState <> 4
Wend
Set objIeDoc = nvgInternetExplorer.Document
For Each htmLinha In objIeDoc.all.tags("tr")
For Each htmColuna In htmLinha.all.tags("td")
If Left$(htmColuna.innerText, 16) = "Cartucho Preto ~" Then
MsgBox Right$(htmColuna.innerText, Len(htmColuna.innerText) - 16) ' para teste verificando se o valor está correto
wsh.Range("F" & 3 + i).Value = Right$(htmColuna.innerText, Len(htmColuna.innerText) - 16)
End If
Next htmColuna
Next htmLinha
nvgInternetExplorer.Quit
Next i
Set nvgInternetExplorer = Nothing
Set objIeDoc = Nothing
Close
End Sub
Printer web page source code
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="content-type">
<meta HTTP-EQUIV="refresh" CONTENT="120; url=/cgi-bin/dynamic/printer/PrinterStatus.html">
<title>Printer Status</title>
<base target="_self">
<LINK REL="stylesheet" HREF="/cgi-bin/dynamic/printer/configStyle.css" TYPE="text/css">
</head>
<body>
<!-- Device Status Title-->
<h3>Status do dispositivo - <a href="/" TARGET="_top"><sub><b>Atualizar</b></sub></a></h3>
<!-- Job Parking -->
<hr class="status_hr"/>
<!-- Panel and Toner Level -->
<table class="status_table">
<tr>
<th colspan="4"> </th>
</tr>
<!-- Toner Level -->
<tr>
<td colspan="4">
<B>Status do toner:</B>
</td>
</tr>
<TR><TD COLSPAN=4><B>Cartucho Preto ~100%</B></TD></TR><TR>
<TD width="25%">
<TABLE style="table-layout:fixed" borderColor=#000000 cellSpacing=0 width="100%" border=1><TBODY>
<TR>
<TD width="100%" bgColor=#000000> </TD><TD width="0%" bgColor=#ffffff> </TD></TR></TBODY></TABLE></TD><TD COLSPAN=3> </TD></TR>
</table>
<hr class="status_hr">
<table class="status_table">
<tr>
<th colspan="5"> </th>
</tr>
<tr>
<td width="23%"><P align="left" style="margin-top: 5; margin-bottom: 5"><B>Bandeja de entrada de papel:</B></P> </TD>
<td width="20%"><P align="left" style="margin-top: 5; margin-bottom: 5"><B>Status:</B></P> </TD>
<td width="15%"><P align="left" style="margin-top: 5; margin-bottom: 5"><B>Capacidade:</B></P></TD>
<td width="15%"><P align="left" style="margin-top: 5; margin-bottom: 5"><B>Tamanho:</B></P></TD>
<td width="25%"><P align="left" style="margin-top: 5; margin-bottom: 5"><B>Tipo:</B></TD>
</tr>
<TR>
<TD><P style="margin-left:5">Bandeja 1</P></TD>
<td width="30%"><P style="margin-left:5"><table width="50%" bgcolor="#009900" style="padding: .75pt" height="1"><tr><td><b>OK</b></TD></TR></TABLE></P></TD>
<TD><P style="margin-left:5">550</P></TD>
<TD><P style="margin-left:5">A4</P></TD>
<TD><P style="margin-left:5">Papel comum</P></TD>
</TR>
<TR>
<TD><P style="margin-left:5">Alimentador MF</P></TD>
<td width="30%"><P style="margin-left:5"><table width="50%" bgcolor="FFFFFF" style="padding: .75pt" height="1"><tr><td><b>OK</b></TD></TR></TABLE></P></TD>
<TD><P style="margin-left:5">100</P></TD>
<TD><P style="margin-left:5">A4</P></TD>
<TD><P style="margin-left:5">Tipo personalizado 6</P></TD>
</TR>
</table>
<hr class="status_hr"/>
<table class="status_table">
<tr>
<td width="23%"><P align="left" style="margin-top: 5; margin-bottom: 5"><B>Bandeja de saída de papel:</B></P></TD>
<td width="20%"><P align="left" style="margin-top: 5; margin-bottom: 5"><B>Status:</B></P> </TD>
<td width="15%"><P align="left" style="margin-top: 5; margin-bottom: 5"><B>Capacidade:</B></P></TD>
<td width="15%"><P align="left" style="margin-top: 5; margin-bottom: 5"><B></B></P></TD>
<td width="25%"><P align="left" style="margin-top: 5; margin-bottom: 5"><B></B></P></TD>
</tr>
<TR>
<TD><P style="margin-left:5">Bandeja padrão</P></TD>
<td width="30%"><P style="margin-left:5"><table width="50%" bgcolor="#009900" style="padding: .75pt" height="1"><tr><td><b>OK</b></TD></TR></TABLE></P></TD>
<TD><P style="margin-left:5">250</P></TD>
<TD><P style="margin-left:5"></P></TD>
<TD><P style="margin-left:5"></P></TD>
</TR>
</table>
<hr class="status_hr">
<table class="status_table">
<tr>
<th colspan="2"> </th>
</tr>
<TR>
<TD width="40%"><B>Tipo de dispositivo:</B></TD><TD width="60%">Laser monocromática</TD>
</TR><TR>
<TD><B>Velocidade:</B></TD><TD>Até 50 Páginas/minuto</TD>
</TR><TR>
<TD><B>Cartucho toner Capacidade:</B></TD><TD>Aproximadamente 7.500 páginas com 5% de cobertura</TD>
</TR><TR>
<TD><B>Kit manutenção Vida restante:</B></TD><TD>100%</TD>
</TR><TR>
<TD><B>Unid. imagem Vida restante:</B></TD><TD>87%</TD>
</TR>
</table>
</body>
</html>