Solved

Export networked printer information with power shell.

Posted on 2014-04-28
4
552 Views
Last Modified: 2014-04-29
I used to have to following working to query a print server to get networked printer information.  Recently when I run it, it asks for credential(it did not ask before), and returns a lot of errors such as the following:

New-Object : Cannot load COM type Excel.Application.
At C:\printer_info.ps1:5 char:20
+ $Excel = new-Object <<<<  -comobject Excel.Application
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : CannotLoadComObjectType,Microsoft.PowerShell.Commands.NewObjectCommand

Property 'visible' cannot be found on this object; make sure it exists and is settable.
At C:\printer_info.ps1:6 char:8
+ $Excel. <<<< visible = $True
    + CategoryInfo          : InvalidOperation: (visible:String) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound

You cannot call a method on a null-valued expression.
At C:\printer_info.ps1:7 char:30
+ $Excel = $Excel.Workbooks.Add <<<< ()
    + CategoryInfo          : InvalidOperation: (Add:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Here is the PS script:

#Faster PrintInventory
$Printserver = "printsrv1"
#$cred = Get-Credential
# Create new Excel workbook
$Excel = new-Object -comobject Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
$Sheet.Cells.Item(1,1) = "Printer Name"
$Sheet.Cells.Item(1,2) = "Location"
$Sheet.Cells.Item(1,3) = "Comment"
$Sheet.Cells.Item(1,4) = "IP Address"
$Sheet.Cells.Item(1,5) = "Driver Name"
$Sheet.Cells.Item(1,6) = "Shared"
$Sheet.Cells.Item(1,7) = "Share Name"
$intRow = 2
$WorkBook = $Sheet.UsedRange
$WorkBook.Font.Bold = $True

# Get printer information
$Ports = Get-WmiObject Win32_TcpIpPrinterPort -computername $Printserver -Credential $cred
$Printers = Get-WMIObject Win32_Printer -computername $Printserver -Credential $cred
foreach ($Printer in $Printers)
{
    $Sheet.Cells.Item($intRow, 1) = $Printer.Name
    $Sheet.Cells.Item($intRow, 2) = $Printer.Location
    $Sheet.Cells.Item($intRow, 3) = $Printer.Comment
   
        foreach ($Port in $Ports)
        {
            if ($Port.Name -eq $Printer.PortName)
            {
            $Sheet.Cells.Item($intRow, 4) = $Port.HostAddress
            }
        }
    $Sheet.Cells.Item($intRow, 5) = $Printer.DriverName
    $Sheet.Cells.Item($intRow, 6) = $Printer.Shared
    $Sheet.Cells.Item($intRow, 7) = $Printer.ShareName
    $intRow = $intRow + 1
}

$WorkBook.EntireColumn.AutoFit()
$intRow = $intRow + 1
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,1) = "Print server inventory"


Please advise.
0
Comment
Question by:nav2567
  • 2
4 Comments
 

Author Comment

by:nav2567
ID: 40028620
The second line of the script has no #. I put it there just for testing.

The whole thing used to work until recently.  NOthing has changed.
0
 
LVL 40

Accepted Solution

by:
footech earned 400 total points
ID: 40028651
Well, the subsequent errors are due to the first.
You need to have Excel installed.  If it is you may want to try doing a repair on it, or re-install it.
0
 
LVL 14

Assisted Solution

by:Justin Yeung
Justin Yeung earned 100 total points
ID: 40029743
gci HKLM:\Software\Classes -ea 0| ? {$_.PSChildName -match '^\w+\.\w+$' -and ($_.PsChildName -like "*Application") -and (gp "$($_.PSPath)\CLSID" -ea 0)} | ft PSChildName

Open in new window


it will list if what application is available via comobject

in your case, it seems like your excel is not able to be launched.

if you run this command below, does powershell return with error
new-Object -comobject Excel.Application

Open in new window

0
 

Author Closing Comment

by:nav2567
ID: 40031102
Thanks, foottech.  Thanks Justin.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This script checks a path to see if a folder exists. If the folder does exist you will get output "The folder has previously been created. No action taken" If not it will create the folder. Then adds one user modify permission to the folder. It …
In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
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…

860 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