PowerShell with Microsoft Excel Macro

Tue, 17 Jan 2017
Tags

Create PowerShell Script to open and run Macro in Excel

Below is an example of how to use PowerShell to automatically run an Excel macro.

Function RunExcelMacro() {
    # Open Excel file
    $excel = new-object -comobject excel.application
    $filePath = "C:\PowershellSheet.xlsm"
    $workbook = $excel.Workbooks.Open($FilePath)
    $excel.Visible = $true
    $worksheet = $workbook.worksheets.item(1)
    Write-Host "Running macro in excel to scrub data."
    $excel.Run("PowershellMacro")
    $workbook.save()
    $workbook.close()
    $excel.quit()
    Write-Host "Closed Excel"
}

This code calls creates a COM Excel object and then opens an Excel Macro called “PowershellMacro”. After it executes the macro then we explicitly quit excel and the process closes.

Here is the xlsm file: PowershellSheet.xlsm

Here is the ps1 file: PowershellExcelMacro.ps1

Here are some good references on how to use Excel inside of PowerShell.

Loading...
paul b castillo

Paul B Castillo - Web developer with a passion for automation.