Using macros to create toner notifications

1

I have a spreadsheet, which I'm implementing in a script, which will monitor the toner level of each printer. and each printer can access via the web through 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 through F24.

Below is the script that is being created, but it is compiling with errors.

Link compilation error: link

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">&nbsp;</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>&nbsp;</TD><TD width="0%" bgColor=#ffffff>&nbsp;</TD></TR></TBODY></TABLE></TD><TD COLSPAN=3>&nbsp;</TD></TR> 

</table>

<hr class="status_hr">
<table class="status_table">
<tr>
<th colspan="5">&nbsp;</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&nbsp;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&nbsp;personalizado&nbsp;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">&nbsp;</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>
    
asked by anonymous 21.09.2015 / 20:10

0 answers