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?

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
– Siva Senthil, Commented Feb 25, 2016 at 5:34

Siva Senthil · Accepted Answer · 2016-03-02 07:00:54Z

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.

Collectives™ on Stack Overflow

Accessing Excel VBA editor from Powershell

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related