Running VBA macro in Excel - Error During Saving File

0
votes

I am trying to create PowerShell Script which allows me to open excel file, run macro, then save file and close it. My code is below:

$excel = New-Object -ComObject Excel.Application
$MacroName1 = "Extraction_Tool.xlsm"
$currentExecutingPath = $fullPathIncFileName.Replace($currentScriptName, "") + $MacroName
$ExcelFiles = Get-ChildItem -Path $currentExecutingPath

$Workbook = $excel.Workbooks.Open($currentExecutingPath)

$excel.Application.DisplayAlerts = $False
$excel.Run("refreshall")
$excel.ActiveWorkbook.Save()
$workbook.Close()
$excel.Quit()

When I am running this script I receive error:

You cannot call a method on a null-valued expression.
At W:\GDA_files\SME_iQA\iQA_NEW\PrepareMacro.ps1:10 char:1
+ $excel.ActiveWorkbook.Save()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

VBA macro (refreshall) takes about one minute. Do you think that script cannot save file because it is trying to do this while macro is running?

2
excelvbapowershell
The error message clearly says that $excel.ActiveWorkbook is empty. Try $workbook.Save() instead. - Ansgar Wiechers
Hello Ansgar, Thanks for reply. With code: $workbook.Save() $workbook.Close() I receive: Method invocation failed because [System.__ComObject] doesn't contain a method named 'Save'. At W:\GDA_files\SME_iQA\iQA_NEW\PrepareMacro.ps1:10 char:1 + $workbook.Save() + ~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (Save:String) [], RuntimeException + FullyQualifiedErrorId : MethodNotFound - Klinik

2 Answers

0
votes

Have you tried the code below?

$excel.Application.ActiveWorkbook.Save()

I am not sure how to Excel object is operating but ActiveWorkbook is definitely null in it's current form. So the call looks to be incorrectly formed. I would recommend running this on a single test-spreadsheet from Command Line step by step and you should come across the root of your error pretty quickly.

0
votes

It's failing because you're launching you code outside of Excel, which mean that there isn't any Active Workbook at the time. Also, it's always good to mention that you're using an application method when launching code from outside Excel, so it knows what you're trying to do.

I would go for something like

$excel.Application.Workbooks(1).Save()

But if you already have a workbook opened it will fail. The best thing would be to determine the name of the workbook and then call said name like 

$excel.Application.Workbooks("<workbook name>").Save()