On Error Resume Next

Dim strComputer, strExcelPath, objExcel, objSheet, k, objGroup
Dim objWMIService, colItems, ErrState, Sheet

'Sheet = spreadsheet page, k = row in sheet
Sheet = 1
k = 2

strComputer = InputBox ("Please type the print server name to check, " & vbCrLf & "Else enter ALL for all CC print servers", "Server Name")

if strComputer = "" Then
        WScript.quit
end if

strExcelPath = InputBox ("Please enter the path to save file to: ", "File path", "D:\")
strExcelPath = strExcelPath & "Printers_" & strComputer & ".xls"
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_Printer",,48)

' Bind to Excel object.
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If Err.Number <> 0 Then
        On Error GoTo 0
        Wscript.Echo "Excel application not found."
        Wscript.Quit
End If
On Error GoTo 0

' Create a new workbook.
objExcel.Workbooks.Add

'Change this to fit your server situation
Select Case UCase(strComputer)
Case "ALL"
        PrintServer("BNTPRNT1")
        Sheet = Sheet + 1
        PrintServer("BNTDOM2")
Case Else
        PrintServer(strComputer)
End Select

Function PrintServer(strComputer)
        k=2
        Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
        Set colItems = objWMIService.ExecQuery("Select * from Win32_Printer",,48)
        ' Bind to worksheet.
        Set objSheet = objExcel.ActiveWorkbook.Worksheets(Sheet)
        objSheet.Name = strComputer
        ' Populate spreadsheet cells with printer attributes.
        objSheet.Cells(1, 1).Value = "Name"
        objSheet.Cells(1, 2).Value = "ShareName"
        objSheet.Cells(1, 3).Value = "Comment"
        objSheet.Cells(1, 4).Value = "Error"
        objSheet.Cells(1, 5).Value = "DriverName"
        objSheet.Cells(1, 6).Value = "EnableBIDI"
        objSheet.Cells(1, 7).Value = "JobCount"
        objSheet.Cells(1, 8).Value = "Location"
        objSheet.Cells(1, 9).Value = "PortName"
        objSheet.Cells(1, 10).Value = "Published"
        objSheet.Cells(1, 11).Value = "Queued"
        objSheet.Cells(1, 12).Value = "Shared"
        objSheet.Cells(1, 13).Value = "Status"
        For Each objItem in colItems
                'put error code into human readable form
                Select Case objItem.DetectedErrorState
                        Case 4
                                ErrState = "Out of Paper"
                        Case 5
                                ErrState = "Toner low"
                        Case 6
                                ErrState = "Printing"
                        Case 9
                                ErrState = "Offline"
                        Case Else
                                ErrState = objItem.DetectedErrorState
                End Select
                On Error Resume next
                'populate the row with this printer's data
                objSheet.Cells(k, 1).Value = objItem.Name
                objSheet.Cells(k, 2).Value = objItem.ShareName
                objSheet.Cells(k, 3).Value = objItem.Comment
                objSheet.Cells(k, 4).Value = ErrState
                objSheet.Cells(k, 5).Value = objItem.DriverName
                objSheet.Cells(k, 6).Value = objItem.EnableBIDI
                objSheet.Cells(k, 7).Value = objItem.JobCountSinceLastReset
                objSheet.Cells(k, 8).Value = objItem.Location
                objSheet.Cells(k, 9).Value = objItem.PortName
                objSheet.Cells(k, 10).Value = objItem.Published
                objSheet.Cells(k, 11).Value = objItem.Queued
                objSheet.Cells(k, 12).Value = objItem.Shared
                objSheet.Cells(k, 13).Value = objItem.Status
                k = k + 1
        Next
        ' Format the spreadsheet.
        objSheet.Range("A1:M1").Font.Bold = True
        objSheet.Select
        objSheet.Range("A2").Select
        objExcel.ActiveWindow.FreezePanes = True
        objExcel.Columns(3).ColumnWidth = 25
        objExcel.Columns(5).ColumnWidth = 25
        objExcel.Columns(6).ColumnWidth = 10
        objExcel.Columns(8).ColumnWidth = 25
        objExcel.Columns(1).ColumnWidth = 20
        objExcel.Columns(9).ColumnWidth = 14
        objExcel.Columns(2).ColumnWidth = 15
End Function

' Save the spreadsheet and close the workbook.
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

' Quit Excel.
objExcel.Application.Quit

' Clean Up
Set objUser = Nothing
Set objGroup = Nothing
Set objSheet = Nothing
Set objExcel = Nothing

WScript.Echo "Printer listing is done"