Posted by: Preetam | May 5, 2008

PowerShell and Excel

This is script is contributed to Frank Koch who created Free book on Powershell. I learnt how to push excel information only through his book. You may like to go through this book as well. Million thanks to Frank.


#This cmdlet picks the servers from workstations.txt one by one, and collects IP details of each server and pushes them #into excel sheet

$WKSs=get-content Workstations.txt                                #Workstation/Server list
$ExcelSheet=new-object -comobject Excel.application    #Creates excel com object
$WorkBook=$ExcelSheet.WorkBooks.add(1)                       #when you first open excel it creates workbook
$WorkSheet=$WorkBook.WorkSheets.item(1)                    #Under book create a worksheet 
$WorkSheet.cells.item(1,1)=”HostName”                            #Creates first row basically heading
$WorkSheet.cells.item(1,2)=”IPAddress”                            #like hostname,IPAddress,SubnetMask,
$WorkSheet.cells.item(1,3)=”Subnet”                                #Default gateway,DNSDomain,MAC,Description
$WorkSheet.cells.item(1,4)=”Default Gateway”                 #DNSSuffix
$WorkSheet.cells.item(1,7)=”DNS Suffix Order”

Foreach($WKS in $WKSs) {
$NICCard=Get-WmiObject win32_networkadapterconfiguration -COMPUTER $WKS
foreach($NIC in $NICCard){
if($NIC.ipenabled -eq $true)
Write-host Hostname => $NIC.DNSHostName
$i=$i+1                                                                                      #Remember here you say go to another column


OutPut in Excel Looks like  





  1. Attempting to put $worksheet.cell.item(2,3) into a variable as integer. How can this be achieved?

    Example: Increment the value of (3,2) by 1, then replace the value on the spreadsheet.

    I was attempting a few
    $Value = $worksheet.cell.item(2,3)
    $worksheet.cell.item(2,3) = $Value

    But it comes out as System.COMObject

  2. I found the answer, but also discovered some more interesting stuff…

    $Value = $worksheet.cell.item(2,3).tointeger()
    $worksheet.cell.item(2,3) = $Value

    Which will output the new incremented result to your excel spreadsheet.

    Then I also found this

    $RandomText = $worksheet.cell.item(1,1).text
    Write-Host “Here’s my text: $RandomText”
    (Output) >> Here’s my text: HostName

    That is different altogether for strings and integers.

    Thought it might help people out there.

  3. The students then choose the project they want to work on. ,

  4. […] found a nice script on the blog post PowerShell and Excel (from the “Powershell,Passion,Persistence and Pursuit” blog).  This script reads a […]

  5. $WorkSheet.cells.item(1,7)=”DNS Suffix Order”
    should be
    $WorkSheet.cells.item(1,8)=”DNS Suffix Order” everything works fine, thanks

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


%d bloggers like this: