Handy PowerShell Commands for Excel

Excel PowerShell Commands

In this blog, I want to discuss a few commands that can be used when we have to do an update on Excel cells using PowerShell. Recently I was working on a script for updating an exported Excel document, and finding  commands for various functions in one place was difficult. Here are a few commands that are helpful for updating an Excel document using the power of PowerShell:

Open a New Excel Workbook/Worksheet

$excel = New-Object -Com Excel.Application
$wb = $excel.Workbooks.Add()
$ws = $wb.Worksheets.Add()

Load an Already Existing Excel File

$excel = New-Object -Com Excel.Application
$importFile = 'C:\Users\User\Downloads\exceldoc.xlsx'
#Open the importfile for edit.
$wb = $excel.Workbooks.Open($importFile)

Get the Worksheet and Rename It

$ws = $wb.sheets.item(1)
$ws.name = "Asset"

Get the Last Row and Last Column Used

This could be used in iterating through the used range in a worksheet.

$ws.UsedRange.columns.count
$ws.UsedRange.rows.count

Read Value From a Cell

$header= $ws.Cells.Item(1, 1).text

Add or Modify Value in a Cell

#update the cell with 'New value'
$ws.Cells.Item(1,1).Value="New value"

Delete Entire Row or Column

[void]$ws.Cells.Item(1,1).EntireColumn.Delete()

[void]$ws.Cells.Item(1,1).EntireRow.Delete()

Save the Workbook

$excel.Save()

Quit the Workbook

$excel.Quit()

Conclusion

These commands can be used together to mass update the cells based on conditions and iterations. You could add many more to the above list, but the ones mentioned are the ones I use and find most helpful for mass updations.

Thanks for reading!