I am trying to use a Powershell script to open an excel file and then run a macro then save and close the excel file. Below is the powershell script i have written. It will open the excel file and save the file, but I cannot figure out how to access the macro. Any suggestions? Thanks
$excel = new-object -comobject excel.application
$FilePath = "C:\AESOP\prempexp0011.xlsx"
$workbook = $excel.Workbooks.Open($FilePath)
$excel.Visible = $true
cd -path "C:\Users\Nick.Clark\AppData\Roaming\Microsoft\Excel\XLSTART"
$macroBook = $excel.Workbooks.Open($path"PERSONAL.XLSB")
$worksheet = $workbook.worksheets.item(1)
Write-Host "Running macro in excel to scrub data."
$excel.Run("Personal.XLSB!AESOP_SUB")
$macroBook.Close()
$SavePath = "C:\AESOP\SUB.csv"
$workbook.saveas($SavePath)
$workbook.close
$excel.quit()
Write-Host "Closed Excel"
Get-Process excel | Stop-Process -Force
$excel = new-object -comobject excel.application
$FilePath = "C:\AESOP\prempexp0011.xlsx"
$workbook = $excel.Workbooks.Open($FilePath)
$excel.Visible = $true
cd -path "C:\Users\Nick.Clark\AppData\Roaming\Microsoft\Excel\XLSTART"
$macroBook = $excel.Workbooks.Open($path"PERSONAL.XLSB")
$worksheet = $workbook.worksheets.item(1)
Write-Host "Running macro in excel to scrub data."
$excel.Run("Personal.XLSB!AESOP_SUB")
$macroBook.Close()
$SavePath = "C:\AESOP\SUB.csv"
$workbook.saveas($SavePath)
$workbook.close
$excel.quit()
Write-Host "Closed Excel"
Get-Process excel | Stop-Process -Force