Why PowerShell "print server inventory script" is not working?

Here is an error that what i got
The term '.\printser.ps1' is not recognized as the name of a cmdlet, function,
script file, or operable program. Check the spelling of the name, or if a path
was included, verify that the path is correct and try again.
At line:1 char:15
+ .\printser.ps1 <<<<
    + CategoryInfo          : ObjectNotFound: (.\printser.ps1:String) [], Comm
   andNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

when i ran this on Powershel v.3 i got this error

At C:\Temp\Scripts\test.ps1:3 char:40
+ foreach ($Printserver in $Printservers)
+                                        ~
Missing statement body in foreach loop.
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : MissingForeachStatement

Hi, try this revised code

# Set print server name
$Printservers = Get-Content -path c:\temp\servers.txt
foreach ($Printserver in $Printservers) {

    # Create new Excel workbook
    $excel = New-Object -COM 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) = “Modell”
    $intRow = 2
    $WorkBook = $Sheet.UsedRange
    $WorkBook.Font.Bold = $True

    # Get printer information
    $Printers = Get-WMIObject Win32_Printer -computername $Printserver
    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
        $Ports = Get-WmiObject Win32_TcpIpPrinterPort -computername $Printserver
        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.Modell
        $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"
}
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
Remove-Variable excel

Select allOpen in new window

Rob.

$excel = New-Object -ComObject Excel.Application
vice
    $excel = New-Object -COM Excel.Application
also added best practice of using indentation

also you were missing the { } in the foreach loop
At C:\Temp\Scripts\test.ps1:3 char:40
+ foreach ($Printserver in $Printservers)
+                                        ~
Missing statement body in foreach loop.

# Set print server name
$Printservers = Get-Content -path c:\temp\servers.txt

foreach ($Printserver in $Printservers){

    # 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) = “Model”
    $Sheet.Cells.Item(1,7) = “Share Name”
    $intRow = 2
    $WorkBook = $Sheet.UsedRange
    $WorkBook.Font.Bold = $True

    # Get printer information
    $Printers = Get-WMIObject Win32_Printer -computername $Printserver
    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
        $Ports = Get-WmiObject Win32_TcpIpPrinterPort -computername $Printserver
        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.Modell
        $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"
}

Select allOpen in new window

Hi Rob

The report is generating but i am getting this error and also column F is not aline with column G.  Please check attachment.


Exception setting "Item": "Exception from HRESULT: 0x800AC472"
At C:\temp\Scripts\prinser.ps1:32 char:34
+                 $Sheet.Cells.Item <<<< ($intRow, 4) = $Port.HostAddress
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationExceptio
   n
    + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertySetValu
   eTI

--------------------

How  difficult would be to modify this script to overwrite location and comment entry for each printer objects on print servers. Would that be possible to  use this generated report and change information in these two " location and comment" and apply it back using powershell script.

Thx, Michal
result.xlsx

Also, would that be possible to automatically  save each generate excel file  from each print server and save it as the name of the print server

Hi, try this revision, and it should write a new excel file per print server.

# Set print server name
$Printservers = Get-Content -path c:\temp\servers.txt
# Create new Excel workbook
$objExcel = New-Object -COM Excel.Application
$objExcel.visible = $True
foreach ($Printserver in $Printservers) {

    $Excel = $objExcel.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) = “Share Name”
    $intRow = 2
    $WorkBook = $Sheet.UsedRange
    $WorkBook.Font.Bold = $True

    # Get printer information
    $Printers = Get-WMIObject Win32_Printer -computername $Printserver
    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
        $Ports = Get-WmiObject Win32_TcpIpPrinterPort -computername $Printserver
        foreach ($Port in $Ports)
        {
            if ($Port.Name -eq $Printer.PortName)
            {
                $Sheet.Cells.Item($intRow, 4) = $Port.HostAddress.ToString()
            }
        }
        $Sheet.Cells.Item($intRow, 5) = $Printer.DriverName
        $Sheet.Cells.Item($intRow, 6) = $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"
    $Excel.SaveAs("$PrintServer.xlsx", $True)
    $Excel.Close($True)
}
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
Remove-Variable excel
Remove-Variable objExcel

Select allOpen in new window

As for writing back a new value, how would you supply those values?  Maybe you could use a second script to read the Excel file and write the values back to the printer?

Rob.

That would be great , do you think you can help me with this? Do i need to open another request? please advice

I can write a script to do the reverse.  I've made a couple of minor changes to the inventory script, so use this one.

# Set print server name
$Printservers = Get-Content -path c:\temp\servers.txt
$CurrentFolder = Split-Path -Parent $MyInvocation.MyCommand.Definition
# Create new Excel workbook
$objExcel = New-Object -COM Excel.Application
$objExcel.visible = $True
foreach ($Printserver in $Printservers) {

    $Excel = $objExcel.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) = “Share Name”
    $Sheet.Cells.Item(1,7) = “Device ID”
    $intRow = 2
    $WorkBook = $Sheet.UsedRange
    $WorkBook.Font.Bold = $True

    # Get printer information
    $Printers = Get-WMIObject Win32_Printer -computername $Printserver
    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
        $Ports = Get-WmiObject Win32_TcpIpPrinterPort -computername $Printserver
        foreach ($Port in $Ports)
        {
            if ($Port.Name -eq $Printer.PortName)
            {
                $Sheet.Cells.Item($intRow, 4) = $Port.HostAddress.ToString()
            }
        }
        $Sheet.Cells.Item($intRow, 5) = $Printer.DriverName
        $Sheet.Cells.Item($intRow, 6) = $Printer.ShareName
        $Sheet.Cells.Item($intRow, 7) = $Printer.DeviceID
        $intRow = $intRow + 1
    }
    [void] $WorkBook.EntireColumn.AutoFit()
    $intRow = $intRow + 1
    $Sheet.Cells.Item($intRow,1).Font.Bold = $True
    $Sheet.Cells.Item($intRow,1) = “Print server inventory"
    $objExcel.DisplayAlerts = $False
    $Excel.SaveAs("$CurrentFolder\$PrintServer.xlsx")
    $objExcel.DisplayAlerts = $True
    $Excel.Close($True)
}
$objExcel.Quit()
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
Remove-Variable excel
Remove-Variable objExcel

Select allOpen in new window

I'll work on the one to write back the values.

Rob.

It works like charm!!!
Can you help me write that will write back and modify some of the field?

Printer Name      
Location       
Comment      
Share Name

OK, this should do the setting of those four properties.  Specify the InputFile for it to read, with the same format as the output file produced by the first script.

Note: I have updated the first script to include DeviceID in the seventh column, so please use the new code

Also, please try to test this on a test system to make sure it works as intended.

Regards,

Rob.

# Set print server name
$CurrentFolder = Split-Path -Parent $MyInvocation.MyCommand.Definition
$InputFile = "$CurrentFolder\PRINTSERVER.xlsx"
$ComputerName = Get-ChildItem $InputFile | Select -ExpandProperty BaseName
# Create new Excel workbook
$objExcel = New-Object -COM Excel.Application
$objExcel.visible = $True
$Excel = $objExcel.Workbooks.Open($InputFile, $False, $False)
$Sheet = $Excel.Worksheets.Item(1)
$intLastRow = $Sheet.Cells.Item(65536, 1).End(-4162).Row
For ($i = 2; $i -le $intLastRow; $i++) {
    $Name = $Sheet.Cells.Item($i,1).Text
    $Location = $Sheet.Cells.Item($i,2).Text
    $Comment = $Sheet.Cells.Item($i,3).Text
    $ShareName = $Sheet.Cells.Item($i,6).Text
    $DeviceID = $Sheet.Cells.Item($i,7).Text
    $Query = "SELECT * FROM Win32_Printer WHERE DeviceID='$DeviceID'"
    $Printer = Get-WMIObject -Query $Query -ComputerName $ComputerName | Set-WmiInstance -Arguments @{Name=$Name;Location=$Location;Comment=$Comment;ShareName=$ShareName}
}
$Excel.Close($False)
$objExcel.Quit()
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
Remove-Variable excel
Remove-Variable objExcel

Select allOpen in new window

Hmm, perhaps i am doing something wrong.

I ran first script that created printer objects information it save automatically file as print server name.

Then i ran second script and i got this?
What should be in \PRINTSERVER.xlsx?  


Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x80070
6BA)
At C:\temp\Scripts\writeprintobject.ps1:18 char:29
+     $Printer = Get-WMIObject <<<<  -Query $Query -ComputerName $ComputerName
| Set-WmiInstance -Arguments @{Name=$Name;Location=$Location;Comment=$Comment;S
hareName=$ShareName}
    + CategoryInfo          : InvalidOperation: (:) [Get-WmiObject], COMExcept
   ion
    + FullyQualifiedErrorId : GetWMICOMException,Microsoft.PowerShell.Commands
   .GetWmiObjectCommand

Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x80070
6BA)
At C:\temp\Scripts\writeprintobject.ps1:18 char:29
+     $Printer = Get-WMIObject <<<<  -Query $Query -ComputerName $ComputerName
| Set-WmiInstance -Arguments @{Name=$Name;Location=$Location;Comment=$Comment;S
hareName=$ShareName}
    + CategoryInfo          : InvalidOperation: (:) [Get-WmiObject], COMExcept
   ion
    + FullyQualifiedErrorId : GetWMICOMException,Microsoft.PowerShell.Commands
   .GetWmiObjectCommand

Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x80070
6BA)
At C:\temp\Scripts\writeprintobject.ps1:18 char:29
+     $Printer = Get-WMIObject <<<<  -Query $Query -ComputerName $ComputerName
| Set-WmiInstance -Arguments @{Name=$Name;Location=$Location;Comment=$Comment;S
hareName=$ShareName}
    + CategoryInfo          : InvalidOperation: (:) [Get-WmiObject], COMExcept
   ion
    + FullyQualifiedErrorId : GetWMICOMException,Microsoft.PowerShell.Commands
   .GetWmiObjectCommand

I think i got it.

I changed  Location      Comment and i ran script.
The result both fields changed "Location      Comment" to test
Unfortunately, Printer name did not get rename and share name did not change either.
Is it possible to modify script so also can be apply and modify print name and share name?

The PRINTERSHARE.xlsx should be an input file with the same name as a file that was output by the first script.

I didn't test the name properties. I'll set up a test printer and see what we need.

Rob.

Hi, it needed a bit of a rework after more testing, but this should write the properties back.

Regards,

Rob.

# Set print server name
$CurrentFolder = Split-Path -Parent $MyInvocation.MyCommand.Definition
$InputFile = "$CurrentFolder\MCCWIN702.xlsx"
$ComputerName = Get-ChildItem $InputFile | Select -ExpandProperty BaseName
# Create new Excel workbook
$objExcel = New-Object -COM Excel.Application
$objExcel.visible = $True
$Excel = $objExcel.Workbooks.Open($InputFile, $False, $False)
$Sheet = $Excel.Worksheets.Item(1)
$intLastRow = $Sheet.Cells.Item(65536, 1).End(-4162).Row
For ($i = 2; $i -le $intLastRow; $i++) {
    $Name = $Sheet.Cells.Item($i,1).Text
    $Location = $Sheet.Cells.Item($i,2).Text
    $Comment = $Sheet.Cells.Item($i,3).Text
    $ShareName = $Sheet.Cells.Item($i,6).Text
    $DeviceID = $Sheet.Cells.Item($i,7).Text
    $Query = "SELECT * FROM Win32_Printer WHERE DeviceID='$DeviceID'"
    Get-WMIObject -Query $Query -ComputerName $ComputerName | ForEach {
        $PropsToChange = ""
        If (($_.Location) -ne $Location) {
            Write-Output "Updating location from `"$($_.Location)`" to `"$Location`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Location=`$Location"
        } Else {
            Write-Output "Not changing location from `"$($_.Location)`" for device `"$($_.DeviceID)`""
        }
        If (($_.Comment) -ne $Comment) {
            Write-Output "Updating comment from `"$($_.Comment)`" to `"$Comment`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Comment=`$Comment"
        } Else {
            Write-Output "Not changing comment from `"$($_.Comment)`" for device `"$($_.DeviceID)`""
        }
        If (($_.ShareName) -ne $ShareName) {
            Write-Output "Updating sharename from `"$($_.ShareName)`" to `"$ShareName`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";ShareName=`$ShareName"
        } Else {
            Write-Output "Not changing sharename from `"$($_.ShareName)`" for device `"$($_.DeviceID)`""
        }
        If ($PropsToChange.Length -gt 0) {
            $Expression = "`$_ | Set-WmiInstance -Arguments @{$($PropsToChange.Substring(1))} | Out-Null"
            Invoke-Expression $Expression
        }
        If (($_.Name) -ne $Name) {
            Write-Output "Renaming device `"$($_.Name)`" to `"$Name`""
            $_.RenamePrinter($Name) | Out-Null
        } Else {
            Write-Output "Not renaming device `"$($_.Name)`""
        }
    }
}
$Excel.Close($False)
$objExcel.Quit()
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Sheet)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
Remove-Variable Sheet
Remove-Variable Excel
Remove-Variable objExcel

Select allOpen in new window

It doesn't work

i got this error

Not changing sharename from "SNV-YLABS" for device "SNV-YLABS"
Not renaming device "SNV-YLABS"
Updating location from "B 4rd Floor 4616" to "Test" for device "SNV-YESSTRONG"
Updating comment from "B 4rd Floor 4616" to "Test" for device "SNV-YESSTRONG"
Not changing sharename from "SNV-YESSTRONG" for device "SNV-YESSTRONG"
Set-WmiInstance : Generic failure
At line:1 char:21
+ $_ | Set-WmiInstance <<<<  -Arguments @{Location=$Location;Comment=$Comment}
| Out-Null
    + CategoryInfo          : InvalidOperation: (:) [Set-WmiInstance], Managem
   entException
    + FullyQualifiedErrorId : SetWMIManagementException,Microsoft.PowerShell.C
   ommands.SetWmiInstance

Not renaming device "SNV-YESSTRONG"
Updating location from ""D, 2nd Floor, 2322 - Private printer - D not use"" to
"Test" for device "SNV-YANKEE"
Updating comment from ""D, 2nd Floor, 2322 - Private printer - D not use"" to "
Test" for device "SNV-YANKEE"
Not changing sharename from "SNV-YANKEE" for device "SNV-YANKEE"
Set-WmiInstance : Generic failure
At line:1 char:21
+ $_ | Set-WmiInstance <<<<  -Arguments @{Location=$Location;Comment=$Comment}
| Out-Null

Are you running Powershell "As Administrator"?  You will need admin rights to the server to make changes to the printer objects.

Rob.

i am logged in as domain admin but not as local admin

you have to explicitly run as an administrator

so, it change only location and comment for one printer but for the reset nothing

Updating location from ""B, 3rd Floor, 3327 - Private printer - Do not use""
 "man" for device "SNV-USUALGONE"
Updating comment from ""B, 3rd Floor, 3327 - Private printer - Do not use"" t
"man" for device "SNV-USUALGONE"
Not changing sharename from "SNV-USUALGONE" for device "SNV-USUALGONE"
Set-WmiInstance : Generic failure
At line:1 char:6
+ $_ | Set-WmiInstance -Arguments @{Location=$Location;Comment=$Comment} |
Out-Nul ...
+      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Set-WmiInstance], Manage
   entException
    + FullyQualifiedErrorId : SetWMIManagementException,Microsoft.PowerShell.
   ommands.SetWmiInstance

Not renaming device "SNV-USUALGONE"
Updating location from ""D, 2nd Floor, 240 - Private printer - Do not use"" t
"test" for device "SNV-UNDERDOG"
Updating comment from ""D, 2nd Floor, 240 - Private printer - Do not use"" to
test" for device "SNV-UNDERDOG"
Not changing sharename from "SNV-UNDERDOG" for device "SNV-UNDERDOG"
Set-WmiInstance : Generic failure
At line:1 char:6
+ $_ | Set-WmiInstance -Arguments @{Location=$Location;Comment=$Comment} |
Out-Nul ...
+      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Set-WmiInstance], Manage
   entException
    + FullyQualifiedErrorId : SetWMIManagementException,Microsoft.PowerShell.
   ommands.SetWmiInstance

Not renaming device "SNV-UNDERDOG"
Not changing location from "rock" for device "SNV-UBERWOODLED"
Not changing comment from "rock" for device "SNV-UBERWOODLED"
Not changing sharename from "SNV-UBERWOODLED" for device "SNV-UBERWOODLED"
Not renaming device "SNV-UBERWOODLED"
Not changing location from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing comment from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing sharename from "SNV-TANNER" for device "SNV-TANNER"
Not renaming device "SNV-TANNER"
Not changing location from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing comment from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing sharename from "SNV-TANNER" for device "SNV-TANNER"
Not renaming device "SNV-TANNER"
Not changing location from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing comment from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing sharename from "SNV-TANNER" for device "SNV-TANNER"
Not renaming device "SNV-TANNER"
Not changing location from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing comment from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing sharename from "SNV-TANNER" for device "SNV-TANNER"
Not renaming device "SNV-TANNER"
PS C:\Temp\Scripts>

So, I executed more test and i run script against 2 different printer servers and scripts work perfect without any issues.

I could change location, comments, share name and display name.

Would that be possible to add another function to this list to modify IP address.
Also, can we export to log file all changes was made and not made?

How would be difficult to add another script that would pull from spread sheet list of printers that needs to be unshared "disable sharing"   - do you want me open another request?

OK, so to add the "Shared" property, use this as script 1 that gathers the inventory:

# Set print server name
$Printservers = Get-Content -path c:\temp\servers.txt
$CurrentFolder = Split-Path -Parent $MyInvocation.MyCommand.Definition
# Create new Excel workbook
$objExcel = New-Object -COM Excel.Application
$objExcel.visible = $True
foreach ($Printserver in $Printservers) {

    $Excel = $objExcel.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”
    $Sheet.Cells.Item(1,8) = “Device ID”
    $intRow = 2
    $WorkBook = $Sheet.UsedRange
    $WorkBook.Font.Bold = $True

    # Get printer information
    $Printers = Get-WMIObject Win32_Printer -computername $Printserver
    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
        $Ports = Get-WmiObject Win32_TcpIpPrinterPort -computername $Printserver
        foreach ($Port in $Ports)
        {
            if ($Port.Name -eq $Printer.PortName)
            {
                $Sheet.Cells.Item($intRow, 4) = $Port.HostAddress.ToString()
            }
        }
        $Sheet.Cells.Item($intRow, 5) = $Printer.DriverName
        $Sheet.Cells.Item($intRow, 6) = $Printer.Shared
        $Sheet.Cells.Item($intRow, 7) = $Printer.ShareName
        $Sheet.Cells.Item($intRow, 8) = $Printer.DeviceID
        $intRow = $intRow + 1
    }
    [void] $WorkBook.EntireColumn.AutoFit()
    $intRow = $intRow + 1
    $Sheet.Cells.Item($intRow,1).Font.Bold = $True
    $Sheet.Cells.Item($intRow,1) = “Print server inventory"
    $objExcel.DisplayAlerts = $False
    $Excel.SaveAs("$CurrentFolder\$PrintServer.xlsx")
    $objExcel.DisplayAlerts = $True
    $Excel.Close($True)
}
$objExcel.Quit()
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
Remove-Variable excel
Remove-Variable objExcel

Select allOpen in new window

And this as script 2 to write the changes back:

# Set print server name
$CurrentFolder = Split-Path -Parent $MyInvocation.MyCommand.Definition
$InputFile = "$CurrentFolder\PrintServerName.xlsx"
$ComputerName = Get-ChildItem $InputFile | Select -ExpandProperty BaseName
# Create new Excel workbook
$objExcel = New-Object -COM Excel.Application
$objExcel.visible = $True
$Excel = $objExcel.Workbooks.Open($InputFile, $False, $False)
$Sheet = $Excel.Worksheets.Item(1)
$intLastRow = $Sheet.Cells.Item(65536, 1).End(-4162).Row
For ($i = 2; $i -le $intLastRow; $i++) {
    $Name = $Sheet.Cells.Item($i,1).Text
    $Location = $Sheet.Cells.Item($i,2).Text
    $Comment = $Sheet.Cells.Item($i,3).Text
    $Shared = $Sheet.Cells.Item($i,6).Text
    If ($Shared -eq "TRUE") { $Shared = $True } Else { $Shared = $False }
    $ShareName = $Sheet.Cells.Item($i,7).Text
    $DeviceID = $Sheet.Cells.Item($i,8).Text
    $Query = "SELECT * FROM Win32_Printer WHERE DeviceID='$DeviceID'"
    Get-WMIObject -Query $Query -ComputerName $ComputerName | ForEach {
        $PropsToChange = ""
        # --- Location property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Location)) -and ([string]::IsNullOrEmpty($Location))) -eq $False) {
            If (($_.Location) -ne $Location) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating location from `"$($_.Location)`" to `"$Location`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Location=`$Location"
        } Else {
            Write-Output "Not changing location from `"$($_.Location)`" to `"$Location`" for device `"$($_.DeviceID)`""
        }
        # --- Comment property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Comment)) -and ([string]::IsNullOrEmpty($Comment))) -eq $False) {
            If (($_.Comment) -ne $Comment) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating comment from `"$($_.Comment)`" to `"$Comment`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Comment=`$Comment"
        } Else {
            Write-Output "Not changing comment from `"$($_.Comment)`" to `"$Comment`" for device `"$($_.DeviceID)`""
        }
        # --- Shared property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Shared)) -and ([string]::IsNullOrEmpty($Shared))) -eq $False) {
            If ($($_.Shared) -ne $Shared) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating shared property from `"$($_.Shared)`" to `"$Shared`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Shared=`$Shared"
        } Else {
            Write-Output "Not changing shared property from `"$($_.Shared)`" to `"$Shared`" for device `"$($_.DeviceID)`""
        }
        # --- ShareName property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.ShareName)) -and ([string]::IsNullOrEmpty($ShareName))) -eq $False) {
            If (($_.ShareName) -ne $ShareName) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating sharename from `"$($_.ShareName)`" to `"$ShareName`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";ShareName=`$ShareName"
        } Else {
            Write-Output "Not changing sharename from `"$($_.ShareName)`" to `"$ShareName`" for device `"$($_.DeviceID)`""
        }
        # --- Set all properties ---
        If ($PropsToChange.Length -gt 0) {
            $Expression = "`$_ | Set-WmiInstance -Arguments @{$($PropsToChange.Substring(1))} | Out-Null"
            Invoke-Expression $Expression
        }
        # --- Name property (for renaming) ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Name)) -and ([string]::IsNullOrEmpty($Name))) -eq $False) {
            If (($_.Name) -ne $Name) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Renaming device `"$($_.Name)`" to `"$Name`""
            $_.RenamePrinter($Name) | Out-Null
        } Else {
            Write-Output "Not renaming device `"$($_.Name)`" to `"$Name`""
        }
    } | Out-File "$CurrentFolder\Printer_Change_Log.txt" -Append
}
$Excel.Close($False)
$objExcel.Quit()
Invoke-Item "$CurrentFolder\Printer_Change_Log.txt"
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Sheet)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
Remove-Variable Sheet
Remove-Variable Excel
Remove-Variable objExcel

Select allOpen in new window

So the above scripts give you an output file with the logging detail, and the ability to change the Shared status.

Changing the IP address would require a fair bit more code, because you would need to create the TCP/IP port before assigning the printer to it.  

Rob.

i forgot to ask you something.  Would that be possible to add to the script another function to change print driver for some of the printers.
 
That would save me time if could change that in excel file and push it back against print server.

Not really sure whether DriverName would change successfully this way, but you can try it.

# Set print server name
$CurrentFolder = Split-Path -Parent $MyInvocation.MyCommand.Definition
$InputFile = "$CurrentFolder\PrintServerName.xlsx"
$ComputerName = Get-ChildItem $InputFile | Select -ExpandProperty BaseName
# Create new Excel workbook
$objExcel = New-Object -COM Excel.Application
$objExcel.visible = $True
$Excel = $objExcel.Workbooks.Open($InputFile, $False, $False)
$Sheet = $Excel.Worksheets.Item(1)
$intLastRow = $Sheet.Cells.Item(65536, 1).End(-4162).Row
For ($i = 2; $i -le $intLastRow; $i++) {
    $Name = $Sheet.Cells.Item($i,1).Text
    $Location = $Sheet.Cells.Item($i,2).Text
    $Comment = $Sheet.Cells.Item($i,3).Text
    $DriverName = $Sheet.Cells.Item($i, 5).Text
    $Shared = $Sheet.Cells.Item($i,6).Text
    If ($Shared -eq "TRUE") { $Shared = $True } Else { $Shared = $False }
    $ShareName = $Sheet.Cells.Item($i,7).Text
    $DeviceID = $Sheet.Cells.Item($i,8).Text
    $Query = "SELECT * FROM Win32_Printer WHERE DeviceID='$DeviceID'"
    Get-WMIObject -Query $Query -ComputerName $ComputerName | ForEach {
        $PropsToChange = ""
        # --- Location property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Location)) -and ([string]::IsNullOrEmpty($Location))) -eq $False) {
            If (($_.Location) -ne $Location) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating location from `"$($_.Location)`" to `"$Location`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Location=`$Location"
        } Else {
            Write-Output "Not changing location from `"$($_.Location)`" to `"$Location`" for device `"$($_.DeviceID)`""
        }
        # --- Comment property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Comment)) -and ([string]::IsNullOrEmpty($Comment))) -eq $False) {
            If (($_.Comment) -ne $Comment) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating comment from `"$($_.Comment)`" to `"$Comment`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Comment=`$Comment"
        } Else {
            Write-Output "Not changing comment from `"$($_.Comment)`" to `"$Comment`" for device `"$($_.DeviceID)`""
        }
        # --- DriverName property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.DriverName)) -and ([string]::IsNullOrEmpty($DriverName))) -eq $False) {
            If (($_.DriverName) -ne $DriverName) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating driver name from `"$($_.DriverName)`" to `"$DriverName`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";DriverName=`$DriverName"
        } Else {
            Write-Output "Not changing driver name from `"$($_.DriverName)`" to `"$DriverName`" for device `"$($_.DeviceID)`""
        }
        # --- Shared property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Shared)) -and ([string]::IsNullOrEmpty($Shared))) -eq $False) {
            If ($($_.Shared) -ne $Shared) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating shared property from `"$($_.Shared)`" to `"$Shared`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Shared=`$Shared"
        } Else {
            Write-Output "Not changing shared property from `"$($_.Shared)`" to `"$Shared`" for device `"$($_.DeviceID)`""
        }
        # --- ShareName property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.ShareName)) -and ([string]::IsNullOrEmpty($ShareName))) -eq $False) {
            If (($_.ShareName) -ne $ShareName) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating sharename from `"$($_.ShareName)`" to `"$ShareName`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";ShareName=`$ShareName"
        } Else {
            Write-Output "Not changing sharename from `"$($_.ShareName)`" to `"$ShareName`" for device `"$($_.DeviceID)`""
        }
        # --- Set all properties ---
        If ($PropsToChange.Length -gt 0) {
            $Expression = "`$_ | Set-WmiInstance -Arguments @{$($PropsToChange.Substring(1))} | Out-Null"
            Invoke-Expression $Expression
        }
        # --- Name property (for renaming) ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Name)) -and ([string]::IsNullOrEmpty($Name))) -eq $False) {
            If (($_.Name) -ne $Name) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Renaming device `"$($_.Name)`" to `"$Name`""
            $_.RenamePrinter($Name) | Out-Null
        } Else {
            Write-Output "Not renaming device `"$($_.Name)`" to `"$Name`""
        }
    } | Out-File "$CurrentFolder\Printer_Change_Log.txt" -Append
}
$Excel.Close($False)
$objExcel.Quit()
Invoke-Item "$CurrentFolder\Printer_Change_Log.txt"
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Sheet)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
Remove-Variable Sheet
Remove-Variable Excel
Remove-Variable objExcel

Select allOpen in new window

The driver that you enter into the Excel sheet must already exist on the server for that to work though.....then it might work.

Regards,

Rob.

Let me check

Thank you that works

Hi Rob

Would that be possible to generate csv output instead xlxs output?

Thx

Yes. Excel automation in Powershell is quite slow due to a lack of compatibility with COM objects. It would be best to raise a new question for that conversion.

Rob.

i will do that . thx

Here is new question
https://www.experts-exchange.com/questions/28664562/Generate-CSV-output-instead-xlsx.html