How to use Powershell to enforce formatting of an Excel Column?

Question

I'm writing a Powershell script to enforce "General Formatting" of Column AI. This is what I have and it didn't appear to execute.

$worksheet.columns.items('AI').GeneralFormat = $true

What is the right Powershell code for such a task?

General Formatting:

score 4 · Accepted Answer · answered May 22 '17 at 16:02

The clue is in your screenshot; the tooltip specifies this is the "Number Format" setting.

$worksheet.Columns("AI").NumberFormat = "General"

Check out the MSDN page on NumberFormat. Unfortunately it doesn't list the allowed values; this StackOverflow q/a appears to cover that.

score 3 · Answer 2 · answered Apr 25 '18 at 15:27

3

This works

$Excel01 = New-Object -ComObject Excel.Application
$Excel01.Visible = $True
$Workbook01 = $Excel01.Workbooks.Add()
$Worksheet01 = $Workbook01.Sheets.Item(1)
$Worksheet01.Activate()
$Excel01.Columns.item('d').NumberFormat = "0"

answered Apr 25 '18 at 15:27

Leo Louis

31
1

What value does this add over the accepted answer posted a year earlier? – Jean-François Corbett Apr 25 '18 at 15:47

How to use Powershell to enforce formatting of an Excel Column?

2 Answers2