question

MarkVerhunce-0269 avatar image
0 Votes"
MarkVerhunce-0269 asked kinuasa answered

Saving VBA Script to Microsoft Excel Objects > thisworbook.cls

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 Opbjects > Thisworkbook as the 2nd image shows.

Any help is greatly appreciated205906-vba-module.png205981-vba-workbook.png


windows-server-powershelloffice-vba-dev
vba-module.png (18.2 KiB)
vba-workbook.png (36.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

kinuasa avatar image
0 Votes"
kinuasa answered

Specify ThisWorkbook in the VBComponent object.

 $workbook.VBProject.VBComponents("ThisWorkbook").CodeModule.AddFromString($Autofit)


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.