powershell -- the vbProject property of an Excel workbook isn't returning a VBProject object

Question

Supposedly the Workbook.VBProject property "Returns a VBProject object that represents the Visual Basic project in the specified workbook" excel.workbook.vbproject

It doesn't seem to be working as billed. I'm using 1: https://gist.github.com/atifaziz/8819159 as a guide. l know my workbook has a VBA project:

Here's the code I'm testing (including the "hack" from code I'm using as a guide):

# create an instance of excel  
$a = New-Object -comobject Excel.Application  
  
# suppress dialog boxes  
$a.DisplayAlerts = $False  
  
# make it visible  
$a.Visible = $True  
  
# open an existing macro-enabled workbook  
$b = $a.Workbooks.Open("\\servername\Informatics\Macros\chris\corlewCostComparison01092019.xlsm")  
  
$b | Get-Member | Out-Null  # HACK! Don't know why but next line doesn't work without this (this line is in the code I'm emulating)  
$project = $b.VBProject  
  
$project | get-member  
  
Here's the error message I get:  
  
  
PS H:\> $project | get-member  
get-member : You must specify an object for the Get-Member cmdlet.  
At line:1 char:12  
+ $project | get-member  
+            ~~~~~~~~~~  
    + CategoryInfo          : CloseError: (:) [Get-Member], InvalidOperationException  
    + FullyQualifiedErrorId : NoObjectInGetMember,Microsoft.PowerShell.Commands.GetMemberCommand  

Anyone know why the Workbook.VBProject property isn't returning a VBProject object?

Christian Bahnsen

Answer 1

Hi,

You need to check "Trust access to the VBA project object model" in Trust Center or add the following line to your script

New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$($a.Version)\Excel\Security" -Name AccessVBOM -Value 1 -Force  

Best Regards，
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.