Powershell Excel Cookbook Ver 2

DATE : 6/25/2012
COMMENT: This is an updated Version of my Cook Book from Augest of 2008
===============================================================
All errors and mistakes are my own. Thanks to all who have helped
along the way.

One thing I would add is that if you are using anything but a US locale version of Excel some of
these scripts won’t work, especially adding and saving workbooks.

Here is a function that helps with this problem:

From:

Using-Culture -Culture culture -Script {scriptblock}

======================================================

function Remove-ComObject {
 # Requires -Version 2.0
 [CmdletBinding()]
 param()
 end {
  Start-Sleep -Milliseconds 500
  [Management.Automation.ScopedItemOptions]$scopedOpt = 'ReadOnly, Constant'
  Get-Variable -Scope 1 | Where-Object {
   $_.Value.pstypenames -contains 'System.__ComObject' -and -not ($scopedOpt -band $_.Options)
  } | Remove-Variable -Scope 1 -Verbose:([Bool]$PSBoundParameters['Verbose'].IsPresent)
  [gc]::Collect()
 }
}

Function Using-Culture (
[System.Globalization.CultureInfo]$culture,
[ScriptBlock]$script)
{
    $OldCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture
    trap
    {
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
    }
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $culture
    $ExecutionContext.InvokeCommand.InvokeScript($script)
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
} # End Function

Using-Culture en-us {
$xl= New-Object -COM Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$xl.Workbooks.Open("C:\Scripts\PowerShell\test.xls")

$xl.quit() 
Remove-ComObject -Verbose
Start-Sleep -Milliseconds 250
Get-Process Excel
#[System.Runtime.InteropServices.Marshal]::ReleaseComObject($xl)
}
# ======================================================
#_______________________________________________________________________
# How do I create an Excel object? 
 
$xl = new-object -comobject excel.application 

# ________________________________________________________________________ 
#How do I make Excel visible?

$xl.Visible = $true

# ________________________________________________________________________
#
# How do I add a workbook? 
 
$wb = $xl.Workbooks.Add()
 
# By default this adds three empty worksheets
# ________________________________________________________________________
#
#
# How do I open an existing Workbook ?
#
$xl = New-Object -comobject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Open("C:\Scripts\powershell\test.xls")

# ________________________________________________________________________
#
# 
#How do I add a worksheet to an existing workbook? 
 
$xl = new-object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Open("C:\Scripts\test.xls")
$ws = $xl.Sheets.Add()

#
# ________________________________________________________________________
#
# How do I activate a a worksheet?
# Create Excel.Application object
$xl = New-Object -comobject Excel.Application
# Show Excel
$xl.visible = $true
$xl.DisplayAlerts = $False
# Create a workbook
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
# Get sheets
$ws1 = $wb.worksheets | where {$_.name -eq "sheet1"} #<------- Selects sheet 1
$ws2 = $wb.worksheets | where {$_.name -eq "sheet2"} #<------- Selects sheet 2
$ws3 = $wb.worksheets | where {$_.name -eq "sheet3"} #<------- Selects sheet 3
# Activate sheet 1
$ws1.activate()
Start-Sleep 1 
# Activate sheet 2
$ws2.activate()
Start-Sleep 1 
# Activate sheet 3
$ws3.activate()
# _________________________________________________________________
# How do I change the value of a cell? 
 
$ws1.cells.Item(1,1).Value() = "x" 
or
$ws1.cells.Item($row,$col).Value() = "x"  # where $row = the row your on and col = the column
# ________________________________________________________________________
#
# How do I select an entire row?
$range = $ws1.Cells.Item(1,1).EntireRow
$range.font.bold = $true # sets the top row to bold
#
#
# How do I Autofit the entire worksheet?
[void]$ws1.cells.entireColumn.Autofit()
#
#
# How do I name a worksheet?
$Date = (Get-Date -format "MM-dd-yyyy")
$xl.Worksheets.item(1).name = $Date
#
#
# How do I Find a specified cell? 
#

$xlCellTypeLastCell = 11

$used = $ws.usedRange 
$lastCell = $used.SpecialCells($xlCellTypeLastCell) 
$row = $lastCell.row # goes to the last used row in the worksheet

for ($i = 1; $i -le $row; $i++) {
If ($ws1.cells.Item(1,2).Value() = "y") {
# "do something"
          }
}

# And another way:
#
$mainRng = $ws.usedRange
$mainRng.Select()
$objSearch = $mainRng.Find("Grand Total")
$objSearch.Select()
#
#
# Find a very specific item:

$Rng = $ws1.range("AS:AS")
$oSearch = "IBSS-Corporate-OSE-PrdSupp-Win"
#search
$ySearch = $xl.WorksheetFunction.Match($oSearch,$Rng,0) # gives you the ROW # of the found cell
$range3 = $ws1.range("AS$ySearch")
[void]$range3.select()
$z = $ySearch - 1
Write-Host $z 
# [void]$range3.Delete()
$range3 = $ws1.range("A2:CI$z")
[void]$range3.select()
[void]$range3.Delete()
#
#
# How do I match an Item?
#
$used = $ws1.usedRange 
$Rng = $ws1.range("A:A")
$oSearch = $Null
$ySearch = $Rng.Find($oSearch)
$zSearch = $ySearch.Row
$R = $zSearch

For ($i = $R; $i -ge 2; $i--)  {
  Write-Progress -Activity "Searching $R Old's..." `
     -PercentComplete ($i/$R*100) -CurrentOperation `
     "$i of $R to go" -Status "Please wait."
	  $Rng = $ws1.Cells.Item($i, 1).value()
If ($Rng -match '\bold\b|-new|zz\.|-INACTIVE|-DELETE|\.\.+') {
# Match at Begining and end of word '\b   \b' <-- looking for old? '\bold\b' 
# must put a \ before a period \. othewise will catch every charactor.
   Write-Host $Rng
   $Range = $ws1.Cells.Item($i, 1).EntireRow
     [void]$Range.Select()
     [void]$Range.Delete()
     $R = $R - 1
      } 
    }
     Write-Progress -Activity "Searching -Old's..." `
  -Completed -Status "All done." 

# ________________________________________________________________________
#
# How do I delete a row in Excel?
#

Function DelRow($y) {	
	$Range = $ws1.Cells.Item($y, 1).EntireRow
	[void]$Range.Select()
	[void]$Range.Delete()
}
DelRow($y)

# How do I find and delete empty rows in Excel?

Function DeleteEmptyRows {
$used = $ws.usedRange 
$lastCell = $used.SpecialCells($xlCellTypeLastCell) 
$row = $lastCell.row 

for ($i = 1; $i -le $row; $i++) {
    If ($ws.Cells.Item($i, 1).Value() -eq $Null) {
        $Range = $ws.Cells.Item($i, 1).EntireRow
        $Range.Delete()
        }
    }
} 

$xlCellTypeLastCell = 11 

$xl = New-Object -comobject excel.application 
$xl.Visible = $true 
$xl.DisplayAlerts = $False 
$wb = $xl.Workbooks.Open("C:\Scripts\Test.xls") # <-- Change as required!
$ws = $wb.worksheets | where {$_.name -eq "Servers" } # <-- Or Sheet1 or Whatever 

DeleteEmptyRows   # <—Call Function 

 
# ________________________________________________________________________

#
#
# How do I set a range with variables? 
 
$ws1.range("a${Sumrow}:b$Sumrow").font.bold = "true" 

# separate the : from the $ with {} on the left hand side
# ________________________________________________________________________
#
# How do I Set range to a value?
 
$range4=$ws.range("3:3")
$range4.cells="Row 3"

# ________________________________________________________________________
#
# How do I list the workbook's name? 
 
$wb.Name

# ______________________________________________________________
#
# How do I find the last used row number?

$xlCellTypeLastCell = 11
$used = $ws.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$lastrow = $lastCell.row 

# Or

$mainRng = $ws1.UsedRange.Cells 
$RowCount = $mainRng.Rows.Count  
$R = $RowCount 

# ________________________________________________________________________
#
# How do I find the last used column number? 
#This works for columns and rows 
 
$mainRng = $ws1.UsedRange.Cells 
$ColCount = $mainRng.Columns.Count 
$RowCount = $mainRng.Rows.Count  
$xRow = $RowCount
$xCol = $ColCount 

# ________________________________________________________________________
#
# How do I loop through a range of cells by row number? 
 
$xl = New-Object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False 
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
for ($row = 1; $row -lt 11; $row++)
{
    $ws.Cells.Item($row,1) = $row
}
# ________________________________________________________________________
#
# How do I get today's date and format it as a string? 
 
$m = (get-date).month
$d = (get-date).day
$y = [string] (get-date).year
$y = $y.substring($y.length - 2, 2)
$f = "C:\Scripts\" + $m + "-" + $d + "-" + $y + ".xlsx"
$wb.SaveAs($F) # C:\Scripts\6-18-10.xlsx 

# OR

$Date = (Get-Date -format "MM-dd-yyyy")

#
# ________________________________________________________________________
#
# How do I write a list of files to Excel? 
 
$xl = new-object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False 
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$row = 1
$s = dir Z:\MBSA_Report\ScanData\*.mbsa
$s | foreach -process {
    $ws.Cells.Item($row,1) = $_;
    $row++
}

#
# this takes a long file name with spaces and splits it up,
# It then picks out the 3rd element and writes it out.
# The 3rd element is [2] because the first one is [0].
# sample file name: AD-Dom - 3IDCT001 (5-21-2010 4-28 PM).mbsa 
 
$xl = new-object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False 
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$row = 1
dir Z:\MBSA_Report\ScanData\*.mbsa |
ForEach-Object {
$FileName = $_.name
$N = $FileName.tostring()
$E = $N.split()
$F = $E[2]
$ws.Cells.Item($row,1) = $f;
    $row++
}
#Or for the whole file name
$ws.Cells.Item($row,1) = $_;

# ________________________________________________________________________
#
# How do I write a list of processes to Excel? 
 
function Release-Ref ($ref) {
#[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($ref)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
# -----------------------------------------------------
$xl = New-Object -comobject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False 
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$range = $ws.Cells.Item(1,1)
$row = 1
$s = Get-Process | Select-Object name
$s | foreach -process {
    $range = $ws.Cells.Item($row,1);
    $range.Value = $_.Name;
    $row++ } 
$wb.SaveAs("C:\Scripts\Get_Process.xls")
Release-Ref $range
Release-Ref $ws
Release-Ref $wb
$xl.Quit()
Release-Ref $xl
#***For a remote machine try
$strComputer = (remote machine name)
$P = gwmi win32_process -comp $strComputer

# ________________________________________________________________________
#
# How do I write the command history to Excel? 
 
function Release-Ref ($ref) {
#[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($ref)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}

$xl = New-Object -comobject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False 
$wb = $excel.Workbooks.Add()
$ws = $workbook.Worksheets.Item(1)
$range = $worksheet.Cells.Item(1,1)
$row = 1
$s = Get-History | Select-Object CommandLine $s | foreach -process { `
$range = $worksheet.Cells.Item($row,1); `
$range.Value = $_.CommandLine; `
$row++ }
$xl.DisplayAlerts = $False
$wb.SaveAs("C:\Scripts\Get_CommandLine.xls")
Release-Ref $range
Release-Ref $ws
Release-Ref $wb
$xl.Quit()
Release-Ref $xl

# ________________________________________________________________________
#
# How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?
# Script name: ConvertTilde.ps1
# Created on: 2007-01-06
# Author: Kent Finkle
# Purpose: How Can I Convert a Tilde-Delimited File to Microsoft Excel Format? 
 
$s = gc C:\Scripts\Test.txt
$s = $s -replace("~","`t")
$s | sc C:\Scripts\Test.txt
$xl = new-object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Open("C:\Scripts\Test.txt")

# ________________________________________________________________________
#
# How can I add Validation to an Excel Worksheet?
#
# $comments = @'
# Script name: Add-Validation.ps1
# Created on: Wednesday, September 19, 2007
# Author: Kent Finkle
# Purpose: How can I use Windows Powershell to Add Validation to an
# Excel Worksheet?
# '@
#-----------------------------------------------------

function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
} # End Function 
$xlValidateWholeNumber = 1
$xlValidAlertStop = 1
$xlBetween = 1
$xl = new-object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False 
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$r = $ws.Range("e5")
$r.Validation.Add($xlValidateWholeNumber,$xlValidAlertStop, $xlBetween, "5", "10")
$r.Validation.InputTitle = "Integers"
$r.Validation.ErrorTitle = "Integers"
$r.Validation.InputMessage = "Enter an integer from five to ten"
$r.Validation.ErrorMessage = "You must enter a number from five to ten" 
$a = Release-Ref $r
$a = Release-Ref $ws
$a = Release-Ref $wb
$a = Release-Ref $xl

# ________________________________________________________________________
#
# How do I add a Chart to an Excel Worksheet? 
 
$xRow = 1
$yrow = 8
$xl = New-Object -c excel.application
$xl.visible = $true
$xl.DisplayAlerts = $False 
$wb = $xl.workbooks.add()
$ws = $wb.sheets.item(1)
1..8 | % { $ws.Cells.Item(1,$_) = $_ }
1..8 | % { $ws.Cells.Item(2,$_) = 9-$_ }
$range = $ws.range("a${xrow}:h$yrow")
$range.activate
# create and assign the chart to a variable
$ch = $xl.charts.add()   # This will open a new sheet
$ch = $ws.shapes.addChart().chart # This will put the Chart in the selected WorkSheet
$ch.chartType = 58
$ch.setSourceData($range)
$ch.HasTitle = $true
$ch.ChartTitle.Text = "Count of KB's"
$ch.export("C:\test.jpg")
$xl.quit() 
# excel has 48 chart styles, you can cycle through all
1..48 | % {$ch.chartStyle = $_; $xl.speech.speak("Style $_"); sleep 1}
$ch.chartStyle = 27      # <-- use the one you like 

# And another Chart sample:

Function XLcharts {
$xlColumnClustered = 51
$xlColumns = 2
$xlLocationAsObject = 2
$xlCategory = 1
$xlPrimary = 1
$xlValue = 2
$xlRows = 1
$xlLocationAsNewSheet = 1
$xlRight = -4152
$xlBuiltIn =21
$xlCategory = 1 
$xl = New-Object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False 
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$ws = $wb.Sheets.Add()
$ws.Cells.Item(1, 2) =  "Jan"
$ws.Cells.Item(1, 3) =  "Feb"
$ws.Cells.Item(1, 4) =  "Mar"
$ws.Cells.Item(2, 1) =  "John"
$ws.Cells.Item(3, 1) =  "Mae"
$ws.Cells.Item(4, 1) =  "Al"
$ws.Cells.Item(2, 2) =  100
$ws.Cells.Item(2, 3) =  200
$ws.Cells.Item(2, 4) =  300
$ws.Cells.Item(3, 2) =  400
$ws.Cells.Item(3, 3) =  500
$ws.Cells.Item(3, 4) =  600
$ws.Cells.Item(4, 2) =  900
$ws.Cells.Item(4, 3) =  800
$ws.Cells.Item(4, 4) =  700 
$Range = $ws.range("A1:D4")
$ch = $xl.charts.add()
$ch.chartType = 58
$ch.name ="Bar Chart"
$ch.Tab.ColorIndex = 3
$ch.setSourceData($Range)
[void]$ch.Location, $xlLocationAsObject, "Bar Chart"
$ch.HasTitle = $False
$ch.Axes($xlCategory, $xlPrimary).HasTitle = $False
$ch.Axes($xlValue, $xlPrimary).HasTitle = $False 
$ch2 = $xl.Charts.Add() | Out-Null
$ch2.HasTitle = $true
$ch2.ChartTitle.Text = "Sales"
$ch2.Axes($xlCategory).HasTitle = $true
$ch2.Axes($xlCategory).AxisTitle.Text = "1st Quarter"
$ch2.Axes($xlValue).HasTitle = $True
$ch2.Axes($xlValue).AxisTitle.Text = "Dollars"
[void]$ch2.Axes($xlValue).Select
$ch2.name ="Columns with Depth"
$ch2.Tab.ColorIndex = 5
[void]$ws.cells.entireColumn.Autofit()
} # End Function
# ________________________________________________________________________
#
# How do I Move and resize a chart in an Excel Worksheet? 

$xl = New-Object -comobject Excel.Application	# Opens Excel and 3 empty Worksheets
# Show Excel
$xl.visible = $true
$xl.DisplayAlerts = $False
# Open a workbook
$wb = $xl.workbooks.add() 
#Create Worksheets
$ws = $wb.Worksheets.Item(1)
1..8 | % { $ws.Cells.Item(1,$_) = $_ }			# adds some data
1..8 | % { $ws.Cells.Item(2,$_) = 9-$_ }		# adds some data
# < ----- This is the good part ------------------------------------------------------->
$range = $ws.range("a${xrow}:h$yrow")			# sets the Data range we want to chart
# create and assign the chart to a variable
#$ch = $xl.charts.add()							# This will open a new sheet
$ch = $ws.shapes.addChart().chart				# This will put the Chart in the selected WorkSheet
$ch.chartType = 58								# Select Chart Type
$ch.setSourceData($range)						# Create the Chart
$ch.HasTitle = $true
$ch.ChartTitle.Text = "Sales"
$RngToCover = $ws.Range("D5:J19")				# This is where we want the chart
$ChtOb = $ch.Parent								# This selects the curent Chart
$ChtOb.Top = $RngToCover.Top					# This moves it up to row 5
$ChtOb.Left = $RngToCover.Left					# and to column D 
$ChtOb.Height = $RngToCover.Height				# resize This sets the height of your chart to Rows 5 - 19
$ChtOb.Width = $RngToCover.Width				# resize This sets the width to Columns D - J
# ________________________________________________________________________
#
# How do I sort a column in an Excel Worksheet? 
 
$xlSummaryAbove = 0
$xlSortValues = $xlPinYin = 1
$xlAscending = 1
$xlDescending = 2
$xlYes = 1 
$xl = New-Object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False 
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1) 
# one-column sort

$R = $ws.UsedRange
$r2 = $ws.Range("B2") # Sorts on Column B and leaves Header alone 
$a = $r.sort($r2, $xlAscending) 

# two-column sort

[void]$ws1.Activate()
$last = $ws1.UsedRange.SpecialCells(11).Address($False,$false) 
$range1 = $ws1.range("A2:$last" )
$range2 = $ws1.range("D2") 
$range3 = $ws1.range("A2")
#two-column sort --> works 
$ws1.sort.sortFields.clear()
[void]$ws1.sort.sortFields.add($range2, $xlSortOnValues, $xlAscending,`
$xlSortNormal)
[void]$ws1.sort.sortFields.add($range3, $xlSortOnValues, $xlAscending,`
$xlSortNormal)
$ws1.sort.setRange($range1)
$ws1.sort.header = $xlNo
$ws1.sort.orientation = $xlTopToBottom
$ws1.sort.apply()
#-----------------------------------------------------
# How do I use xlConstants? 
$xlOpenXMLWorkbook = 51
$xlAutomatic=-4105
$xlBottom = -4107
$xlCenter = -4108
$xlContext = -5002
$xlContinuous=1
$xlDiagonalDown=5
$xlDiagonalUp=6
$xlEdgeBottom=9
$xlEdgeLeft=7
$xlEdgeRight=10
$xlEdgeTop=8
$xlInsideHorizontal=12
$xlInsideVertical=11
$xlNone=-4142
$xlThin=2 
$xl = new-object -com excel.application
$xl.visible=$true
$wb = $xl.workbooks.open("d:\book1.xls")
$ws = $wb.worksheets | where {$_.name -eq "sheet1"}
$selection = $ws.range("A1:D1")
$selection.select() 
$selection.HorizontalAlignment = $xlCenter
$selection.VerticalAlignment = $xlBottom
$selection.WrapText = $false
$selection.Orientation = 0
$selection.AddIndent = $false
$selection.IndentLevel = 0
$selection.ShrinkToFit = $false
$selection.ReadingOrder = $xlContext
$selection.MergeCells = $false
$selection.Borders.Item($xlInsideHorizontal).Weight = $xlThin 

## -----------------------------------------------------------
# How do I set the column width ? 

$ws.columns.item(1).columnWidth = 50
$ws.columns.item('a').columnWidth = 50
# Or
$ws.cells.item(1).columnWidth = 50
$ws.range('a:a').columnwidth = 50

## -----------------------------------------------------------
# How do I center a column?  
## You can try this to center a column: 
 
[reflection.assembly]::loadWithPartialname("Microsoft.Office.Interop.Excel") |
Out-Null
$xlConstants = "microsoft.office.interop.excel.Constants" -as [type] 
 
$ws.columns.item("F").HorizontalAlignment = $xlConstants::xlCenter
$ws.columns.item("K").HorizontalAlignment = $xlConstants::xlCenter

 
# The next four lines of code create four enumeration types.
# Enumeration types are used to tell Excel which values are allowed
# for specific types of options. As an example, xlLineStyle enumeration
# is used to determine the kind of line to draw: double, dashed, and so on.
# These enumeration values are documented on MSDN.
# To make the code easier to read, we create shortcut aliases for each
# of the four enumeration types we will be using.
# Essentially, we're casting a string that represents the name of the
# enumeration to a [type]. This technique is actually a pretty cool trick:
# http://technet.microsoft.com/en-us/magazine/2009.01.heyscriptingguy.aspx 
 
$lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
$colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
$chartType = "microsoft.office.interop.excel.xlChartType" -as [type] 

For($b = 1 ; $b -le 2 ; $b++)
{
$ws.cells.item(1,$b).font.bold = $true
$ws.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
$ws.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
$ws.cells.item(1,$b).borders.weight = $borderWeight::xlMedium
}
$workbook.ActiveChart.chartType = $chartType::xl3DPieExploded
$workbook.ActiveChart.SetSourceData($range) 

# ________________________________________________________________________
#
# How do I use autofill in excel?
 
$xlFillWeekdays = 6 
$xl = New-Object -com excel.application
$xl.visible=$true
$wb = $xl.workbooks.add()
$ws = $wb.worksheets | where {$_.name -eq "sheet1"}
$range1= $ws.range("A1")
$range1.value() = (get-date).toString("d")
$range2 = $ws.range("A1:A25")
$range1.AutoFill($range2,$xlFillWeekdays)
$range1.entireColumn.Autofit() 
# Another example:
$xlCellTypeLastCell = 11
$xl = new-object -com excel.application
$xl.visible=$true
$wb = $xl.workbooks.add()
$ws = $wb.worksheets | where {$_.name -eq "sheet1"} 
$used = $ws.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$lastrow = $lastCell.row 
$ws.Cells.Item(2,1).FormulaR1C1 = "=CONCATENATE(C[+1],C[+2],C[+3])"
$range1= $ws.range("A2")
$r = $ws.Range("A2:A$lastrow")
$range1.AutoFill($r) | Out-Null
[void]$range1.entireColumn.Autofit() 
$wb.close()
$xl.quit()

# ________________________________________________________________________
#
# How to get a range and format it in excel?
 
# get-excelrange.ps1
# opens an existing workbook in Excel 2007, using PowerShell
# and turns a range bold # Thomas Lee - t...@psp.co.uk
# Create base object
$xl = new-object -comobject Excel.Application 
# make Excel visible
$xl.visible = $true
$xl.DisplayAlerts = $False 
# open a workbook
$wb = $xl.workbooks.open("C:\Scripts\powershell\test.xls") 
 
# Get sheet1
$ws = $wb.worksheets | where {$_.name -eq "sheet1"} 
 
# Make A1-B1 bold
$range = $ws.range("A1:B1")
$range.font.bold = "true" 
 
# Make A2-B2 italic
$range2 = $ws.range("A2:B2")
$range2.font.italic = "true" 
 
# Set range to a value
$range4=$ws.range("3:3")
$range4.cells="Row 3"

# Set range to a Font size
$range3=$ws.range("A2:B2")
$Range3.font.size=24 

# now format an entire row

$range4=$ws.range("3:3")

$range4.font.italic="$true"
$range4.font.bold=$true
$range4.font.size=10
$range4.font.name="Comic Sans MS" 
# now format a Range of cells
$ws.Range("D1:F5").NumberFormat = "#,##0.00"

# ______________________________________________________________________
#
# How do I add a comment to a cell in Excel? 
 
$xl = New-Object -com Excel.Application
$xl.visible = $true
$xl.DisplayAlerts = $False 
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$ws.Cells.Item(1,1) = “A value in cell A1.”
[void]$ws.Range("A1").AddComment()
[void]$ws.Range("A1").comment.Visible = $False
[void]$ws.Range("A1").Comment.text("OldDog: `r this is a comment")
[void]$ws.Range("A2").Select 

# The 'r adds a line feed after the comment's author. This is required!
# ________________________________________________________________________
#
# How do I copy and Paste in Excel (special)?

$xl = New-Object -comobject Excel.Application
# Show Excel
$xl.visible = $true
$xl.DisplayAlerts = $False 
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1) 
$xlPasteValues = -4163          # Values only, not formulas
$xlCellTypeLastCell = 11        # to find last used cell
$used = $ws.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$row = $lastCell.row
$range = $ws.UsedRange
[void]$ws.Range("A8:F$row").Copy()
[void]$ws.Range("A8").PasteSpecial(-4163) 

# __________________________________________________________________________
#
# How do I Add Worksheets, name them and save as today's date? 
#****************************************** 
# get today's date and format it as a string. 
 
$m = (get-date).month
$d = (get-date).day
$y = [string] (get-date).year
$y = $y.substring($y.length - 2, 2)
$f = "C:\Scripts\" + $m + "-" + $d + "-" + $y + ".xlsx" 
# Or
$Date = (Get-Date -format "MM-dd-yyyy")
$f = "C:\Scripts\$Date.xlsx"

# Create Excel.Application object
$xl = New-Object -comobject Excel.Application
# Show Excel
$xl.visible = $true
$xl.DisplayAlerts = $False 
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1) 
# this will add 9 sheets to the default 3 for a total of 12 sheets 
for ($i = 0; $i -le 8; $i++) {
$ws = $wb.Sheets.Add() } 
# Now we name our new sheets 
$xl.Worksheets.item(1).name = "Jan"
$xl.Worksheets.item(2).name = "Feb"
$xl.Worksheets.item(3).name = "Mar"
$xl.Worksheets.item(4).name = "Apr"
$xl.Worksheets.item(5).name = "May"
$xl.Worksheets.item(6).name = "June"
$xl.Worksheets.item(7).name = "July"
$xl.Worksheets.item(8).name = "Aug"
$xl.Worksheets.item(9).name = "Sept"
$xl.Worksheets.item(10).name = "Oct"
$xl.Worksheets.item(11).name = "Nov"
$xl.Worksheets.item(12).name = "Dec" 
# and here we save it 
$wb.SaveAs($F) 

#*******************************************
# How do I find duplicate entries in Excel?
# This Function creates a spreadsheet with some
# duplicate names and then highlights the Dups in Blue
 
Function XLFindDups {
$xlExpression = 2
$xlPasteFormats = -4122
$xlNone = -4142
$xlToRight = -4161
$xlToLeft = -4159
$xlDown = -4121
$xlShiftToRight = -4161
$xlFillDefault = 0
$xlSummaryAbove = 0
$xlSortValues = $xlPinYin = 1
$xlAscending = 1
$xlDescending = 2
$xlYes = 1
$xlTopToBottom = 1
$xlPasteValues = -4163          # Values only, not formulas
$xlCellTypeLastCell = 11        # to find last used cell 
$xl = new-object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$ws.name = "Concatenate" 
$ws.Tab.ColorIndex = 4 
$ws.Cells.Item(1,1) = "FirstName"
$ws.Cells.Item(1,2) = "MI"
$ws.Cells.Item(1,3) = "LastName"
$ws.Cells.Item(2,1) = "Jesse"
$ws.Cells.Item(2,2) = "L"
$ws.Cells.Item(2,3) = "Roberts"
$ws.Cells.Item(3,1) = "Mary"
$ws.Cells.Item(3,2) = "S"
$ws.Cells.Item(3,3) = "Talbert"
$ws.Cells.Item(4,1) = "Ben"
$ws.Cells.Item(4,2) = "N"
$ws.Cells.Item(4,3) = "Smith"
$ws.Cells.Item(5,1) = "Ed"
$ws.Cells.Item(5,2) = "S"
$ws.Cells.Item(5,3) = "Turner"
$ws.Cells.Item(6,1) = "Mary"
$ws.Cells.Item(6,2) = "S"
$ws.Cells.Item(6,3) = "Talbert"
$ws.Cells.Item(7,1) = "Jesse"
$ws.Cells.Item(7,2) = "L"
$ws.Cells.Item(7,3) = "Roberts"
$ws.Cells.Item(8,1) = "Joe"
$ws.Cells.Item(8,2) = "L"
$ws.Cells.Item(8,3) = "Smith"
$ws.Cells.Item(9,1) = "Ben"
$ws.Cells.Item(9,2) = "A"
$ws.Cells.Item(9,3) = "Smith" 
$used = $ws.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$lastrow = $lastCell.row 
$range4=$ws.range("2:2")
$range4.Select() | Out-Null
$xl.ActiveWindow.FreezePanes = $true
$ws.cells.EntireColumn.AutoFit() | Out-Null
$range1 = $ws.Range("A1").EntireColumn
$range1.Insert($xlShiftToRight) | Out-Null
$range1.Select() | Out-Null
$ws.Cells.Item(1, 1) = "Concat"
$r2 = $ws.Range("A2")
$r2.Select() | Out-Null
$ws.Cells.Item(2,1).FormulaR1C1 = "=CONCATENATE(C[+1],C[+2],C[+3])"
$range1= $ws.range("A2")
$r = $ws.Range("A2:A$lastrow")
$range1.AutoFill($r) | Out-Null
$range.EntireColumn.AutoFit() | Out-Null
$select = $range1.SpecialCells(11).Select()  | Out-Null
$ws.Range("A2:A$lastrow").Copy()| Out-Null
$ws1 = $wb.Sheets.Add()
$ws1.name = "FindDups"
$ws1 = $wb.worksheets | where {$_.name -eq "FindDups"}
$ws1.Tab.ColorIndex = 5
$ws1.Select() | Out-Null
[void]$ws1.Range("A2").PasteSpecial(-4163)
$ws1.Range("A1").Select() | Out-Null 
$objRange = $xl.Range("B1").EntireColumn
[void] $objRange.Insert($xlShiftToRight) 
$ws1.Cells.Item(1, 2) = "Dups"
$range = $ws.range("B1:D$lastrow")
$range.copy() | Out-Null
[void]$ws1.Range("C1").PasteSpecial(-4163) 
$ws1.Cells.Item(2,2).FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])>1"
$range1= $ws1.range("B2")
$range2 = $ws1.range("B2:B$lastrow")
[void]$range1.AutoFill($range2,$xlFillDefault) 
# Thnaks to Wolfgang Kais for the following:
$xl.Range("B2").Select() | Out-Null
$xl.Selection.FormatConditions.Delete()
$xl.Selection.FormatConditions.Add(2, 0, "=COUNTIF(A:A,A2)>1") | Out-Null
$xl.Selection.FormatConditions.Item(1).Interior.ColorIndex = 8
$xl.Selection.Copy() | Out-Null
$xl.Columns.Item("B:B").Select() | Out-Null
$xl.Range("B2").Activate() | Out-Null
$xl.Selection.PasteSpecial(-4122, -4142, $false, $false) | Out-Null 
$r = $ws1.UsedRange
$r2 = $ws1.Range("B2")
$r3 = $ws1.Range("E2")
$r4 = $ws1.Range("C2") 
$a = $r.Sort($r2,$xlDescending,$r3,$null,$xlAscending, `
$r4,$xlAscending,$xlYes) 
$ws1.Application.ActiveWindow.FreezePanes=$true
[void]$ws1.cells.entireColumn.Autofit()
$s = $xl.Range("A1").EntireColumn
$s.Hidden = $true
$ws1.Range("B2").Select() | Out-Null
}

# ==============================================================================================
# How do I show all 57 Colors in Excel?

$xl = new-object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False 
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$row = 1
$i = 1
For($i = 1; $i -lt 57; $i++){
$ws.Cells.Item($row, 1) = "'$'ws.Cells.Item($row, 2).Font.ColorIndex = " + $row
$ws.Cells.Item($row, 2).Font.ColorIndex = $row
$ws.Cells.Item($row, 2) = "test " + $row
$row++
}
[void]$ws.cells.entireColumn.Autofit() 
 
# ==============================================================================================
# How do I insert a Hyperlink into an Excel Spreadsheet? 
 
function Release-Ref ($info) {
    foreach ( $p in $args ) {
        ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
        [System.__ComObject]$p) -gt 0)
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
    }
} # End Function

Function XLHyperlinks {
$link = "http://www.microsoft.com/technet/scriptcenter" 
$xl = new-object -comobject excel.application
$xl.Visible = $true
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1) 
$ws.Cells.Item(1, 1).Value() = "Script Center"
$r = $ws.Range("A1")
$objLink = $ws.Hyperlinks.Add($r, $link)
$a = Release-Ref $r $ws $wb $xl
} # End Function
#
# Link to another sheet

$xl = New-Object -comobject Excel.Application

$xl.Visible = $True

$wb = $xl.Workbooks.Add()

$wb.Worksheets.Item(1).Hyperlinks.Add( `
  $wb.Worksheets.Item(1).Cells.Item(1,1) , `
  "" , "Sheet2!C4", "", "Link to sheet2")

# ________________________________________________________________________ 
#
# How do I sum a column in Excel?
#

Function xlSum {
$range = $ws1.usedRange
$row = $range.rows.count # Takes you to the last used row
$Sumrow = $row + 1
$r = $ws1.Range("A2:A$row") # select the column to Add up
$functions = $xl.WorkSheetfunction
$ws.cells.item($Sumrow,1) = $functions.sum($r) # this uses the Excel sum function
$rangeString = $r.address().tostring() -replace "\$",'' # convert formula to Text
$ws.cells.item($Sumrow,2) = "Sum $rangeString" # Print formula in Cell B & last row + 1
$ws1.cells.item($Sumrow,1).Select()
$ws1.range("a${Sumrow}:b$Sumrow").font.bold = "true" # seperate the : from the $ with {}
$ws1.range("a${Sumrow}:b$Sumrow").font.size=12 # Changes the font size to 12 points
[void]$range.entireColumn.Autofit()
} # End Function

# ________________________________________________________________________
#
# How do I SubTotal a column in an Excel Worksheet?
#
#
#  Sample Spreadsheet
#          mon    tue    wed
# eggs        1     1      1
# ham         5     5      5
# spam       1     4      7
# spam        2     5      8
# spam        3     6      9
#
# Code to sub total
$xlSum = -4157
$range = $xl.range("A1:D6")
$range.Subtotal(1,-4157,(2,3,4),$true,$False,$true) 

#     Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _
#     Replace:=True, PageBreaks:=False, SummaryBelowData:=True
# ________________________________________________________________________
#
# In this example Subtotals are sums grouped by each change in field 1 "Salesperson"
# with Subtotals of field 2 "Amount".
# Details of Subtotal function
# SubTotal GroupBy =2, Function =XLSum, TotalList =Array(1),
# Replace =False, PageBreaks =False, SummaryBelowData =$True 

Function XLSubtotals {
$xlExpression = 2
$xlPasteFormats = -4122
$xlNone = -4142
$xlToRight = -4161
$xlToLeft = -4159
$xlDown = -4121
$xlShiftToRight = -4161
$xlFillDefault = 0
$xlSummaryAbove = 0
$xlSortValues = $xlPinYin = 1
$xlAscending = 1
$xlDescending = 2
$xlYes = 1
$xlTopToBottom = 1
$xlPasteValues = -4163          # Values only, not formulas
$xlCellTypeLastCell = 11        # to find last used cell
$xlSum = -4157 
$xl = New-Object -comobject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$ws = $wb.Sheets.Add()
$xl.Worksheets.item(1).name = "Detail"
$xl.Worksheets.item(2).name = "ShowLevels1"
$xl.Worksheets.item(3).name = "ShowLevels2"
$xl.Worksheets.item(4).name = "ShowLevels3" 
$ws1 = $wb.worksheets | where {$_.name -eq "Detail"}
$ws2 = $wb.worksheets | where {$_.name -eq "ShowLevels1"} #------- Selects sheet 6
$ws3 = $wb.worksheets | where {$_.name -eq "ShowLevels2"} #------- Selects sheet 5
$ws4 = $wb.worksheets | where {$_.name -eq "ShowLevels3"} #------- Selects sheet 4 
$ws1.Tab.ColorIndex = 8
$ws1.Tab.ColorIndex = 5
$ws2.Tab.ColorIndex = 6
$ws3.Tab.ColorIndex = 7 
$ws1.Cells.Item(1,2) = "Amount"
$ws1.Cells.Item(1,1) = "SalesPerson"
$ws1.Cells.Item(2,2) = 7324
$ws1.Cells.Item(2,1) = "Jack"
$ws1.Cells.Item(3,2) = 294
$ws1.Cells.Item(3,1) = "Elizabeth"
$ws1.Cells.Item(4,2) = 41472
$ws1.Cells.Item(4,1) = "Renee"
$ws1.Cells.Item(5,2) = 25406
$ws1.Cells.Item(5,1) = "Elizabeth"
$ws1.Cells.Item(6,2) = 20480
$ws1.Cells.Item(6,1) = "Jack"
$ws1.Cells.Item(7,2) = 11294
$ws1.Cells.Item(7,1)= "Renee"
$ws1.Cells.Item(8,2) = 982040
$ws1.Cells.Item(8,1) = "Elizabeth"
$ws1.Cells.Item(9,2) = 2622368
$ws1.Cells.Item(9,1) = "Jack"
$ws1.Cells.Item(10,2) = 884144
$ws1.Cells.Item(10,1) = "Renee" 
$ws1.Range("B2").Select() | Out-Null
$ws1.Application.ActiveWindow.FreezePanes=$true
[void]$ws1.cells.entireColumn.Autofit() 
$ws1.Range("A1").Select() | Out-Null
$r = $ws.Range("A2:A10")
$r2 = $ws.Range("A2") # Sorts on Column B and leaves Header alone
$a = $r.sort($r2, $xlAscending) 
$range1 = $ws1.Range("A1:B1").EntireColumn
$Range1.Select() | Out-Null
#$ws.Range.SpecialCells(11)).Select()
$Range1.Copy()
$ws1.Range("A1").Select() | Out-Null 
$ws2.Select() | Out-Null
[void]$ws2.Range("A1").PasteSpecial(-4163)
$ws3.Select() | Out-Null
[void]$ws3.Range("A1").PasteSpecial(-4163)
$ws4.Select() | Out-Null
[void]$ws4.Range("A1").PasteSpecial(-4163) 
$ws2.Select() | Out-Null
$range = $xl.range("A1:B10")
$range.Subtotal(1,-4157,(2),$true,$False,$true)
$ws2.Outline.ShowLevels(1)
[void]$ws1.cells.entireColumn.Autofit()
$ws2.Range("A1").Select() | Out-Null 
$ws3.Select() | Out-Null
$range = $xl.range("A1:B10")
$range.Subtotal(1,-4157,(2),$true,$False,$true)
$ws3.Outline.ShowLevels(2)
[void]$ws1.cells.entireColumn.Autofit()
$ws3.Range("A1").Select() | Out-Null 
$ws4.Select() | Out-Null
$range = $xl.range("A1:B10")
$range.Subtotal(1,-4157,(2),$true,$False,$true)
$ws4.Outline.ShowLevels(3) 
$ws1.Select() | Out-Null
[void]$ws1.cells.entireColumn.Autofit()
$ws1.Range("A1").Select() | Out-Null
} # End Function

#---------------------------------------------------
#
#How do I set up Auto Filters in Excel?
#This function sets up a spreadsheet and then sets Auto filters 
 
Function XLAutoFilter {
$xlTop10Items = 3
$xlTop10Percent = 5
$xlBottom10Percent = 6
$xlBottom10Items = 4
$xlAnd = 1
$xlOr = 2
$xlNormal = -4143
$xlPasteValues = -4163          # Values only, not formulas
$xlCellTypeLastCell = 11        # to find last used cell 
$xl = New-Object -comobject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$ws = $wb.Sheets.Add()
$ws = $wb.Sheets.Add() 
$ws1 = $wb.worksheets | where {$_.name -eq "Sheet1"}        #------- Selects sheet 1
$ws2 = $wb.worksheets | where {$_.name -eq "Sheet2"}         #------- Selects sheet 2
$ws3 = $wb.worksheets | where {$_.name -eq "Sheet3"}         #------ Selects sheet 3
$ws4 = $wb.worksheets | where {$_.name -eq "Sheet4"}         #------- Selects sheet 4
$ws5 = $wb.worksheets | where {$_.name -eq "Sheet5"}        #------- Selects sheet 5 
$ws1.Tab.ColorIndex = 8
$ws2.Tab.ColorIndex = 7
$ws3.Tab.ColorIndex = 6
$ws4.Tab.ColorIndex = 5
$ws5.Tab.ColorIndex = 4 
$ws1.name = "Detail"
$ws2.name = "JackOnly"
$ws3.name = "Top2"
$ws4.name = "LowestHighest"
$ws5.name = "Top25Percent" 
$ws1.cells.Item(1,1) =  "Amount"
$ws1.cells.Item(1,2) =  "SalesPerson"
$ws1.cells.Item(2,1) = 1
$ws1.cells.Item(2,2) = "Jack"
$ws1.cells.Item(3,1) = 2
$ws1.cells.Item(3,2) = "Elizabeth"
$ws1.cells.Item(4,1) = 3
$ws1.cells.Item(4,2) = "Renee"
$ws1.cells.Item(5,1) = 4
$ws1.cells.Item(5,2) = "Elizabeth"
$ws1.cells.Item(6,1) = 5
$ws1.cells.Item(6,2) = "Jack"
$ws1.cells.Item(7,1) = 6
$ws1.cells.Item(7,2) = "Renee"
$ws1.cells.Item(8,1) = 7
$ws1.cells.Item(8,2) = "Elizabeth"
$ws1.cells.Item(9,1) = 8
$ws1.cells.Item(9,2) = "Jack"
$ws1.cells.Item(10,1) = 9
$ws1.cells.Item(10,2) = "Renee"
$ws1.cells.Item(11,1) = 10
$ws1.cells.Item(11,2) = "Jack"
$ws1.cells.Item(12,1) = 11
$ws1.cells.Item(12,2) = "Jack"
$ws1.cells.Item(13,1) = 12
$ws1.cells.Item(13,2) = "Elizabeth"
$ws1.cells.Item(14,1) = 13
$ws1.cells.Item(14,2) = "Renee"
$ws1.cells.Item(15,1) = 14
$ws1.cells.Item(15,2) = "Elizabeth"
$ws1.cells.Item(16,1) = 15
$ws1.cells.Item(16,2) = "Jack"
$ws1.cells.Item(17,1) = 16
$ws1.cells.Item(17,2) = "Renee"
$ws1.cells.Item(18,1) = 17
$ws1.cells.Item(18,2) = "Elizabeth"
$ws1.cells.Item(19,1) = 18
$ws1.cells.Item(19,2) = "Jack"
$ws1.cells.Item(20,1) = 19
$ws1.cells.Item(20,2) = "Renee"
$ws1.cells.Item(21,1) = 20
$ws1.cells.Item(21,2) = "Renee" 
$used = $ws1.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$lastrow = $lastCell.row 
$r = $ws1.Range("A1:B$lastrow")
$ws1.Range("A1:B$lastrow").Copy() 
$ws2.Select() | Out-Null
[void]$ws2.Range("A1").PasteSpecial(-4163)
$ws3.Select() | Out-Null
[void]$ws3.Range("A1").PasteSpecial(-4163)
$ws4.Select() | Out-Null
[void]$ws4.Range("A1").PasteSpecial(-4163)
$ws5.Select() | Out-Null
[void]$ws5.Range("A1").PasteSpecial(-4163)
#
$ws5.Range("A1").Select()
# AutoFilter structure - Field, Criteria, Operator
#$xl.Selection.AutoFilter 1, "10", $xlTop10Items        #top 10
$xl.Range("A1").Select() | Out-Null
$xl.Selection.AutoFilter(1, "2", $xlTop10Items)            #top 2
#$xl.Selection.AutoFilter 1, "10", $xlTop10Percent        #top 10 percent
#$xl.Selection.AutoFilter 1, "25", $$xlTop10Percent        #top 25 percent
#$xl.Selection.AutoFilter 1, "5", $xlBottom10Items        #Lowest 5 Items
#$xl.Selection.AutoFilter 1, "10", $$xlBottom10Percent    #Bottom 10 percent
#$xl.Selection.AutoFilter 1, ">15"                        #size greater 15
#$xl.Selection.AutoFilter 1, ">19",XLOr , "<2"            #Lowest and Highest
#$xl.Selection.AutoFilter 2, "Jack"                        #Jack items only
$ws5.cells.Item.EntireColumn.AutoFit 
$ws2.Select()
$ws2.Range("A1").Select()
# AutoFilter structure - Field, Criteria, Operator
#$xl.Selection.AutoFilter 1, "10", $xlTop10Items        #top 10
#$xl.Selection.AutoFilter 1, "2", $xlTop10Items            #top 2
#$xl.Selection.AutoFilter 1, "10", $xlTop10Percent        #top 10 percent
#$xl.Selection.AutoFilter 1, "25", $xlTop10Percent        #top 25 percent
#$xl.Selection.AutoFilter 1, "5", $xlBottom10Items        #Lowest 5 Items
#$xl.Selection.AutoFilter 1, "10", $xlBottom10Percent    #Bottom 10 percent
#$xl.Selection.AutoFilter 1, ">15"                        #size greater 15
#$xl.Selection.AutoFilter 1, ">19",XLOr , "<2"            #Lowest and Highest
$xl.Selection.AutoFilter(2, "Jack")                        #Jack items only
$ws2.cells.Item.EntireColumn.AutoFit 
$ws4.Select()
$ws4.Range("A1").Select()
# AutoFilter structure - Field, Criteria, Operator
#$xl.Selection.AutoFilter 1, "10", $xlTop10Items        #top 10
#$xl.Selection.AutoFilter 1, "2", $xlTop10Items            #top 2
#$xl.Selection.AutoFilter 1, "10", $xlTop10Percent        #top 10 percent
#$xl.Selection.AutoFilter 1, "25", $xlTop10Percent        #top 25 percent
#$xl.Selection.AutoFilter 1, "5", $xlBottom10Items        #Lowest 5 Items
#$xl.Selection.AutoFilter 1, "10", $xlBottom10Percent    #Bottom 10 percent
#$xl.Selection.AutoFilter 1, ">15"                        #size greater 15
$xl.Selection.AutoFilter(1, ">19",$xlOr , "<2")            #Lowest and Highest
#$xl.Selection.AutoFilter 2, "Jack"                        #Jack items only
$ws4.cells.Item.EntireColumn.AutoFit
# "Top25Percent"
$ws5.Select()
$ws5.Range("A1").Select()
# AutoFilter structure - Field, Criteria, Operator
#$xl.Selection.AutoFilter 1, "10", $xlTop10Items        #top 10
#$xl.Selection.AutoFilter 1, "2", $xlTop10Items            #top 2
#$xl.Selection.AutoFilter 1, "10", $xlTop10Percent        #top 10 percent
$xl.Range("A1").Select() | Out-Null
$xl.Selection.AutoFilter(1,"25",$xlTop10Percent)        #top 25 percent
#$xl.Selection.AutoFilter 1, "5", $xlBottom10Items        #Lowest 5 Items
#$xl.Selection.AutoFilter 1, "10", $xlBottom10Percent    #Bottom 10 percent
#$xl.Selection.AutoFilter 1, ">15"                        #size greater 15
#$xl.Selection.AutoFilter 1, ">19",XLOr , "<2"            #Lowest and Highest
#$xl.Selection.AutoFilter 2, "Jack"                        #Jack items only
$ws5.cells.Item.EntireColumn.AutoFit 
} # End Function

#---------------------------------------------------
#

#
# How do I set up a complex Formula in Excel?
# Here is one that creates a Complex Formula and executes it 
 
Function XLFormula1 {
$xl = New-Object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False 
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1) 
$ws.name = "ComplexFormula"
$ws.Tab.ColorIndex = 9
$row = 2
$lastrow = $row
$Col = 3
$Off = $Col - 1
$ws.Cells.Item(1, 1) = "FileName"
$ws.Cells.Item(1, 2) = "Folder"
$ws.Cells.Item(1, 3) = "FullPath"
$ws.Cells.Item(2, 3) = "c:\Folder1\FunctionFolder1\FunctionFolder2\File1.txt"
$ws.Cells.Item(3, 3) = "c:\Folder1\FunctionFolder1\FunctionFolder2\FunctionFolder3\File2.txt"
$ws.Cells.Item(4, 3) = "c:\Folder1\FunctionFolder1\FunctionFolder2\FunctionFolder3\FunctionFolder4\File3.txt"
$lastrow = 4 
#Filename
$Range1 = $ws.Range("A2")
$ws.Cells.Item(2,1).FormulaR1C1 = "=MID(C[2],FIND(CHAR(127),Substitute(C[2],""\"",CHAR(127),LEN(C[2])-LEN(Substitute(C[2],""\"",""""))))+1,254)"
# Note I used 254 as a hardcoded length.  No filename should ever reach this length.  However….
# You could get the length programatically with the following
# "=MID(C[2],FIND(CHAR(127),FunctionSubsTITUTE(C[2],""\"",CHAR(127),LEN(C[2])-LEN(Substitute(C[2],""\"",""""))))+1,LEN(C[2])-(FIND(CHAR(127),Substitute(C[2],""\"",CHAR(127),LEN(C[2])-LEN(Substitute(C[2],""\"",""""))))))" 
$range2 = $ws.Range("A2:A$lastrow")
[void]$range1.AutoFill($range2,$xlFillDefault) 
#Folder
$lastrow = 4
$ws.Range("B2").Select() | Out-Null
$ws.Cells.Item(2,2).FormulaR1C1 = "=LEFT(C[1],FIND(CHAR(127),Substitute(C[1],""\"",CHAR(127),LEN(C[1])-LEN(Substitute(C[1],""\"",""""))))-1)"
$R1 = $ws.Range("B2")
$r2 = $ws.Range("B2:B$lastrow")
[void]$R1.AutoFill($R2,$xlFillDefault)
$ws.Cells.Item.EntireColumn.AutoFit
$ws.Range("A2").Select() | Out-Null
} # End Function

#---------------------------------------------------
#

# How do I set up a Pivot Table in Excel?
# I was working with MBSA reports here.
#
Function Pivot {
$xlPivotTableVersion12     = 3
$xlPivotTableVersion10     = 1
$xlCount                 = -4112
$xlDescending             = 2
$xlDatabase                = 1
$xlHidden                  = 0
$xlRowField                = 1
$xlColumnField             = 2
$xlPageField               = 3
$xlDataField               = 4    
# R1C1 means Row 1 Column 1 or "A1"
# R65536C5 means Row 65536 Column E or "E65536"
$PivotTable = $wb.PivotCaches().Create($xlDatabase,"Report!R1C1:R65536C5",$xlPivotTableVersion10)
$PivotTable.CreatePivotTable("Pivot!R1C1") | Out-Null 
[void]$ws3.Select()
$ws3.Cells.Item(1,1).Select()
$wb.ShowPivotTableFieldList = $true 
$PivotFields = $ws3.PivotTables("PivotTable1").PivotFields("Server") # Worksheet Name is Server
$PivotFields.Orientation = $xlRowField
$PivotFields.Position = 1 
$PivotFields = $ws3.PivotTables("PivotTable1").PivotFields("KBID") # Column Header is KBID
$PivotFields.Orientation = $xlColumnField
$PivotFields.Position = 1 
$PivotFields = $ws3.PivotTables("PivotTable1").PivotFields("KBID") # The data is in this column
$PivotFields.Orientation=$xlDataField 
$PivotFields.Caption = "Count of KB's"
$PivotFields.Function = $xlCount

$mainRng = $ws3.UsedRange.Cells 
$RowCount = $mainRng.Rows.Count  
$R = $RowCount
$R = $R - 1    
$mainRng.Select()
$objSearch = $mainRng.Find("Grand Total")
$objSearch.Select()
$C = $objSearch.Column
Write-Host $C $R # this is just so I can see what's happining 
$xlSummaryAbove = 0 
$xlSortValues = $xlPinYin = 1
$xlAscending = 1 
$xlDescending = 2 
$range1 = $ws3.UsedRange 
$range2 = $ws3.Cells.Item(3, $C) 
# one-column sort
[void]$range2.sort($range2, $xlDescending) # puts the highest numbers at the top
} # End Function
Advertisements

110 thoughts on “Powershell Excel Cookbook Ver 2

  1. Thanks ever so much for this. It covers every single operation I’ve been trying to figure out. Would have saved me many hours if I had found it sooner.

    • Well, you can find the font size like this:

      $xl = new-object -comobject Excel.Application
      # make Excel visible
      $xl.visible = $true
      $xl.DisplayAlerts = $False
      # open a workbook
      $wb = $xl.workbooks.open(“C:\Scripts\Test.xls”)

      # Get sheet1
      $ws = $wb.worksheets | where {$_.name -eq “sheet1”}

      $range3=$ws.range(“A2:B2”)
      $a = $Range3.font.size
      $a.ToString()

      So you might be able to ID the range that that the foot notes are in and then delete the range. I’ll think about it.

  2. woot, thank you! I’ve finally came across a website where the owner knows what they’re talking about. You know how many results are in Google when I check.. too many! It’s so annoying having to go from page after page after page, wasting my day away with tons of owners just copying eachother’s articles… ugh. Anyway, thankyou for the information anyway, much appreciated.

    • $R = $ws.UsedRange
      $r2 = $ws.Range(“B2”) # Sorts on Column B and leaves Header alone
      $a = $r.sort($r2, $xlAscending)

      You are right, Try this:

      $range1 = $ws1.range(“A2:$last” ) #<— Range starts on Row two
      $range2 = $ws1.range("B2") # <– Sorts Column B starting on row 2
      [void]$range1.sort($range2, $xlAscending)

  3. Very good post. I actually just stumbled upon your current blog plus wanted to talk about which need truly enjoyed studying your own web page posts. However I’ll often be following to the wesite plus I hope you actually post again in the near future.

  4. Hi,
    that is great.
    I´ve one question: Is it possible to add more then one value to the autofilter,

    I tried the following but no luck:
    $filterlist = “Jack”,”Elizabeth”
    $xl.Selection.AutoFilter(2, $filterlist, xlFilterValues)

    • Field:=2, Criteria1:=”Jack”, _
      Operator:=xlAnd, Criteria2:=”Elizabeth”

      So try this:

      $xl.Selection.AutoFilter 2, Jack, $xlAnd, Elizabeth #Jack & Elizabeth items only

  5. Thanks for the response, but this solution just works with two criteria,
    I was looking for that solution:

    $xlFilterValues = 7 # found in MS documentation
    $filterList = “Jack”,”Elizabeth” # array
    $xl.Selection.AutoFilter(2, $filterList ,$xlFilterValues)

    Thanks again, for you great work, really nice stuff.

  6. Hi, I am trying to delete a row using your example and it is not working. My boss suggested that I needed to actually call the function after I declared it (see below) but that doesn’t seem to do it either. Can you please help? (And actually, I am trying to delete 2 rows.) Thanks!

    #Delete a row
    Function DelRow($y) {
    $Range = $ws.Cells.Item($y, 1).EntireRow
    [void]$Range.Select()
    [void]$Range.Delete()
    }
    DelRow

    • To delete one row:
      The example creates a spreadsheet with 30 rows and then deletes row 28:

      Function DelRow($y) {
      $Range = $ws.Cells.Item($y, 1).EntireRow
      [void]$Range.Select()
      [void]$Range.Delete()
      }

      $xl = new-object -comobject excel.application
      $xl.Visible = $true
      $wb = $xl.Workbooks.Add()
      $ws = $wb.Worksheets.Item(1)

      for ($row = 1; $row -lt 30; $row++)
      {
      $ws.Cells.Item($row,1) = $row

      }
      $Y = "28"
      DelRow($Y)

      To delete two or more rows, (I didn’t use a function here):

      $xl = new-object -comobject excel.application
      $xl.Visible = $true
      $wb = $xl.Workbooks.Add()
      $ws = $wb.Worksheets.Item(1)

      for ($row = 1; $row -lt 30; $row++)
      {
      $ws.Cells.Item($row,1) = $row
      }
      $range = $ws.Range("A28", "A29").EntireRow
      [void]$Range.Select()
      [void]$Range.Delete()

      I fixed my sample in the Cook book so it looks like this:

      Function DelRow($y) {
      $Range = $ws1.Cells.Item($y, 1).EntireRow
      [void]$Range.Select()
      [void]$Range.Delete()
      }
      DelRow($y)

      • I thought of another way to select a range of rows:
        This will select rows 12 through 17.

        $range = $ws.Rows.Item("12:17")
        [void]$range.select()
        
  7. Oh, I thought it might help if you saw the errors I am getting:

    Exception getting “Item”: “Exception from HRESULT: 0x800A03EC”
    At D:\Work\DEV\COA\COA.ps1:13 char:24
    + $Range = $ws.Cells.Item <<<< ($y, 1).EntireRow
    + CategoryInfo : NotSpecified: (:) [], GetValueInvocationException
    + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertyGetValueTI

    You cannot call a method on a null-valued expression.
    At D:\Work\DEV\COA\COA.ps1:14 char:20
    + [void]$Range.Select <<<< ()
    + CategoryInfo : InvalidOperation: (Select:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At D:\Work\DEV\COA\COA.ps1:15 char:20
    + [void]$Range.Delete <<<< ()
    + CategoryInfo : InvalidOperation: (Delete:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

  8. Excellent. Thank you very much. Could I bother you for two more issues?
    1) I have data in a column that looks like 31-4006 or 31-2004, etc. The common denominator is the 31-. I want to remove the 31- from the data in the column leaving the remaining 4 digits.

    2) I used your copy/past function to copy data from one column to another but this is a copy not a move. Is there a function to move the data rather than copy? I am left with data in the original columns that I don’t want – though I need the columns there. If I delete the columns to get rid of the data, the rest of the data in the spreadsheet moves to the right which I don’t want. And I can’t find any commands to insert columns to put the data back to the columns I want if I delete the columns with the data in it.

    Thanks again
    Sherri

  9. You could select the column and then replace “31-” with “nothing”:
    Because I don’t know how many rows are in your spreadsheet,
    I look for the Last used cell and search to its row.
    I am using column “B” here so change the ($row,2) to the correct column.

    $xlCellTypeLastCell = 11
    $used = $ws.usedRange
    $lastCell = $used.SpecialCells($xlCellTypeLastCell)
    $R = $lastCell.row
    for ($row = 1; $row -lt $R; $row++)
    {
    $s = $ws.Cells.Item($row,2).Value()
    $s = $s -replace(“31-“,””)
    $ws.Cells.Item($row,2) = $s
    }
    [/sourcecode ]

    • Item 2: If there is a move function, I can’t find it. This will copy a range of cells and paste them in a different column. You could then go back and delete the items you no longer need or want. Assumes that Row 1 is a header row, so I start with row 2 and copy everything down to the last used cell.

      $xlCellTypeLastCell = 11
      $used = $ws.usedRange
      $lastCell = $used.SpecialCells($xlCellTypeLastCell)
      $R = $lastCell.row

      [void]$ws.Range(“B2:B$R”).Copy()
      [void]$ws.Range(“D2”).Select()
      [void]$ws.Range(“D2”).PasteSpecial(-4163)

  10. I definitely wanted to construct a simple word to say thanks to you for these remarkable guidelines you are showing at this website. My extended internet investigation has at the end been rewarded with brilliant facts and techniques to talk about with my visitors. I ‘d admit that most of us readers actually are very endowed to exist in a decent website with very many wonderful individuals with useful tactics. I feel somewhat fortunate to have encountered the web site and look forward to tons of more excellent moments reading here. Thanks once again for a lot of things.

  11. Have you had any experience with iterating through the PageField Values? I set up a PivotField with an Orientation = 3 (which is PageField) and I have a FOR loop that filters on each Pagefield value and creating a new worksheet for each value, but it fails. Any ideas?

    $pivotfields = $pivot.PivotFields(“Abbreviation”)
    $pivotfields.Orientation=3

    #code does not work, the problem is the COunt function is not returning anything
    for ($i=1; $i -le $pivotfields.PivotItems.Count; $i++)

    {
    #sets the filter to the nth item
    $pivotfields.CurrentPage = $pivotfields.PivotItems($i).Value
    #set the worksheet name to the nth item
    $pivotsheet.Name = $pivotsheet.Cells.Item(1,2).Value()
    #copies the worksheet to the front
    $workbook.worksheets.Item(1).Copy($workbook.worksheets.Item(1))
    }

      • $filePathcsv =”C:\My Documents\Raw Data\20120625.txt”
        $filePathxlsx =”C:\Raw Data\20120625.xlsx”
        #Create the COM object and set application settings
        $excel = New-Object -ComObject excel.application
        $excel.Visible = $False
        $excel.DisplayAlerts = $False
        #Open the CSV file in excel
        $workbook = $excel.Workbooks.Open($filePathcsv)
        #Rename SHeet
        $workbook.worksheets.Item(1).Name = “Table”
        #Create the Pivot Table
        $pivot = $workbook.PivotCaches().Create(1, $workbook.worksheets.Item(1).UsedRange)
        $pivot.CreatePivotTable(“”,”AR”) | Out-Null
        #Set the new sheets with pivot tables on them
        $pivotsheet = $workbook.worksheets.Item(1)
        $pivot = $pivotsheet.PivotTables(“AR”)
        #Add items to the pivot table
        $pivotfields = $pivot.PivotFields(“Category_Description”)
        $pivotfields.Orientation=1
        $pivotfields = $pivot.PivotFields(“Five”)
        $pivotfields.Orientation=2
        $pivotfields = $pivot.PivotFields(“Total”)
        $pivotfields.Orientation=4
        #Add the filter items
        $pivotfields = $pivot.PivotFields(“Abbreviation”)
        $pivotfields.Orientation=3
        #Part that does not work, the Count function does not count anything
        for ($i=1; $i -le $pivotfields.PivotItems.Count; $i++)
        {
        #sets the filter to the nth item
        $pivotfields.CurrentPage = $pivotfields.PivotItems($i).Value
        #set the worksheet name to the nth item
        $pivotsheet.Name = $pivotsheet.Cells.Item(1,2).Value()
        #copies the worksheet to the front
        $workbook.worksheets.Item(1).Copy($workbook.worksheets.Item(1))
        }

        $xlFormat = 51
        $workbook.SaveAS($filePathxlsx,$xlFormat)
        $workbook.Close()
        $excel.DisplayAlerts = $True
        $excel.Quit()
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

      • You might try the Excel forum. The answer will probably be something in VBA, but we can either convert that to POSH or call a macro with your script. To be honest, I can’t get my Excel 2010 to admit that there is such a thing as PageField Items, But I’m thinking you probably know a lot more about Excel than I do.

        I found this: “http://www.mrexcel.com/forum/showthread.php?643300-Loop-Through-Pivot-Table-Page-Field-and-return-data” that might give you a clue.

  12. Upon further review:

    $mainRng = $pivotsheet.UsedRange.Cells
    $RowCount = $mainRng.Rows.Count
    $R = $RowCount
    $R = $R – 1
    $mainRng.Select()

    for ($i=1; $i -le $R; $i++)
    {
    #sets the filter to the nth item
    $pivotfields.CurrentPage = $pivotfields.PivotItems($i).Value
    #set the worksheet name to the nth item
    $pivotsheet.Name = $pivotsheet.Cells.Item(1,2).Value()
    #copies the worksheet to the front
    $workbook.worksheets.Item(1).Copy($workbook.worksheets.Item(1))
    }

    $xlFormat = 51
    $workbook.SaveAS($filePathxlsx,$xlFormat)
    $workbook.Close()
    $excel.DisplayAlerts = $True
    $excel.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

    • I appreciate the asssitance, and I did not realize you responded. I found a solution as well by using the Evalution function in excel i can count the unique values in the column along with the rows since i have thousands of entries and only 3 unique values

      #this will count the unique values in the column
      $rows = $sheettable.UsedRange.Rows.Count – 1
      $formula = “=SUM(IF(FREQUENCY(Table!AZ2:AZ” + $rows + “,Table!AZ2:AZ” + $rows + “)>0,1))”

      [int]$uniquevalues = $excel.Evaluate($formula)

      for ($i=1; $i -le $uniquevalues; $i++)

      {
      #sets the filter to the nth item
      $workbook.worksheets.Item(1).PivotTables(1).PivotFields(“Abbreviation”).CurrentPage = $pivotfields.PivotItems($i).Value
      #set the worksheet name to the nth item
      $workbook.worksheets.Item(1).Name =$workbook.worksheets.Item(1).PivotTables(1).PivotFields(“Abbreviation”).PivotItems($i).Value
      #copies the worksheet to the front, if i am on the last loop do not do this
      if ($i -eq $uniquevalues) {break}
      $workbook.worksheets.Item(1).Copy($workbook.worksheets.Item(1))
      $workbook.worksheets.Item(1).Activate()
      }

  13. Cool blog! Is your theme custom made or did you download it from somewhere? A design like yours with a few simple adjustements would really make my blog shine. Please let me know where you got your theme. Cheers

  14. Hi there! I know this is kinda off topic but I was wondering if you knew where I could find a captcha plugin for my comment form? I’m using the same blog platform as yours and I’m having difficulty finding one? Thanks a lot!

  15. Incredible! This blog looks exactly like my old one! It’s on a totally different topic but it has pretty much the same page layout and design. Wonderful choice of colors!

  16. Greetings I am so thrilled I found your blog, I really found you by error, while I was browsing on Bing for something else, Anyways I am here now and would just like to say kudos for a fantastic post and a all round exciting blog (I also love the theme/design), I don’t have time to go through it all at the minute but I have bookmarked it and also added your RSS feeds, so when I have time I will be back to read a lot more, Please do keep up the excellent work.

  17. Excellent blog you have here but I was wanting to know if you knew of any discussion boards that cover the same topics discussed here? I’d really like to be a part of community where I can get suggestions from other knowledgeable individuals that share the same interest. If you have any recommendations, please let me know. Thanks!

  18. Hey there just wanted to give you a quick heads up. The text in your article seem to be running off the screen in Internet explorer. I’m not sure if this is a formatting issue or something to do with internet browser compatibility but I figured I’d post to let you know. The style and design look great though! Hope you get the problem resolved soon. Cheers

  19. Hiya, I am really glad I’ve found this info. Nowadays bloggers publish only about gossips and web and this is really annoying. A good website with exciting content, that is what I need. Thanks for keeping this web site, I will be visiting it. Do you do newsletters? Can’t find it.

    • If you mean Powershell and Excel, then no. Powershell in general has a whole lot of blogs dedicated to it.
      I have some listed on my site on the right hand side.

  20. I know this if off topic but I’m looking into starting my own weblog and was curious what all is required to get set up? I’m assuming having a blog like yours would cost a pretty penny? I’m not very internet smart so I’m not 100% certain. Any recommendations or advice would be greatly appreciated. Thanks

  21. Howdy! Do you know if they make any plugins to safeguard against hackers? I’m kinda paranoid about losing everything I’ve worked hard on. Any suggestions? 893660

  22. Hey there! I’ve been reading your website for a long time now and finally got the bravery to go ahead and give you a shout out from Porter Tx! Just wanted to mention keep up the excellent job!

  23. Nice. Came here to take a sip (I was trying to find the last used row of a spreadsheet) and ended up drinking from the firehose! Thanks, your examples and comments are quite helpful.

  24. Following examine a couple of of the weblog posts on your web website now, and I truly like your manner of blogging. I bookmarked it to my bookmark web site record and will probably be checking back soon. Pls take a look at my web page as nicely and let me know what you think. 798536

  25. Id should consult you here. Which is not some thing Its my job to do! I spend time reading an write-up that might get individuals to believe. Also, numerous thanks for permitting me to comment! 532547

  26. I discovered your internet site internet site online and check a lot of of your early posts. Maintain on the top notch operate. I just now additional your Feed to my MSN News Reader. Seeking for forward to reading considerably far more from you obtaining out later on! 337308

  27. An fascinating discussion could be valued at comment. I do believe which you basically write read far more about this subject, it may possibly not often be a taboo topic but normally persons are too couple of to dicuss on such topics. To a higher. Cheers 365688

  28. I discovered your weblog post internet internet site on the search engines and appearance several of your early posts. Always sustain the top notch operate. I additional the Feed to my MSN News Reader. Seeking forward to reading considerably far more on your part down the line! 133701

  29. I have a small problem with selecting a range, and then sorting by another range contained within the first one. Here is my current effort at it:

    $Range1 = $objWorksheet.Range(“A($Global:intGroup + $Global:intTotUsersOU + 1):G($Global:intGroup) + 1”)
    $Range2 = $objWorksheet.Range(“B($Global:intGroup) + 1”)
    [void]$Range1.Sort($Range2, $xlAscending)

    Can you help me find a way to write this correctly?

  30. Can you give me the exact powershell script to run to sum a column? I have been trying to use your example but cannot get it to work. I have tried on the actual file I want to work with as well as a test file. I cannot get it to sum either one.

    Thanks!

    • What does not work?
      I have tested it on Excel 2010 as that is all I have right now.

      Function xlSum {
      $range = $ws1.usedRange
      $row = $range.rows.count # Takes you to the last used row
      $Sumrow = $row + 1
      $r = $ws1.Range(“B2:B$row”) # select the column to Add up. In this case B2 to the last used row in column B.
      $functions = $xl.WorkSheetfunction
      $ws.cells.item($Sumrow,2) = $functions.sum($r) # this uses the Excel sum function
      }

      • This is what I have. The file has numbers in column A for 6 rows. There is nothing that gets out put to the file.

        $xl = New-Object -ComObject excel.application

        $xl.visible = $True

        $sourcefile = “C:\test2.xlsx”

        $Workbook = $xl.Workbooks.open($sourcefile)

        $ws1 = $Workbook.WorkSheets.item(1)

        $ws1.activate()

        Function xlSum {
        $range = $ws1.usedRange
        $row = $range.rows.count # Takes you to the last used row
        $Sumrow = $row + 1
        $r = $ws1.Range(“A2:A$row”) # select the column to Add up. In this case B2 to the last used row in column B.
        $functions = $xl.WorkSheetfunction
        $ws.cells.item($Sumrow,2) = $functions.sum($r) # this uses the Excel sum function
        }

        $Workbook.Save()

        $Excel.Quit()
        [gc]::collect()

        [gc]::WaitForPendingFinalizers()

      • Number one.

        Put you function first.
        Then call it from the main script.

        ###### Start Posh Script ######## 
        Function xlSum { 
        $range = $ws1.usedRange 
        $row = $range.rows.count # Takes you to the last used row 
        $Sumrow = $row + 1 $r = $ws1.Range(“A2:A$row”) # select the column to Add up. In this case B2 to the last used row in column B. 
        $functions = $xl.WorkSheetfunction 
        $ws.cells.item($Sumrow,2) = $functions.sum($r) # this uses the Excel sum function & puts the sum in column B.
        } 
        
        
        $xl = New-Object -ComObject excel.application 
        $xl.visible = $True 
        $sourcefile = "C:\test2.xlsx" 
        $Workbook = $xl.Workbooks.open($sourcefile) 
        $ws1 = $Workbook.WorkSheets.item(1) 
        $ws1.activate() 
        xlsum
        $Workbook.Save() 
        $Excel.Quit() 
        [gc]::collect() 
        [gc]::WaitForPendingFinalizers()
        
        

      • “One last question. I am trying to schedule this script but not having much luck. I have tried to point it at powershell then have –file $scriptname, I have tried pointing it at powershell and then have –command $scriptname. I have tried calling the script from a batch file. I have also tried calling just the script. None of these work. It says that it successfully ran the script, but none of my files change. I have set the execution policy to unrestricted. The script works perfectly if I run it manually. Any ideas on how to get this scheduled? Thanks!

      • To run the script outside of its Powershell environment you type a command similar to the following:
        powershell -command “& ‘MyScript.ps1’ ”

        Just put the above command into a .bat or .cmd file and schedule it like you would normally schedule a script to be run with Windows task scheduler. Happy coding!

    • Michael,

      The best thing to do is let Powershell sort the information it collects, instead of trying to get Excel to do it:

      $colUsers = Get-QADUser -SearchRoot $Path -SizeLimit 0 | Sort-Object displayName | `
      Select Path, displayName, sAMAccountName, profilePath, homeDirectory, department

      This way, when the data is placed into Excel, it will be placed in the order in which it was sorted by Powershell when it was retrieved.

      • David,

        I think you are wanting to reply to the post right above mine. I am not trying to sort data, I am trying to sum a column.

        Thanks

  31. Pingback: Powershell Excel cookbook v2.0 « Jiggawattz

  32. Logic is sound, but having an issue injecting variablized range via $derp in xlSum function. Could you shine a lite on this this for me?

    cls
    function vars { get-variable | sort Name }
    vars | remove-item #-whatif
    $erroractionpreference = “SilentlyContinue”
    Import-Module ActiveDirectory
    $currentfolder = split-path $myinvocation.mycommand.path
    $groups = “”
    $userlist = GC $currentfolder\list.txt | sort
    if (test-path $currentfolder\temp.txt) {remove-item $currentfolder\temp.txt}
    if (test-path $currentfolder\MasterGroupList.txt) {remove-item $currentfolder\MasterGroupList.txt}
    if (test-path $currentfolder\UserCNs.txt) {remove-item “$currentfolder\UserCNs.txt”}

    Foreach ($user in $userlist)
    {
    $array = @()
    $array2 = @()
    $groups = Get-AdUser -Identity $user -property “MemberOf”
    $sortedGroups = $groups.memberof | Sort | Get-Unique
    Foreach($group in $sortedGroups)
    {$array +=$group}
    $number = $array.count
    foreach ($item in $array)
    {
    $1 = $item -replace “CN\=”,””
    $2 = $1 -replace “^*,OU\=.*$”,””
    $3 = $2 -replace “^*,Users.*$”,””
    $4 = $3 -replace “^*,Builtin.*$”,””
    add-content $currentfolder\temp.txt $4
    }
    }
    $MasterGroupList = gc $currentfolder\temp.txt | sort | Get-Unique
    remove-item $currentfolder\temp.txt
    foreach ($schlub in $MasterGroupList)
    {add-content $currentfolder\MasterGroupList.txt $schlub}
    # some Excel Constants
    # line styles
    $xlLineStyleNone = -4142
    $xlContinuous = 1
    $xlDash = -4115

    $a = New-Object -comobject Excel.Application
    $a.Visible = $true
    $functions = $a.WorkSheetfunction
    $b = $a.Workbooks.Add()
    $WS=$a.ActiveSheet
    $cells = $WS.Cells
    $date = Get-Date
    #define some variables to control navigation
    $row=1
    $col=2
    $WS.UsedRange.Borders.LineStyle = $xlContinuous
    $blue = 255
    $green= 0
    $red = 0
    $a = 1
    $WS.UsedRange.Borders.Color = $a
    $WS.UsedRange.Borders.Weight = $xlThin
    $WS.Application.ActiveWindow.SplitColumn = 1
    $WS.Application.ActiveWindow.SplitRow = 1
    $WS.Application.ActiveWindow.FreezePanes=$true
    #insert column headings
    GC $currentfolder\MasterGroupList.txt | foreach {
    $cells.item($row,$col)=$_
    $cells.item($row,$col).font.bold=$False
    $cells.item($row,$col).Orientation = 90
    $cells.EntireColumn.AutoFit() | Out-Null
    $WS.UsedRange.Borders.LineStyle = $xlContinuous
    $col++
    }
    $row=2
    $col=1
    $blarb = Get-Content (“$currentfolder\list.txt”) | Sort #
    foreach ($slarb in $blarb)
    {
    $CN = Get-AdUser -filter ‘SamAccountName -eq $slarb’
    Add-Content “$currentfolder\UserCNs.txt” $CN
    }
    $GroupList = @{}
    Function IsMember ($ADObject, $GroupName)
    {
    If ($GroupList.ContainsKey($ADObject.sAMAccountName.ToString() + “\”) -eq $False)
    {
    $GroupList.Add($ADObject.sAMAccountName.ToString() + “\”, $True)
    $ADObject.psbase.RefreshCache(“tokenGroups”)
    $SIDs = $ADObject.psbase.Properties.Item(“tokenGroups”)
    ForEach ($Value In $SIDs)
    {
    $SID = New-Object System.Security.Principal.SecurityIdentifier $Value, 0
    $Group = $SID.Translate([System.Security.Principal.NTAccount])
    $GroupList.Add($ADObject.sAMAccountName.ToString() + “\” + $Group.Value.Split(“\”)[1], $True)
    }
    }
    If ($GroupList.ContainsKey($ADObject.sAMAccountName.ToString() + “\” + $GroupName)) {Return $True}
    Else {Return $False }
    }
    $UserCNs = Get-Content(“$currentfolder\UserCNs.txt”)
    foreach ($item in $UserCNs)
    {
    $User = [ADSI]”LDAP://$item”
    $3 = $item -replace “CN\=”,””
    $4 = $3 -replace “^*,OU\=.*$”,””
    $x=1
    $y=2
    foreach ($habba in Get-content $currentfolder\MasterGroupList.txt)
    {
    If (IsMember $User $habba -eq $True)
    { write-host Yes,$habba,$User.sAMAccountName -Foregroundcolor green
    $cells.item($row,$col)=$4
    $cells.EntireColumn.AutoFit() | Out-Null
    $cells.item($row,$col).font.bold=$True
    $WS.Cells.Item($row,$y).Value() =1;$WS.Cells.Item($row,$y).Interior.ColorIndex = 34#;$WS.Cells.Item($row,$y).HorizontalAlignment = -4108
    }
    Else
    {
    write-host No,$habba,$Computer.sAMAccountName -Foregroundcolor red
    }
    $y++
    $WS.UsedRange.Borders.LineStyle = $xlContinuous
    }
    $Row++
    }
    if (test-path $currentfolder\MasterGroupList.txt) {remove-item $currentfolder\MasterGroupList.txt}
    if (test-path $currentfolder\UserCNs.txt) {remove-item “$currentfolder\UserCNs.txt”}

    Function Convert-NumberToA1
    {

    Param([parameter(Mandatory=$true)]
    [int]$number)

    $a1Value = $null
    While ($number -gt 0) {
    $multiplier = [int][system.math]::Floor(($number / 26))
    $charNumber = $number – ($multiplier * 26)
    If ($charNumber -eq 0) { $multiplier– ; $charNumber = 26 }
    $a1Value = [char]($charNumber + 64) + $a1Value
    $number = $multiplier
    }
    Return $a1Value
    }
    # End Function

    Function xlSum
    {
    $range = $WS.usedRange
    $rows = $range.rows.count # Takes you to the last used row
    $cols = $range.columns.count # Takes you to the last used column
    $Sumrow = $rows + 1
    $Sumcol = $cols + 1
    $Q=2
    $U=2
    do
    {
    $Y=2
    $p = Convert-NumberToA1 ($Q)
    $grid1 = “$p$Y`:”.ToString()
    $grid2 = “$p$rows”.ToString()
    $r = “””$grid1$grid2″””
    write-host range $r
    $derp = $WS.Range($r)
    write-host $Q$Sumrow
    $WS.cells.item($SumRow,$Q) = $functions.sum($derp)
    [void]$range.entireColumn.Autofit()
    $Q++
    }
    while($Q -lt $Sumcol)
    do
    {
    $p = Convert-NumberToA1 ($U)
    $G = Convert-NumberToA1 ($cols)
    $grid1 = “B$U`:”
    $grid2 = “$G$U”
    $r = “””$grid1$grid2″””
    write-host range $r
    $derp = $WS.Range($r)
    write-host $U$SumCol
    $WS.cells.item($U,$Sumcol) = $functions.sum($derp)
    [void]$range.entireColumn.Autofit()
    $U++
    }
    while($U -lt $Sumrow)
    $WS.cells.item($SumRow,$SumCol) = $functions.sum($range)
    $cells.EntireColumn.AutoFit() | Out-Null
    }
    # End Function
    xlSum
    #End Script

  33. I was able to figure it out… In the code above (within the first do-while loop in the xlSum Function) The .ToString() invocation was not working. What I needed to do was instantiate the $derp variable as a string, which I accomplished by passing a null value string at the beginning of the variable, causing the remainder of the variable to be processed as a string. This allowed me to remove the “grid” variables, and call the others directly.

    Here is the updated, working function:

    Function xlSum
    {
    $range = $WS.usedRange
    $rows = $range.rows.count # Takes you to the last used row
    $cols = $range.columns.count # Takes you to the last used column
    $Sumrow = $rows + 1
    $Sumcol = $cols + 1
    $Q=2
    $U=2
    do
    {
    $Y=2
    $p = Convert-NumberToA1 ($Q)
    $r = (“”+$p+$Y+”:”+$p+$rows)
    write-host range $r
    $derp = $WS.Range($r)
    write-host $Q$Sumrow
    $WS.cells.item($SumRow,$Q) = $functions.sum($derp)
    [void]$range.entireColumn.Autofit()
    $Q++
    }
    while($Q -lt $Sumcol)
    do
    {
    $G = Convert-NumberToA1 ($cols)
    $r = (“”+”B”+$U+”:”+$G+$U)
    write-host range $r
    $derp = $WS.Range($r)
    write-host $U$SumCol
    $WS.cells.item($U,$Sumcol) = $functions.sum($derp)
    [void]$range.entireColumn.Autofit()
    $U++
    }
    while($U -lt $Sumrow)
    $WS.cells.item($SumRow,$SumCol) = $functions.sum($range)
    $cells.EntireColumn.AutoFit() | Out-Null
    }
    # End Function
    xlSum

  34. Great Article! Thanks it saved a alot of reserach effort.

    Can you show an example to do VLOOKUP using power shell?

  35. Hi,

    Fabulous blog. Thanks for putting this up!

    I’ve entered this formula into cell F2 (it does a vlookup against a 2nd spreadsheet). It works perfectly for just that individual cell.

    =VLOOKUP(E2,ADUsers.csv!$A$2:$B$10000,2,FALSE)

    What’s the Powershell equivalent of double-clicking the tiny black square in the bottom right-hand corner of the cell, so that it copies the formula right the way down column F? (The ‘EntireColumn’ property will almost do it, but it messes with the column heading. 😦

    Thanks.

    Alex

  36. Hi : Thank you for an awesome post, consolidating all excel functions. The deleteemptyrows is not working for me. IT is telling you cannot equate to $Null. is there another way. Can you script out a working copy for me. Thanks

  37. # Specify the path to the Excel file and the WorkSheet Name
    $FilePath = “C:\Users\Nathan\Desktop\test\”;
    $srcFiles = Get-ChildItem $FilePath\*.xls;

    $destFile = “C:\Users\Nathan\Desktop\test.xls”;
    # Create an Object Excel.Application using Com interface
    $destObjExcel = New-Object -ComObject Excel.Application;

    # Disable the ‘visible’ property so the document won’t open in excel
    $destObjExcel.Visible = $false;

    # Open the Excel file and save it in $WorkBook
    $destWorkBook = $destObjExcel.Workbooks.Open($destFile);

    # Load the WorkSheet ‘BuildSpecs’
    $destWorkSheet = $destWorkBook.sheets.item(“Sheet1”);
    $destRowCount = 0;
    $range2 = $destWorkSheet.Range(“A1”);
    foreach ($srcFile in $srcFiles){

    $fullPath = $FilePath+””+$srcFile.name
    $sheetName = $srcFile.name;
    $sheetName = $sheetName -replace “.xls”,””;

    # Create an Object Excel.Application using Com interface
    $srcObjExcel = New-Object -ComObject Excel.Application;

    # Disable the ‘visible’ property so the document won’t open in excel
    $srcObjExcel.Visible = $false;
    $srcObjExcel.DisplayAlerts = $False
    # Open the Excel file and save it in $WorkBook
    $srcWorkBook = $srcObjExcel.Workbooks.Open($fullPath);

    # Load the WorkSheet ‘BuildSpecs’
    $srcWorkSheet = $srcWorkBook.sheets.item(1);
    $mainRng = $srcWorkSheet.UsedRange.Cells;
    $srcRowCount = $mainRng.Rows.Count;
    $srcColCount = $mainRng.Columns.Count;
    $range = $srcWorkSheet.usedRange;

    [void]$range.copy();
    [void]$range2.PasteSpecial(-4163);

    $srcWorkBook.close();
    $srcObjExcel.quit();
    $destWorkBook.save();

    Remove-Variable -Name srcObjExcel;
    Remove-Variable -Name range;
    [gc]::collect();
    [gc]::WaitForPendingFinalizers();
    $destRowCount += $srcRowCount;
    $newRow = $destRowCount+1;
    $range2 = $destWorkSheet.Range(“A$newRow”);
    echo “Combining:”;
    echo $fullPath;
    }
    $destWorkBook.close();
    $destObjExcel.quit();
    Remove-Variable -Name destObjExcel;
    [gc]::collect();
    [gc]::WaitForPendingFinalizers();

  38. # Specify the path to the Excel file and the WorkSheet Name
    $FilePath = “C:\Users\Nathan\Desktop\test\”;
    $srcFiles = Get-ChildItem $FilePath\*.xls;

    $destFile = “C:\Users\Nathan\Desktop\test.xls”;
    # Create an Object Excel.Application using Com interface
    $destObjExcel = New-Object -ComObject Excel.Application;

    # Disable the ‘visible’ property so the document won’t open in excel
    $destObjExcel.Visible = $false;

    # Open the Excel file and save it in $WorkBook
    $destWorkBook = $destObjExcel.Workbooks.Open($destFile);

    # Load the WorkSheet ‘BuildSpecs’
    $destWorkSheet = $destWorkBook.sheets.item(“Sheet1”);
    $destRowCount = 0;
    $range2 = $destWorkSheet.Range(“A1”);
    foreach ($srcFile in $srcFiles){

    $fullPath = $FilePath+””+$srcFile.name
    $sheetName = $srcFile.name;
    $sheetName = $sheetName -replace “.xls”,””;

    # Create an Object Excel.Application using Com interface
    $srcObjExcel = New-Object -ComObject Excel.Application;

    # Disable the ‘visible’ property so the document won’t open in excel
    $srcObjExcel.Visible = $false;
    $srcObjExcel.DisplayAlerts = $False
    # Open the Excel file and save it in $WorkBook
    $srcWorkBook = $srcObjExcel.Workbooks.Open($fullPath);

    # Load the WorkSheet ‘BuildSpecs’
    $srcWorkSheet = $srcWorkBook.sheets.item(1);
    $mainRng = $srcWorkSheet.UsedRange.Cells;
    $srcRowCount = $mainRng.Rows.Count;
    $srcColCount = $mainRng.Columns.Count;
    $range = $srcWorkSheet.usedRange;

    [void]$range.copy();
    [void]$range2.PasteSpecial(-4163);

    $srcWorkBook.close();
    $srcObjExcel.quit();
    $destWorkBook.save();

    Remove-Variable -Name srcObjExcel;
    Remove-Variable -Name range;
    [gc]::collect();
    [gc]::WaitForPendingFinalizers();
    $destRowCount += $srcRowCount;
    $newRow = $destRowCount+1;
    $range2 = $destWorkSheet.Range(“A$newRow”);
    echo “Combining:”;
    echo $fullPath;
    }
    $destWorkBook.close();
    $destObjExcel.quit();
    Remove-Variable -Name destObjExcel;
    [gc]::collect();
    [gc]::WaitForPendingFinalizers();

  39. Admiring the hard work you put into your site and detailed information you offer. It’s good to come across a blog every once in a while that isn’t the same unwanted rehashed material. Wonderful read! I’ve bookmarked your site and I’m adding your RSS feeds to my Google account. dbdgdkeekdck

  40. An impressive share! I have just forwarded this onto a coworker who was doing a little homework on this.
    And he in fact ordered me breakfast because I found it for him…
    lol. So allow me to reword this…. Thank YOU for
    the meal!! But yeah, thanks for spending some time to discuss this subject here on your website.

  41. Pingback: Number of VLFs and Autogrowth Settings Colour Coded to Excel with PowerShell | SQL DBA with A Beard

  42. great page , wish I had found it sooner 🙂

    I now have a script that searches through a folder structure and looks for a specific value in all of the excel files. Once it finds a match I can get it to report the worksheet and row etc , how can I get it to grab the entire row as a result ?

  43. Pingback: Number of VLFs and Autogrowth Settings Colour Coded to Excel with PowerShell - SQL Server - SQL Server - Toad World

  44. Is so nice all wath you do!
    For me, all works so fin. Thanks!

    Please:
    How do add diff color for one cell?
    ~
    $text1 = $($Rob.Cells.Item(48, 2).Font.ColorIndex = 1) + “Weekly: ” # black
    $text2 = $($Rob.Cells.Item(48, 2).Font.ColorIndex = 3) + $RWeek # red
    $text3 = $($Rob.Cells.Item(48, 2).Font.ColorIndex = 1) + ” (Day ” + ((get-date).DayOfWeek.value__) + “)” # Black
    $Rob.Cells.Item(48,2) = $text1+” “+$text2+” “+$text3
    ~
    Thanks for you help!
    Greats

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s