PowerShell: Remove VBA module from excel file

Question

What I want to do:

• Open an excel file
• Dynamically import a VBA module and run a function from the module
• Remove the module

So here is my powershell code:

$excel = New-Object -ComObject Excel.Application
$excel.Workbooks.Open($filepath) | Out-Null
$macro = $excel.ActiveWorkbook.VBProject.VBComponents.Import($MacroFilepath)
$Excel.ActiveWorkbook.Application.Run("HoursSumCounter.main") | Out-Null
$excel.ActiveWorkbook.VBProject.VBComponents.Remove($macro)

(Naturally I enabled accessing the VBA project in the trust center settings of Excel in order to be able to import a module dynamically)

Now the error that I get is the following:

Cannot find an overload for "Remove" and the argument count: "1".
At line:1 char:1
+ $excel.ActiveWorkbook.VBProject.VBComponents.Remove($macro)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

This whole thing actually works if I do this within Excel (no powershell).

But here is what I found out already...

I checked the overload of the Remove function:

[DBG]: PS C:\Users\MUT2BP\Desktop\recefice>> $excel.ActiveWorkbook.VBProject.VBComponents.Remove

OverloadDefinitions                                                                                                                                                                           
-------------------                                                                                                                                                                           
void Remove(Microsoft.Vbe.Interop.VBComponent VBComponent)                                                                                                                                    
void _VBComponents.Remove(Microsoft.Vbe.Interop.VBComponent VBComponent)                                                                                                                      
void _VBComponents_Old.Remove(Microsoft.Vbe.Interop.VBComponent VBComponent) 

It turned out that I actually should pass an object of type Microsoft.Vbe.Interop.VBComponent VBComponent however my $macro object is of type System.__ComObject#{eee00921-e393-11d1-bb03-00c04fb6c4a6}

[DBG]: PS C:\Users\MUT2BP\Desktop\recefice>> $macro | Get-Member


   TypeName: System.__ComObject#{eee00921-e393-11d1-bb03-00c04fb6c4a6}

Name            MemberType Definition                        
----            ---------- ----------                        
Activate        Method     void Activate ()                  
DesignerWindow  Method     Window DesignerWindow ()          
Export          Method     void Export (string)         
...

...Even though the Remove function actually returns a type of VBComponent, during this OLE automation process it gets converted to a COM object.

I'm just suspecting that I have to somehow convert this COM object to an actual VBComponent object, how ever I cannot cast it explicitly.

Answer 1

had to do something similar recently, this how i did it.

$Code = @'
your code here
Make sure this guy is public
@'

$Excel = new-object -com Excel.Application
#Need to change security settings

New-ItemProperty -Path `
"HKCU:\Software\Microsoft\Office\$($Excel.Version)\excel\Security" -Name ` 
AccessVBOM -Value 1 -Force | Out-Null

New-ItemProperty -Path `
"HKCU:\Software\Microsoft\Office\$($Excel.Version)\excel\Security" -Name `
VBAWarnings -Value 1 -Force | Out-Null

$Workbook = $Excel.Workbooks.open("Insert Path to file here",$true)
$xlModule = $Workbook.VBProject.VBComponents.Add(1)
$Module = $xlmodule.CodeModule.AddFromString($Code)
$Excel.Run("Name of Module here, make sure that the sub is public")
$Workbook.VBProject.VBComponents.Remove($xlmodule)
$Workbook.Close($True)