Powershell, VBA, and Excel

Mark Verhunce 41 Reputation points
2022-05-25T20:16:24.68+00:00

I have a vba script that automatically auto sizes columns in all of the worksheets in an excel file which runs fine, however I have a couple of questions

  1. As the script is running I receive the following dialog boxes.asking if I want to save changes to test.xlsx and I click yes, then file explorer opens and has copy of test.xlsx in the filename box and I remove "copy of" and then I need to click on save. At this point I can choose .xlsm in the save file type as and choose .xlsm
  2. Once step 1. is completed, I get a dialog box asking if I want to save the workbook as a macro free workbook and I select no and then file explorer opens again with test.xlsx as the filename and I need to choose save file type as and choose .xlsm.

How do I save the changes to .xlsx as text.xlsx and then save test.xlsx as a macro enabled workbook .xlsm
Here is the code

$Excel = New-Object -ComObject Excel.Application
$ExcelVersion = $Excel.Version

New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$ExcelVersion\Excel\Security" -Name AccessVBOM -Value 1 -Force | Out-Null
New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$ExcelVersion\Excel\Security" -Name VBAWarnings -Value 1 -Force | Out-Null

$workbook = $Excel.Workbooks.Open("C:\Temp.xlsx")
$AutofitModule = $workbook.VBProject.VBComponents.Add(1)
$AutoFit = @"
Sub autofit_columns()

Worksheets("Sheet1").UsedRange.EntireColumn.autofit
Worksheets("Sheet2").UsedRange.EntireColumn.autofit

End Sub

"@
$AutoFitModule.CodeModule.AddFromString($Autofit)

$Excel.Workbooks.Close()
$Excel.Quit()

Also, when I open the test.xlsm file the macro works when I click Alt + F8 and then run it works. However, I believe that it should work automatically when I open test.xlsm.

Any help would be greatly appreciated.

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

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mark Verhunce 41 Reputation points
    2022-05-26T15:33:27.333+00:00

    $Excel = New-Object -ComObject Excel.Application
    $ExcelVersion = $Excel.Version

    New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$ExcelVersion\Excel\Security" -Name AccessVBOM -Value 1 -Force | Out-Null
    New-ItemProperty -Path "HKCU:\Software\Microsoft\Office\$ExcelVersion\Excel\Security" -Name VBAWarnings -Value 1 -Force | Out-Null

    $workbook = $Excel.Workbooks.Open("C:\Temp.xlsx")
    $ExcelFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbookMacroEnabled
    $AutofitModule = $workbook.VBProject.VBComponents.Add(1)
    $AutoFit = @"
    Sub autofit_columns()

    Worksheets("Sheet1").UsedRange.EntireColumn.autofit
    Worksheets("Sheet2").UsedRange.EntireColumn.autofit

    End Sub

    "@
    $AutoFitModule.CodeModule.AddFromString($Autofit)
    $workbook.SaveAs($XLSMFile,$ExcelFixedFormat)
    $Excel.Workbooks.Close()
    $Excel.Quit()

    0 comments