CODEWITHSUNDEEP
excelvbapowershellautomation
ThomasMX
ThomasMX

Reputation: 1811

PowerShell: Remove VBA module from excel file

What I want to do:

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.

Upvotes: 6

Views: 2942

Answers (2)

tattmoney76
tattmoney76

Reputation: 502

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)

Upvotes: 1

Pip
Pip

Reputation: 11

is there a reason why you can't simply reference the appropriate module? excel does not require modules to be attached. modules in add-ins & personal workbooks can run on any sheet.

if you create an add-in (save as _ add-in, then under developer tools click the box to turn it on) you csn update it on the fly with whatever module you want.

if you dont have access to the developer tools tab, you can add the module to your personal workbook (this is a hidden workbook that can be auto generated when you first use the record macro button)

by doing any of the above you can open an excel file, run code on it, and then close the file without the hassle af attempting to programmatically add a module to a fike that does not support modules.

if you absolutely have to attach and remove the module, try this: open the file, convert to xlsb format, close file, rename file as .zip, add module and update xml path inside .zip, rename as .xlsb, open in excel, run module, save as file that does not support modules.

Upvotes: 0

Related Questions