Submitted By: Kent Finkle

Opens all the spreadsheets in a folder and runs a macro found in each of those spreadsheets.

PowerShell
Edit|Remove
$comments = @'
Script name: OpenRun-Macro.ps1
Created on: Thursday, September 13, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to
Open All the Excel Spreadsheets in a Folder 
and Run a Specified Macro Found in Each of Those Spreadsheets?
'@
#-----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers() 
}
#-----------------------------------------------------
$files = dir("c:\scripts\test\*.xls")
 
$xl = new-object -comobject excel.application
$xl.Visible = $True

$xl.DisplayAlerts = $False
 
foreach ($f In $files) { 
    $wb = $xl.Workbooks.Open($f.FullName)
    $ws = $wb.Worksheets.Item(1)
    $a = $xl.Run("BoldfaceHeadings")
    $a = $wb.SaveAs($f.Name)
    $a = $wb.Close()
}
$a = $xl.Quit()
 
$a = Release-Ref($ws)
$a = Release-Ref($wb)
$a = Release-Ref($xl)