Accessing Excel VBA editor from Powershell

Question

I want to execute the Project Properties command in the VBA menu of Excel application using Powershell.

Following is the script -

$excelApplication = New-Object -ComObject ("Excel.Application")
$excelApplication.Visible = $True

$workbook = $excelApplication.Workbooks.Open("C:\Workbooks\PasswordProtectedVBEFile.xlsm")
$appReference = $workbook.Application
$appReference.VBE.CommandBars(1).FindControl(2578, $True).Execute()

I encounter the error - You cannot call a method on a null-valued expression., an inspection revealed - $appReference.VBE is empty. i.e. Write-Host $appReference.VBE outputs empty line.

I want help in troubleshooting why do I get an empty value for VBE?

Try this: http://stackoverflow.com/questions/19536241/calling-excel-macros-from-powersheel-with-arguments — Indian, Feb 24 '16 at 13:15
Well the link addresses how to run a macro. I am asking about executing the menu command in VBA editor. They are two different things. — Siva Senthil, Feb 25 '16 at 05:34

score 2 · Accepted Answer · answered Mar 02 '16 at 07:00

I found the way out!

In short, we need to grant access to VBA Project Model explicitly before we could automate/access programmatically using script. Journey to this discovery was interesting. I stumbled on this git project which helped me troubleshoot the problem.

The solution to my problem was this security check which I found in referred project

$mo = Get-ItemProperty -Path HKCU:Software\Microsoft\Office\*\Excel\Security `
                       -Name AccessVBOM `
                       -EA SilentlyContinue | `
          ? { !($_.AccessVBOM -eq 0) } | `
          Measure-Object

This security check ensures VBA Project model is available programmatically. Well though this is just a check against registry, I wanted to know how to set a value. It was simple and one time activity in Excel.

You could allow such programmatic access by checking the check box "Trust access to the VBA Project model". This setting could be accessed by navigating in Excel (2010) File > Options > Trust Center > Trust Center Settings > Macro Settings.

Accessing Excel VBA editor from Powershell

1 Answers1