Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PowerScript and VBScript for using an Excel Macro from an external macro source on a folder
$comments = @'
Script name: DerpExcelGoodness.ps1
Thanks to Kent Finkle
Purpose: How can I use Windows Powershell to Open All the Excel Spreadsheets in a Folder
and Run a Specified Macro Found on those Spreadsheets?
Need to edit: $a = $xl.Run("C:\Users\<USER>\Documents\PERSONAL.XLSB!Clean")
directory dont work yet - this is stored in Documents Folder
Macros can be called in daisy chain.
File types to remember: 6 - csv; 51 - xlsx
'@
#-----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
#-----------------------------------------------------
$files = dir("E:\DIRECTORY\*.csv")
# Could be any filetype that excel can open
$xl = new-object -comobject excel.application
$xl.Visible = $True
$xl.DisplayAlerts = $False
foreach ($f In $files) {
#Best practice: call open the Macro file here to AVOID random errors
$wb = $xl.Workbooks.Open("C:\Users\relschool\Documents\macro.xlsb")
#Best practice: call open any Reference/lookup files here to AVOID random errors
$wb = $xl.Workbooks.Open("C:\Users\relschool\Documents\Student Summary.xlsx")
$wb = $xl.Workbooks.Open($f.FullName)
$ws = $wb.Worksheets.Item(1)
# Uncomment anything you need to do - can be done in successive series of each other.
# $a = $xl.Run("macro.xlsb!clean")
# $a = $xl.Run("macro.xlsb!gradesheet")
# $a = $xl.Run("macro.xlsb!protect")
# $a = $xl.Run("macro.xlsb!unprotect")
# Can Convert on the fly, as well as append (only suffix?) names
# $a = $wb.SaveAs($f.FullName + "-clean.csv", 6)
# $a = $wb.SaveAs($f.FullName + "-gradesheet.xlsx", 51)
# $a = $wb.SaveAs($f.FullName, 51)
$a = $wb.Close()
}
$a = $xl.Quit()
$a = Release-Ref($ws)
$a = Release-Ref($wb)
$a = Release-Ref($xl)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.