Solved

VBScript - sorting table

Posted on 2011-03-17
9
1,315 Views
Last Modified: 2012-08-13
The code below shows all the printers on our print server and their relevant status.

I want to be able to sort it so that printers with an "ERROR" or "Unknown or OFFLINE" status are at the top of the table, with the table row background in red and font colour in white.

Thanks in advance.



<HTML>
<style type="text/css">
    body {
        font-family:Arial;
        font-size: 12px;
        color: #49403B;
        background: #FFFFFF;
        scroll:no;
        }
    hr {
        color: #666666;
        }
    input.text, option {
        font-size: 18px;
        background-color: #FFFFBB;
        }
     table {
        font-size: 12px;
        text-align: left;
    }
</STYLE>

<SCRIPT LANGUAGE = "VBScript">
Sub window_onLoad
 GetInfo
 iTimerID = window.setInterval("GetInfo", 60000, "VBScript")
End Sub
Sub GetInfo
 For i = (objTable.Rows.Length - 1) to 0 Step -1
 myNewRow = Document.All.objTable.deleteRow(i)
 Next
 Set objRow = objTableBody.InsertRow()
 objRow.Style.fontWeight = "bold"
 Set objCell = objRow.InsertCell()
 objCell.InnerText = "Name"
 Set objCell = objRow.InsertCell()
 objCell.InnerText = "Location"
 Set objCell = objRow.InsertCell()
 objCell.InnerText = "Status"
 strComputer = "CMRIS"
 Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate}!\\" & _
 strComputer & "\root\cimv2")
 Set colPrinters = objWMIService.ExecQuery _
 ("SELECT * FROM Win32_Printer")
 For Each objPrinter in colPrinters
 Set objRow = objTableBody.InsertRow()
 Set objCell = objRow.InsertCell()
 objCell.InnerText = objPrinter.Name
 Set objCell = objRow.InsertCell()
 objCell.InnerText = objPrinter.Location
 Set objCell = objRow.InsertCell()
 Select Case objPrinter.PrinterStatus
 Case 1
 strPrinterStatus = "ERROR"
 Case 2
 strPrinterStatus = "Unknown or OFFLINE"
 Case 3
 strPrinterStatus = "Ready"
 Case 4
 strPrinterStatus = "Printing"
 Case 5
 strPrinterStatus = "Warming up"
 End Select
 objCell.InnerText = strPrinterStatus
 Next
End Sub
</SCRIPT>
<TABLE ID = "objTable" border = "1" style="width:100%">
<TBODY ID = "objTableBody">
</TBODY>
</TABLE>
</HTML>

Open in new window

0
Comment
Question by:IM&T SRFT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 12

Expert Comment

by:prashanthd
ID: 35155657
Hi,

Can you check if the sort is working?

regards
Prashanth
<HTML>
<style type="text/css">
body {
font-family:Arial;
font-size: 12px;
color: #49403B;
background: #FFFFFF;
scroll:no;
}
hr {
color: #666666;
}
input.text, Option {
font-size: 18px;
background-color: #FFFFBB;
}
table {
font-size: 12px;
text-align: Left;
}
</STYLE>

<SCRIPT LANGUAGE = "VBScript">
Sub window_onLoad
    GetInfo
    iTimerID = window.setInterval("GetInfo", 60000, "VBScript")
End Sub
Sub GetInfo
    Const adVarChar = 200' Set the data type to variant.
    Const MaxCharacters = 300
    
    For i = (objTable.Rows.Length - 1) To 0 Step -1
        myNewRow = Document.All.objTable.deleteRow(i)
    Next
    Set objRow = objTableBody.InsertRow()
    objRow.Style.fontWeight = "bold"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Name"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Location"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Status"
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & _
    strComputer & "\root\cimv2")
    Set colPrinters = objWMIService.ExecQuery _
    ("SELECT * FROM Win32_Printer")
    
    Set DataList = CreateObject("ADOR.Recordset")
    DataList.Fields.Append "name",adVarChar, MaxCharacters
    DataList.Fields.Append "location",adVarChar, MaxCharacters
    DataList.Fields.Append "status",adVarChar, MaxCharacters
    DataList.Fields.Append "sortst",adVarChar, MaxCharacters
    DataList.Open
    
    For Each objPrinter In colPrinters
        
        Select Case objPrinter.PrinterStatus
            Case 1
            strPrinterStatus = "ERROR"
            sortstr="1"
            Case 2
            strPrinterStatus = "Unknown or OFFLINE"
            sortstr="1"
            Case 3
            strPrinterStatus = "Ready"
            sortstr="2"
            Case 4
            strPrinterStatus = "Printing"
            sortstr="2"
            Case 5
            strPrinterStatus = "Warming up"
            sortstr="2"
        End Select
        
        If Len(Trim(objPrinter.Name))> 0 Then
            DataList.AddNew
            DataList("name") = objPrinter.Name
            WScript.Echo ctr & objPrinter.Name
            DataList("location") = objPrinter.Location
            DataList("status") = strPrinterStatus
            DataList("sortst") = sortstr 
            WScript.Echo sortstr 
            DataList.Update   	   
            ctr=ctr+1
        End If
    Next
    
    
    datalist.Sort="sortst"
    DataList.MoveFirst
    Do Until DataList.EOF
        Set objRow = objTableBody.InsertRow()
        Set objCell = objRow.InsertCell()
        objCell.InnerText = DataList.Fields.Item("name")
        WScript.Echo DataList.Fields.Item("name")
        Set objCell = objRow.InsertCell()
        objCell.InnerText = DataList.Fields.Item("location")
        Set objCell = objRow.InsertCell()
        objCell.InnerText = DataList.Fields.Item("status")
        WScript.Echo DataList.Fields.Item("status")
        DataList.MoveNext
    Loop
    
End Sub
</SCRIPT>
<TABLE ID = "objTable" border = "1" style="width:100%">
<TBODY ID = "objTableBody">
</TBODY>
</TABLE>
</HTML>

Open in new window

0
 

Author Comment

by:IM&T SRFT
ID: 35155828
Thanks for this. Have just tried it but I get a runtime error:

Line: 79
Error: Object required: 'WScript'
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35155946
Try the following, forgot to comment the wscript

regards
Prashanth
<HTML>
<style type="text/css">
body {
font-family:Arial;
font-size: 12px;
color: #49403B;
background: #FFFFFF;
scroll:no;
}
hr {
color: #666666;
}
input.text, Option {
font-size: 18px;
background-color: #FFFFBB;
}
table {
font-size: 12px;
text-align: Left;
}
</STYLE>

<SCRIPT LANGUAGE = "VBScript">
Sub window_onLoad
    GetInfo
    iTimerID = window.setInterval("GetInfo", 60000, "VBScript")
End Sub
Sub GetInfo
    Const adVarChar = 200' Set the data type to variant.
    Const MaxCharacters = 300
    
    For i = (objTable.Rows.Length - 1) To 0 Step -1
        myNewRow = Document.All.objTable.deleteRow(i)
    Next
    Set objRow = objTableBody.InsertRow()
    objRow.Style.fontWeight = "bold"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Name"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Location"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Status"
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & _
    strComputer & "\root\cimv2")
    Set colPrinters = objWMIService.ExecQuery _
    ("SELECT * FROM Win32_Printer")
    
    Set DataList = CreateObject("ADOR.Recordset")
    DataList.Fields.Append "name",adVarChar, MaxCharacters
    DataList.Fields.Append "location",adVarChar, MaxCharacters
    DataList.Fields.Append "status",adVarChar, MaxCharacters
    DataList.Fields.Append "sortst",adVarChar, MaxCharacters
    DataList.Open
    
    For Each objPrinter In colPrinters
        
        Select Case objPrinter.PrinterStatus
            Case 1
            strPrinterStatus = "ERROR"
            sortstr="1"
            Case 2
            strPrinterStatus = "Unknown or OFFLINE"
            sortstr="1"
            Case 3
            strPrinterStatus = "Ready"
            sortstr="2"
            Case 4
            strPrinterStatus = "Printing"
            sortstr="2"
            Case 5
            strPrinterStatus = "Warming up"
            sortstr="2"
        End Select
        
        If Len(Trim(objPrinter.Name))> 0 Then
            DataList.AddNew
            DataList("name") = objPrinter.Name
            'wscript.Echo ctr & objPrinter.Name
            DataList("location") = objPrinter.Location
            DataList("status") = strPrinterStatus
            DataList("sortst") = sortstr 
            'wscript.Echo sortstr 
            DataList.Update   	   
            ctr=ctr+1
        End If
    Next
    
    
    datalist.Sort="sortst"
    DataList.MoveFirst
    Do Until DataList.EOF
        Set objRow = objTableBody.InsertRow()
        Set objCell = objRow.InsertCell()
        objCell.InnerText = DataList.Fields.Item("name")
        'wscript.Echo DataList.Fields.Item("name")
        Set objCell = objRow.InsertCell()
        objCell.InnerText = DataList.Fields.Item("location")
        Set objCell = objRow.InsertCell()
        objCell.InnerText = DataList.Fields.Item("status")
        'wscript.Echo DataList.Fields.Item("status")
        DataList.MoveNext
    Loop
    
End Sub
</SCRIPT>
<TABLE ID = "objTable" border = "1" style="width:100%">
<TBODY ID = "objTableBody">
</TBODY>
</TABLE>
</HTML>

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:IM&T SRFT
ID: 35155976
Getting another error:

Line: 80
Error: Non-nullable column cannot be updated to Null.

This is probably related to the fact thjat some printers have nothing in their location field.

Thanks.
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35156029
can you try inserting "On error resume next" on line no 29
0
 

Author Comment

by:IM&T SRFT
ID: 35156214
The table now displays and sorts correctly. There are 2 issues:

1. Anywhere there is no location, it's completely blank even the table cell is missing and it looks quite strange. I would like it to still show a table cell with no writing in it.

2. I would like the ERROR and OFFLINE statuses to make the whole row red, with a white font. I want to use this to easily see which printers have a problem.

It's nearly there and I really appreciate your efforts.
0
 
LVL 12

Accepted Solution

by:
prashanthd earned 500 total points
ID: 35156357
My knowledge of HTML is limited, the font color will be changed to Red if there is a problem. I had fixed the location null issue.
<HTML>
<style type="text/css">
body {
font-family:Arial;
font-size: 12px;
color: #49403B;
background: #FFFFFF;
scroll:no;
}
hr {
color: #666666;
}
input.text, Option {
font-size: 18px;
background-color: #FFFFBB;
}
table {
font-size: 12px;
text-align: Left;
}
</STYLE>

<SCRIPT LANGUAGE = "VBScript">
Sub window_onLoad
    GetInfo
    iTimerID = window.setInterval("GetInfo", 60000, "VBScript")
End Sub
Sub GetInfo
    On Error Resume Next		
    Const adVarChar = 200' Set the data type to variant.
    Const MaxCharacters = 300
    
    For i = (objTable.Rows.Length - 1) To 0 Step -1
        myNewRow = Document.All.objTable.deleteRow(i)
    Next
    Set objRow = objTableBody.InsertRow()
    objRow.Style.fontWeight = "bold"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Name"
    Set objCell = objRow.InsertCell()
    objCell.InnerText = "Location"
    Set objCell = objRow.InsertCell()
    
    objCell.InnerText = "Status"
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & _
    strComputer & "\root\cimv2")
    Set colPrinters = objWMIService.ExecQuery _
    ("SELECT * FROM Win32_Printer")
    
    Set DataList = CreateObject("ADOR.Recordset")
    DataList.Fields.Append "name",adVarChar, MaxCharacters
    DataList.Fields.Append "location",adVarChar, MaxCharacters
    DataList.Fields.Append "status",adVarChar, MaxCharacters
    DataList.Fields.Append "sortst",adVarChar, MaxCharacters
    DataList.Open
    
    For Each objPrinter In colPrinters
        
        Select Case objPrinter.PrinterStatus
            Case 1
            strPrinterStatus = "ERROR"
            sortstr="1"
            Case 2
            strPrinterStatus = "Unknown or OFFLINE"
            sortstr="1"
            Case 3
            strPrinterStatus = "Ready"
            sortstr="2"
            Case 4
            strPrinterStatus = "Printing"
            sortstr="2"
            Case 5
            strPrinterStatus = "Warming up"
            sortstr="2"
        End Select
        
        If Len(Trim(objPrinter.Name))> 0 Then
            DataList.AddNew
            DataList("name") = objPrinter.Name
            'wscript.Echo ctr & objPrinter.Name
            DataList("location") = objPrinter.Location
            DataList("status") = strPrinterStatus
            DataList("sortst") = sortstr 
            'wscript.Echo sortstr 
            DataList.Update   	   
            ctr=ctr+1
        End If
    Next
    
    
    datalist.Sort="sortst"
    DataList.MoveFirst
    Do Until DataList.EOF
        Set objRow = objTableBody.InsertRow()
        Set objCell = objRow.InsertCell()
        If DataList.Fields.Item("sortst")="1" Then
            objCell.Innerhtml = "<font color='red'>"&DataList.Fields.Item("name")&"</font>"
        Else
            objCell.Innertext = DataList.Fields.Item("name")
        End If
        'wscript.Echo DataList.Fields.Item("name")
        Set objCell = objRow.InsertCell()
        
        If len(DataList.Fields.Item("location")) > 0 Then
	        If DataList.Fields.Item("sortst")="1" Then
	            objCell.Innerhtml = "<font color='red'>"&DataList.Fields.Item("location")&"</font>"
	        Else
	            objCell.Innertext = DataList.Fields.Item("location")
	        End If
        Else
        	objCell.Innertext = "  "
        End If
        Set objCell = objRow.InsertCell()
        If DataList.Fields.Item("sortst")="1" Then
            objCell.Innerhtml = "<font color='red'>"&DataList.Fields.Item("status")&"</font>"
        Else
            objCell.Innertext = DataList.Fields.Item("status")
        End If
        'wscript.Echo DataList.Fields.Item("status")
        DataList.MoveNext
    Loop
    
End Sub
</SCRIPT>
<TABLE ID = "objTable" border = "1" style="width:100%">
<TBODY ID = "objTableBody">
</TBODY>
</TABLE>
</HTML>

Open in new window

0
 

Author Comment

by:IM&T SRFT
ID: 35156558
This is perfect. Thank you.
0
 

Author Closing Comment

by:IM&T SRFT
ID: 35156562
Very impressive. Thank you.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server…
Script to copy or move mouse-selected collection of files plus targets referenced by shortcuts (.lnk) The purpose of this article is to help illuminate the real challenges and options available (where they may exist) for utilizing simple scriptin…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question