January 17, 2017

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 are some good references on how to use Excel inside of PowerShell.

Here is the xlsm file: PowershellSheet.xlsm

Here is the ps1 file: PowershellExcelMacro.ps1

• Scripting guy
• Rambling Cookie Monster
• Posh2Scripting