?
Solved

Export networked printer information with power shell.

Posted on 2014-04-28
4
Medium Priority
?
595 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 41

Accepted Solution

by:
footech earned 1600 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 400 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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
Measuring Server's processing rate with a simple powershell command. The differences in processing rate also was recorded in different use-cases, when a server in free and busy states.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview

571 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