Macros missing when opening Excel file with PowerShell

1
votes

The purpose of the PowerShell script I am trying to write is - Open an excel document and run a macro. (The macro is stored in the Personal Workbook file - personal.xlsb and it runs successfully)

The problem - When the excel file is opened via PowerShell code, it opens fine but when the macro is run by calling Run("NameOfMacro"), I get an error stating macro not found. I try to find the macro in the opened excel file and it does not appear in the macro list. This seems to be because the personal.xlsb file is not loading.

So I close the excel file, open it manually, but the macro is still missing. I then manually open the personal.xlsb file and notice that the macro exists however the macro still does not appear in the excel file or any other excel file I open thereafter.
After a frustrating hour trying to understand why is the personal.xlsb file stopped loading suddenly. I noticed another thing - if I kill the excel.exe process that was started by the PowerShell code (via task manager window) and then open any excel document the macro is available!!

So I have narrowed down the issue to this - The macros are not available because the Personal.xlsb is NOT opened/loaded WHEN I open excel file via PowerShell.

Does anyone have an idea why is this happening and what do I need to do to resolve this?

Code reference

$excel = New-Object -comobject Excel.Application
$FilePath = "D:\DailyReport.xls"
$workbook = $excel.Workbooks.Open($FilePath)
$excel.Visible = $true
$worksheet = $workbook.worksheets.item(1)
$excel.Run("SelectDataFromReport")

Error I get -

Exception calling "Run" with "31" argument(s): "Cannot run the macro 'SelectDataFromReport'. The macro may not be available in this workbook or all macro s may be disabled."
At line:18 char:11 + $excel.Run <<<< ("SelectDataFromReport") + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException

By the way, I use the 64 bit PowerShell console by default but I also tried running the code using 32 bit console but it didn't make a difference. In excel I have added the location as a trusted location and i have allowed excel files to be opened by default in edit mode.

2
excelpowershell-2.0

2 Answers

2
votes

you can call the macro within the personal.xlsb like so:

$excel = New-Object -comobject Excel.Application
$wbPersonalXLSB = $excel.workbooks.open("$env:USERPROFILE\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLSB")
$FilePath = "D:\DailyReport.xls"
$workbook = $excel.Workbooks.Open($FilePath)
$excel.Visible = $true
$worksheet = $workbook.worksheets.item(1)
$excel.Run("PERSONAL.XLSB!SelectDataFromReport")
$wbPersonalXLSB.Close()
$workbook.save()
$workbook.close()
$excel.quit()
0
votes
$x1 = New-Object -comobject Excel.Application 
$wb = $x1.workbooks.open("$env:C:\teste\testamacro.xlsm") 
$FilePath = "c:\teste\testamacro" 
$workbook = $x1.Workbooks.Open($FilePath) 
$x1.Visible = $true 
$worksheet = $workbook.worksheets.item(1)     
$x1.Run("testamacro.xlsm!SelectDataFromReport")    
$wb.Close() $workbook.save()`

$workbook.close() 
$x1.quit()