Scripting >> Powershell >> Working with Microsoft Office Excel - part 1

1.   Open Excel, Select the 1st worksheet, set AutoFilter and then filter by Specific Column that equals a particular String

$Filename = "c:\temp\mysheet.xlsx"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.DisplayAlerts = $False
$objWrkBk  = $objExcel.Workbooks.Open($Filename)
$objExcel.Visible = $True
# one way of selecting a sheet e.g. 3rd worksheet
# $objWrkBk.Sheets(3).Select()
# another way of selecting a sheet e.g. 1st worksheet
# $objWrkBk.Worksheets.item(1).Select()
$objWrkSht1 = $objWrkBk.Worksheets.item(1)
$objWrkSht1.Select()
# filter on 7th column, with string matching 'ABC'
$objWrkSht1.Range('$A$nn:$P$mm').AutoFilter(7,'=ABC')
# $A$nn => range first cell position, $P$mm = range last cell position

2.  Read data from particular cell

# Read data at column 7, row 1
$objWrkSht1.Cells.Item(7,1).Value2
Company

3. Read data from a cell range

# Read data from a range
$objWrkSht1.Range("G2:G4").Value2
ABC
ABC
ABC

4. Read data from a cell range into an array

#read into an array
$companies = @($objWrkSht1.Range("G2:G21").Value2)
# iterate on all array items, method 1
for ($i=0;$i -lt $companies.Length; $i++)
{
   $companies[$i]
}
# iterate on all array items, method 2
foreach ($coy in $companies) { $coy }
CA04
CA04
CA03
CY02
CY02
CY03
RA01
RA01
ABC01
ABC01
DEF02
DEF02
DEF02
DEF02
DEF02
VA02
VA02
VA02
VA02
VA02

5.  Select all used cells in a worksheet

# select all used cells
$objExcel.Cells.Select()

6.  Sorting on 2 columns with headers

# how to sort on 2 columns H & G with header on, sort by column G ascending, followed by column H descending
$xlSortOnValues = $xlSortNormal   = 0
$xlTopToBottom  = $xlSummaryBelow = 1
$xlAscending    = 1
$xlDescending   = 2
$xlNo           = 2
$xlYes          = 1

$objWrkSht1 = $objWrkBk.Worksheets.item(1)
$objWrkSht1.Select()
$objRange       = $objWrkSht1.UsedRange
$objRange1      = $objWrkSht1.range("G1")
$objRange2      = $objWrkSht1.range("H1")
$objWrkSht1.Sort.SortFields.Clear()

[void] $objWrkSht1.Sort.SortFields.Add($objRange1,$xlSortOnValues,$xlAscending,$xlSortNormal)
[void] $objWrkSht1.Sort.SortFields.Add($objRange2,$xlSortOnValues,$xlDescending,$xlSortNormal)

$objWrkSht1.sort.setRange($objRange)  # define the range to sort
$objWrkSht1.sort.header = $xlYes      # range has a header
$objWrkSht1.sort.orientation = $xlTopToBottom
$objWrkSht1.sort.apply()
 
Before Sort
After Sort

7.  Finding the last row and last column of the worksheet that is in use

$Filename = "c:\temp\mysheet.xlsx"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.DisplayAlerts = $False
$objWrkBk  = $objExcel.Workbooks.Open($Filename)
$objExcel.Visible = $True

$objWrkSht1 = $objWrkBk.Worksheets.item(1)
$objWrkSht1.Select()
$objRange   = $objWrkSht1.UsedRange

$xlCellTypeLastCell = 11
$objLastcell = $objRange.SpecialCells($xlCellTypeLastCell)
$lastrow = $objLastcell.row
$lastcolumn = $objLastcell.column
$lastrow,$lastcolumn

1175
11

 8.  Set autofilter on a column by multiple criteria

# Autofilter with multiple criteria on a specific column
$Filename = "c:\temp\mysheet.xlsx"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.DisplayAlerts = $False
$objWrkBk  = $objExcel.Workbooks.Open($Filename)
$objExcel.Visible = $True

$objWrkSht1 = $objWrkBk.Worksheets.item(1)
$objWrkSht1.Select()
$objRange       = $objWrkSht1.UsedRange

$CriteriaArray = @('CA03','CY02','DEF02',"VA02","=")
# select blank with "=" OR ""
#$CriteriaArray = @('CA03','CY02','DEF02',"VA02")
$xlFilterValues = 7
$objRange.AutoFilter(7,$CriteriaArray,$xlFilterValues)

9.  More autofilter examples

# More complex autofilters
$Filename = "c:\temp\mysheet.xlsx"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.DisplayAlerts = $False
$objWrkBk  = $objExcel.Workbooks.Open($Filename)
$objExcel.Visible = $True

$objWrkSht1 = $objWrkBk.Worksheets.item(1)
$objWrkSht1.Select()
$objRange       = $objWrkSht1.UsedRange

$xlFilterValues = 7
$xlAnd = 1
$xlOr  = 2
# single criteria
$Criteria = "<>ABC01"   # Not Equal, use with xlAnd
$Criteria = "=ABC*"     # Begins with, use with xlAnd
$Criteria = "=*02"      # Ends with, use with xlAnd
$Criteria = "=*BC*"     # Contains, use with xlAnd
$Criteria = "<>*BC*"    # Does Not Contain, use with xlAnd
$objRange.AutoFilter(7,$Criteria,$xlAnd)
# 2 Criteria with logical And
$Criteria1 = "=A*"
$Criteria2 = "=*1"
$objRange.AutoFilter(7,$Criteria1,$xlAnd,$Criteria2)
# 2 Criteria with logical Or
$objRange.AutoFilter(7,$Criteria1,$xlOr,$Criteria2)

#
# TO CLEAR EXISTING FILTERS
$objRange.AutoFilter()
#

 

10.  Select cells that were autofiltered

# apply autofilter and then select the active cells that are visible from top left to bottom right
$Filename = "c:\temp\mysheet.xlsx"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.DisplayAlerts = $False
$objWrkBk  = $objExcel.Workbooks.Open($Filename)
$objExcel.Visible = $True

$objWrkSht1 = $objWrkBk.Worksheets.item(1)
$objWrkSht1.Select()
$objRange   = $objWrkSht1.UsedRange

$xlCellTypeLastCell = 11
$objLastcell = $objRange.SpecialCells($xlCellTypeLastCell)
$lastrow = $objLastcell.row
$lastcolumn = $objLastcell.column

$xlFilterValues = 7
$xlAnd = 1
$xlOr  = 2
# single criteria
$Criteria = "=DEF02"
$objRange.AutoFilter(7,$Criteria)

$xlLastCell = 11
#$objExcel.Cells.Select()
#$objExcel.Selection.AutoFilter(7,$Criteria)

$objExcel.ActiveCell.SpecialCells($xlLastCell).Select()
$objExcel.Range($objExcel.Selection,$objExcel.Cells(1)).Select()

11.  Select autofiltered cells, copy and past into new workbook, save as new file name and close.

$Filename = "c:\temp\mysheet.xlsx"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.DisplayAlerts = $False
$objWrkBk  = $objExcel.Workbooks.Open($Filename)
$objExcel.Visible = $True

$objWrkSht1 = $objWrkBk.Worksheets.item(1)
$objWrkSht1.Select()
$objRange   = $objWrkSht1.UsedRange
# define constants
$xlCellTypeLastCell = 11
$xlOpenXMLWorkbook = 51
$xlFilterValues = 7
$xlAnd = 1
$xlOr  = 2
$xlLastCell = 11
# find last cell
$objLastcell = $objRange.SpecialCells($xlCellTypeLastCell)
$lastrow = $objLastcell.row
$lastcolumn = $objLastcell.column
# Set autofilter
$Criteria = "=DEF02"
$objRange.AutoFilter(7,$Criteria)
# Select filtered cells
$objExcel.ActiveCell.SpecialCells($xlLastCell).Select()
$objExcel.Range($objExcel.Selection,$objExcel.Cells(1)).Select()
# copy and paste to new workbook
$objExcel.Selection.Copy()
$objExcel.Workbooks.Add()
$objExcel.ActiveSheet.Paste()
# set nowrap and column autofit
$objExcel.Selection.WrapText = $False
$objExcel.Selection.Columns.AutoFit()
$objExcel.Range("A2").Select()
# save as new excel workbook
$objExcel.ActiveWorkbook.SaveAs("c:\temp\DEF02-SR-Oct-2018.xlsx",$xlOpenXMLWorkbook)
#
$objExcel.Workbooks.Close()
$objExcel.Quit()  
Initial workbook

After autofilter and saving as new workbook

12. Import data from CSV file into a table-like custom object.

The Import-Csv cmdlet creates table-like custom objects from the items in CSV files. Each column in the CSV file becomes a property of the custom object and the items in rows become the property values.

# This is native to powershell and not really part of Excel
$mycontacts = import-csv "c:\temp\contacts.csv"
# Creates table-like custom objects from the items in a CSV file
# An array of row items, each column accessible by .ColumnName
$mycontacts[0].name,$mycontacts[0].address,$mycontacts[0].phone
'------------'
foreach ($contact in $mycontacts) { $contact.name,$contact.address,$contact.phone }

source .csv file

output

tom
1 main street
12345678
------------
tom
1 main street
12345678
dick
2 side avenue
23456781
mary
3 highland drive
34567812

 

xlFileFormat enumeration (Excel)

 

Name Value Description Extension
xlAddIn 18 Microsoft Excel 97-2003 Add-In *.xla
xlAddIn8 18 Microsoft Excel 97-2003 Add-In *.xla
xlCSV 6 CSV *.csv
xlCSVMac 22 Macintosh CSV *.csv
xlCSVMSDOS 24 MSDOS CSV *.csv
xlCSVWindows 23 Windows CSV *.csv
xlCurrentPlatformText -4158 Current Platform Text *.txt
xlDBF2 7 Dbase 2 format *.dbf
xlDBF3 8 Dbase 3 format *.dbf
xlDBF4 11 Dbase 4 format *.dbf
xlDIF 9 Data Interchange format *.dif
xlExcel12 50 Excel Binary Workbook *.xlsb
xlExcel2 16 Excel version 2.0 (1987) *.xls
xlExcel2FarEast 27 Excel version 2.0 far east (1987) *.xls
xlExcel3 29 Excel version 3.0 (1990) *.xls
xlExcel4 33 Excel version 4.0 (1992) *.xls
xlExcel4Workbook 35 Excel version 4.0. Workbook format (1992) *.xlw
xlExcel5 39 Excel version 5.0 (1994) *.xls
xlExcel7 39 Excel 95 (version 7.0) *.xls
xlExcel8 56 Excel 97-2003 Workbook *.xls
xlExcel9795 43 Excel version 95 and 97 *.xls
xlHtml 44 HTML format *.htm; *.html
xlIntlAddIn 26 International Add-In No file extension
xlIntlMacro 25 International Macro No file extension
xlOpenDocumentSpreadsheet 60 OpenDocument Spreadsheet *.ods
xlOpenXMLAddIn 55 Open XML Add-In *.xlam
xlOpenXMLStrictWorkbook 61 (&H3D) Strict Open XML file *.xlsx
xlOpenXMLTemplate 54 Open XML Template *.xltx
xlOpenXMLTemplateMacroEnabled 53 Open XML Template Macro Enabled *.xltm
xlOpenXMLWorkbook 51 Open XML Workbook *.xlsx
xlOpenXMLWorkbookMacroEnabled 52 Open XML Workbook Macro Enabled *.xlsm
xlSYLK 2 Symbolic Link format *.slk
xlTemplate 17 Excel Template format *.xlt
xlTemplate8 17 Template 8 *.xlt
xlTextMac 19 Macintosh Text *.txt
xlTextMSDOS 21 MSDOS Text *.txt
xlTextPrinter 36 Printer Text *.prn
xlTextWindows 20 Windows Text *.txt
xlUnicodeText 42 Unicode Text No file extension; *.txt
xlWebArchive 45 Web Archive *.mht; *.mhtml
xlWJ2WD1 14 Japanese 1-2-3 *.wj2
xlWJ3 40 Japanese 1-2-3 *.wj3
xlWJ3FJ3 41 Japanese 1-2-3 format *.wj3
xlWK1 5 Lotus 1-2-3 format *.wk1
xlWK1ALL 31 Lotus 1-2-3 format *.wk1
xlWK1FMT 30 Lotus 1-2-3 format *.wk1
xlWK3 15 Lotus 1-2-3 format *.wk3
xlWK3FM3 32 Lotus 1-2-3 format *.wk3
xlWK4 38 Lotus 1-2-3 format *.wk4
xlWKS 4 Lotus 1-2-3 format *.wks
xlWorkbookDefault 51 Workbook default *.xlsx
xlWorkbookNormal -4143 Workbook normal *.xls
xlWorks2FarEast 28 Microsoft Works 2.0 far east format *.wks
xlWQ1 34 Quattro Pro format *.wq1
xlXMLSpreadsheet 46 XML Spreadsheet *.xml

Constants enumeration (Excel)

This enumeration groups together constants used with various Excel methods.

Name Value Description
xl3DBar -4099 3D Bar
xl3DEffects1 13 3D Effects1
xl3DEffects2 14 3D Effects2
xl3DSurface -4103 3D Surface
xlAbove 0 Above
xlAccounting1 4 Accounting1
xlAccounting2 5 Accounting2
xlAccounting4 17 Accounting4
xlAdd 2 Add
xlAll -4104 All
xlAccounting3 6 Accounting3
xlAllExceptBorders 7 All Except Borders
xlAutomatic -4105 Automatic
xlBar 2 Automatic
xlBelow 1 Below
xlBidi -5000 Bidi
xlBidiCalendar 3 BidiCalendar
xlBoth 1 Both
xlBottom -4107 Bottom
xlCascade 7 Cascade
xlCenter -4108 Center
xlCenterAcrossSelection 7 Center Across Selection
xlChart4 2 Chart 4
xlChartSeries 17 Chart Series
xlChartShort 6 Chart Short
xlChartTitles 18 Chart Titles
xlChecker 9 Checker
xlCircle 8 Circle
xlClassic1 1 Classic1
xlClassic2 2 Classic2
xlClassic3 3 Classic3
xlClosed 3 Closed
xlColor1 7 Color1
xlColor2 8 Color2
xlColor3 9 Color3
xlColumn 3 Column
xlCombination -4111 Combination
xlComplete 4 Complete
xlConstants 2 Constants
xlContents 2 Contents
xlContext -5002 Context
xlCorner 2 Corner
xlCrissCross 16 CrissCross
xlCross 4 Cross
xlCustom -4114 Custom
xlDebugCodePane 13 Debug Code Pane
xlDefaultAutoFormat -1 Default Auto Format
xlDesktop 9 Desktop
xlDiamond 2 Diamond
xlDirect 1 Direct
xlDistributed -4117 Distributed
xlDivide 5 Divide
xlDoubleAccounting 5 Double Accounting
xlDoubleClosed 5 Double Closed
xlDoubleOpen 4 Double Open
xlDoubleQuote 1 Double Quote
xlDrawingObject 14 Drawing Object
xlEntireChart 20 Entire Chart
xlExcelMenus 1 Excel Menus
xlExtended 3 Extended
xlFill 5 Fill
xlFirst 0 First
xlFixedValue 1 Fixed Value
xlFloating 5 Floating
xlFormats -4122 Formats
xlFormula 5 Formula
xlFullScript 1 Full Script
xlGeneral 1 General
xlGray16 17 Gray16
xlGray25 -4124 Gray25
xlGray50 -4125 Gray50
xlGray75 -4126 Gray75
xlGray8 18 Gray8
xlGregorian 2 Gregorian
xlGrid 15 Grid
xlGridline 22 Gridline
xlHigh -4127 High
xlHindiNumerals 3 Hindi Numerals
xlIcons 1 Icons
xlImmediatePane 12 Immediate Pane
xlInside 2 Inside
xlInteger 2 Integer
xlJustify -4130 Justify
xlLast 1 Last
xlLastCell 11 Last Cell
xlLatin -5001 Latin
xlLeft -4131 Left
xlLeftToRight 2 Left To Right
xlLightDown 13 Light Down
xlLightHorizontal 11 Light Horizontal
xlLightUp 14 Light Up
xlLightVertical 12 Light Vertical
xlList1 10 List1
xlList2 11 List2
xlList3 12 List3
xlLocalFormat1 15 Local Format1
xlLocalFormat2 16 Local Format2
xlLogicalCursor 1 Logical Cursor
xlLong 3 Long
xlLotusHelp 2 Lotus Help
xlLow -4134 Low
xlLTR -5003 LTR
xlMacrosheetCell 7 MacrosheetCell
xlManual -4135 Manual
xlMaximum 2 Maximum
xlMinimum 4 Minimum
xlMinusValues 3 Minus Values
xlMixed 2 Mixed
xlMixedAuthorizedScript 4 Mixed Authorized Script
xlMixedScript 3 Mixed Script
xlModule -4141 Module
xlMultiply 4 Multiply
xlNarrow 1 Narrow
xlNextToAxis 4 Next To Axis
xlNoDocuments 3 No Documents
xlNone -4142 None
xlNotes -4144 Notes
xlOff -4146 Off
xlOn 1 On
xlOpaque 3 Opaque
xlOpen 2 Open
xlOutside 3 Outside
xlPartial 3 Partial
xlPartialScript 2 Partial Script
xlPercent 2 Percent
xlPlus 9 Plus
xlPlusValues 2 Plus Values
xlReference 4 Reference
xlRight -4152 Right
xlRTL -5004 RTL
xlScale 3 Scale
xlSemiautomatic 2 Semiautomatic
xlSemiGray75 10 SemiGray75
xlShort 1 Short
xlShowLabel 4 Show Label
xlShowLabelAndPercent 5 Show Label and Percent
xlShowPercent 3 Show Percent
xlShowValue 2 Show Value
xlSimple -4154 Simple
xlSingle 2 Single
xlSingleAccounting 4 Single Accounting
xlSingleQuote 2 Single Quote
xlSolid 1 Solid
xlSquare 1 Square
xlStar 5 Star
xlStError 4 St Error
xlStrict 2 Strict
xlSubtract 3 Subtract
xlSystem 1 System
xlTextBox 16 Text Box
xlTiled 1 Tiled
xlTitleBar 8 Title Bar
xlToolbar 1 Toolbar
xlToolbarButton 2 Toolbar Button
xlTop -4160 Top
xlTopToBottom 1 Top To Bottom
xlTransparent 2 Transparent
xlTriangle 3 Triangle
xlVeryHidden 2 Very Hidden
xlVisible 12 Visible
xlVisualCursor 2 Visual Cursor
xlWatchPane 11 Watch Pane
xlWide 3 Wide
xlWorkbookTab 6 Workbook Tab
xlWorksheet4 1 Worksheet4
xlWorksheetCell 3 Worksheet Cell
xlWorksheetShort 5 Worksheet Short

 

 

 Excel Object Model Enumerations

https://docs.microsoft.com/en-us/office/vba/api/excel(enumerations)

XlAutoFilterOperator Enumeration

Name Value Description
xlAnd 1 Logical AND of Criteria1 and Criteria2.
xlBottom10Items 4 Lowest-valued items displayed (number of items specified in Criteria1).
xlBottom10Percent 6 Lowest-valued items displayed (percentage specified in Criteria1).
xlFilterCellColor 8 Color of the cell
xlFilterDynamic 11 Dynamic filter
xlFilterFontColor 9 Color of the font
xlFilterIcon 10 Filter icon
xlFilterValues 7 Filter values
xlOr 2 Logical OR of Criteria1 or Criteria2.
xlTop10Items 3 Highest-valued items displayed (number of items specified in Criteria1).
xlTop10Percent 5 Highest-valued items displayed (percentage specified in Criteria1).

 

xlWindowState enumeration (Excel)

Name Value Description
xlMaximized -4137 Maximized
xlMinimized -4140 Minimized
xlNormal -4143 Normal

xlSortOrder enumeration (Excel)

Specifies the sort order for the specified field or range.

Name Value Description
xlAscending 1 Sorts the specified field in ascending order. This is the default value.
xlDescending 2 Sorts the specified field in descending order.

 

xlCellType enumeration (Excel)

Specifies the type of cells.

Name Value Description
xlCellTypeAllFormatConditions -4172 Cells of any format.
xlCellTypeAllValidation -4174 Cells having validation criteria.
xlCellTypeBlanks 4 Empty cells.
xlCellTypeComments -4144 Cells containing notes.
xlCellTypeConstants 2 Cells containing constants.
xlCellTypeFormulas -4123 Cells containing formulas.
xlCellTypeLastCell 11 The last cell in the used range.
xlCellTypeSameFormatConditions -4173 Cells having the same format.
xlCellTypeSameValidation -4175 Cells having the same validation criteria.
xlCellTypeVisible 12 All visible cells.

xlYesNoGuess enumeration (Excel)

Specifies whether or not the first row contains headers. Cannot be used when sorting PivotTable reports.

Name Value Description
xlGuess 0 Excel determines whether there is a header, and where it is, if there is one.
xlNo 2 Default. The entire range should be sorted.
xlYes 1 The entire range should not be sorted