?
Solved

Export networked printer information with power shell.

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

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Utilizing an array to gracefully append to a list of EmailAddresses
A procedure for exporting installed hotfix details of remote computers using powershell
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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

762 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