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

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

Qiita:PowerShell で Excel をどうのこうのすることに興味を持ってくれると嬉しい

PowerShellでExcelを操作する - シートの操作編 -

Last modified October 11, 2022: update (8216439)