Solved

VBScript - sorting table

Posted on 2011-03-17
9
1,261 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 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