question

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

Powershell, VBA, and Excel

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-powershelloffice-vba-dev
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

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

$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()

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.