By Example – PowerShell commands for Excel
I’ve been having some fun recently playing around cleaning up an excel document, it needed to be automated as a new document is created every day. Looking around for help on using PowerShell with Excel it seems that mainly people wanted to create a sheet with new data from Posh and finding other information was rather more digging than I would have liked, I’ve decided to put a collection of commands in a central location which hopefully people will find useful.
##################################################################### # Open New Excel Workbook / WorkSheet $Excel = new-object -comobject excel.application $ExcelWordBook = $Excel.Workbooks.Add() $ExcelWorkSheet = $ExcelWordBook.Worksheets.Add() $Excel.Visible = $true ##################################################################### ## Load Excel file $ExcelPath = 'C:\KM_Main.xlsx' $Excel = New-Object -ComObject Excel.Application $Excel.Visible = $false $ExcelWordBook = $Excel.Workbooks.Open($ExcelPath) $ExcelWorkSheet = $Excel.WorkSheets.item(&quot;Sheet 1&quot;) ##################################################################### # Close connections to Excel # set interactive to false so no save buttons are shown $Excel.DisplayAlerts = $false $Excel.ScreenUpdating = $false $Excel.Visible = $false $Excel.UserControl = $false $Excel.Interactive = $false ## save the workbook $Excel.Save() ## quit the workbook $Excel.Quit() ## function to close all com objects function Release-Ref ($ref) { ([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0) [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers() } ## close all object references Release-Ref($ExcelWorkSheet) Release-Ref($ExcelWordBook) Release-Ref($Excel) ##################################################################### # Change to a different Worksheet $ExcelWorkSheet = $Excel.WorkSheets.item("Sheet 2") ##################################################################### # Update / Insert / Delete Value in a Cell $ExcelWorkSheet.Cells.Item(1,1).Value2 = "New Value" ##################################################################### # Read Cell $ExcelWorkSheet.Cells.Item(1,1).Text ##################################################################### # Delete Row / Column [void]$ExcelWorkSheet.Cells.Item(1,1).EntireColumn.Delete() [void]$ExcelWorkSheet.Cells.Item(1,1).EntireRow.Delete() ##################################################################### # Find Last Used Column or Row $ExcelWorkSheet.UsedRange.columns.count $ExcelWorkSheet.UsedRange.rows.count ##################################################################### # Sorting $table = $ExcelWorkSheet.ListObjects | where DisplayName -EQ "User_Table" $table.Sort.SortFields.clear() $table.Sort.SortFields.add( $table.Range.Columns.Item(1) ) $table.Sort.apply() ##################################################################### # Clear all formatting on a sheet $tableRange = $ExcelWorkSheet.UsedRange $tableRange.ClearFormats() ##################################################################### # Using Excel Table Styles&lt; ## formatting from &lt;a href="http://activelydirect.blogspot.co.uk/2011/03/write-excel-spreadsheets-fast-in.html"&gt;http://activelydirect.blogspot.co.uk/2011/03/write-excel-spreadsheets-fast-in.html&lt;/a&gt; $listObject = $ExcelWorkSheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $ExcelWorkSheet.UsedRange, $null,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes,$null) $listObject.Name = "User Table" $listObject.TableStyle = "TableStyleLight10" ##################################################################### # Auto-Sizing Columns / Rows $ExcelWorkSheet.UsedRange.Columns.Autofit() | Out-Null ##################################################################### # Formatting a Column $ExcelWorkSheet.columns.item($formatcolNum).NumberFormat = "yyyy/mm/dd" ##################################################################### # Formatting Text / Numbers Colours # <a href="http://theolddogscriptingblog.wordpress.com/2009/08/04/adding-color-to-excel-the-powershell-way/">http://theolddogscriptingblog.wordpress.com/2009/08/04/adding-color-to-excel-the-powershell-way/</a> ##################################################################### # Format Text / Numbers Bold $ExcelWorkSheet.Cells.Item(1,1).Font.Bold=$True ##################################################################### # Add Hyperlink to cell $link = "http://www.microsoft.com/technet/scriptcenter" $r = $ExcelWorkSheet.Range("A2") [void]$ExcelWorkSheet.Hyperlinks.Add($r, $link) ##################################################################### # Add Comment to Cell $ExcelWorkSheet.Range("D2").AddComment("Autor Name: `rThis is my comment") ##################################################################### # Add a Picture to a Comment $image = "C:\test\Pictures\Kittys\gotyou.jpg" $ExcelWorkSheet.Range("C1").AddComment() $ExcelWorkSheet.Range("d3").Comment.Shape.Fill.UserPicture($image) ##################################################################### # Fix Location and Size of comment $ExcelWorkSheet.Range("D3").Comment.Shape.Left = 100 $ExcelWorkSheet.Range("D3").Comment.Shape.Top = 100 $ExcelWorkSheet.Range("D3").Comment.Shape.Width = 100 $ExcelWorkSheet.Range("D3").Comment.Shape.Height = 100 ##################################################################### # Making a Comment/s visible $comments = $ExcelWorkSheet.comments foreach ($c in $comments) { $c.Visible = 1 } ##################################################################### # Add a Formula $formula = "=8*8" $ExcelWorkSheet.Cells.Item(1,1).Formula = $formula
I hope these are as helpful to you as they are to me! ill keep updating this post when i find other commands i find interesting.
Hi,
Have you found a command that allows you to reorganize the order of the columns? Not necessarily alphabetically or anything, but simply setting column titled “Name”, for example, as column A and so on.
Thanks!
Hi Matias,
Not sure what you mean? are you talking about filtering a column to a value?
Hello I am trying to automate a special report and it uses excel, I was wondering if you know of a way to add a blank cell then another cell with a title after a report is ran so when the next part of the report is starting it will put a space and then I can put a new title in. So my order is Title-Report-Blank Cell- Title- Report etc etc etc.
Hi Jedediah,
Would something like this suit?