Saving VBA Script to Microsoft Excel Objects > thisworbook.cls

Mark Verhunce 41 Reputation points
2022-05-26T19:43:39.697+00:00

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 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,364 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. kinuasa 356 Reputation points
    2022-05-30T04:06:30.99+00:00

    Specify ThisWorkbook in the VBComponent object. 

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