Solved

Export networked printer information with power shell.

Posted on 2014-04-28
4
540 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 39

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
A procedure for exporting installed hotfix details of remote computers using powershell
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

786 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