Powershell Reference vba projects > microsoft excel objects

I am using PowerShell to create open a .xlsx, then save it as a macro enabled workbook (.xlsm) and it adds a vb script to vba projects > modules with

$AutofitModule = $workbook.VBProject.VBComponents.Add(1)
$Autofit = @"
Sub workbook_open()
Worksheets("Sheet1").UsedRange.EntireColumn.autofit
End Sub
"@

$AutoFitModule.codemodule.AddFromString($Autofit)

Instead of saving this to vba projects > modules as the first image shows, I would like to save it to vbaprojects > Microsoft Excel Objects > Thisworkbook as the 2nd image shows.

CodePudding user response：

Tested and Working:

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$FilePath = 'C:\Temp\TestBook.xlsx'
$opendoc = $excel.Workbooks.Open($FilePath)
$excel.DisplayAlerts = $false
try{
    $opendoc.VBProject.VBComponents.Item("ThisWorkbook").CodeModule.InsertLines(1, 'Sub workbook_open()')
    $opendoc.VBProject.VBComponents.Item("ThisWorkbook").CodeModule.InsertLines(2, 'Worksheets("Sheet1").UsedRange.EntireColumn.autofit')
    $opendoc.VBProject.VBComponents.Item("ThisWorkbook").CodeModule.InsertLines(3, 'End Sub')
    $opendoc.SaveAs("C:\Temp\TestBook.xlsm", 52, [Type]::Missing, [Type]::Missing, $false, $false, 1, 2)
}
catch{
    $opendoc.close()
    $excel.quit()
}
$excel.quit()

CodePudding user response：

I found a work around. I should have posted my goal which is to create a workbook event that automatically runs when opening the .xlsm file.

I changed Sub Workbook_Open() to Sub Auto_Open and this worked.