[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Export networked printer information with power shell.

Posted on 2014-04-28
4
Medium Priority
?
588 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 antispam), the admini…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

650 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