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

Christian Bahnsen -- .mil account 201 Reputation points
2021-01-05T15:06:07.533+00:00

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:
53654-vbaproject01052021.jpg

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

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,560 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ian Xue 37,711 Reputation points Microsoft Vendor
    2021-01-06T08:01:54.987+00:00

    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.


0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.