Manipulate Excel with PowerShell
YouTube
Example: Create a new Excel file, edit it, and save it.
Example of creating a new test.xlsx.
# Create a new test.xlsx
try {
$excel = New-Object -ComObject Excel.Application # Start Excel
$excel.Visible = $false # Show or not show
$book = $excel.Workbooks.Add() # Add workbook
$sheet = $book.Sheets(1) # Get sheet
$sheet.Cells.Item(1, 1) = 100
$book.saveas("${HOME}\Desktop\test.xlsx") # Save Excel
}
finally {
$excel.Quit() # Close Excel
$excel = $null # Release the process
[GC]::Collect()
}
Example: Open, edit, and save Excel
Example of opening an already existing test.xlsx, editing it, and saving it over.
# Open the already existing test.xlsx, edit it, and save it overwritten.
try {
$excel = New-Object -ComObject Excel.Application # Start Excel
$excel.Visible = $false # Show or not show
$book = $excel.Workbooks.Open("${HOME}\Desktop\test.xlsx") # Open the book
$sheet = $book.Sheets(1) # Get sheet
$sheet.Cells.Item(1, 1) = [int]$sheet.Cells.Item(1, 1).text + 1
$book.Save() # Overwrite and save Excel
}
finally {
$excel.Quit() # Close Excel
$excel = $null # Release the process
[GC]::Collect()
}
Example: Running Excel macro using PowerShell
Example of running Excel macro using PowerShell.
Increment.ps1
try {
# Create an object
$xls = New-Object -ComObject Excel.Application
$xls.Visible = $false
$book = $xls.workbooks.open([string](Resolve-Path (Get-ChildItem "sample.xlsm")))
# Run the macro
$xls.Run("Increment")
# Save
$book.Save()
}
finally {
$xls.Quit()
$xls = $null
[GC]::Collect()
}
sample.xlsm
Example of macro.
Sub Increment()
Cells(1, 1) = Cells(1, 1) + 1
End Sub
Sub Reset()
Cells(1, 1) = 0
End Sub
Reference Links
Sheets object (Excel)| Microsoft Docs
Workbooks.Add method (Excel) | Microsoft Docs
Workbook.SaveAs method (Excel) | Microsoft Docs
Workbook.Save method (Excel) | Microsoft Docs
Workbooks.Open method (Excel) | Microsoft Docs
Application.Run method (Excel) | Microsoft Docs
Application.Quit method (Excel) | Microsoft Docs