Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3111
  • Last Modified:

How do I format a number cell to Accounting in Excel using Powershell?

I have a Powershell script that creates an Excel spreadsheet. I would like to format some of the cells with numbers to Accounting with 0 decimal places and no symbol. How do I do this? For example, how would I change the format to Accounting for the numbers in column 2 on rows 7-12 in my snippet code below?
$Excel = New-Object -Com Excel.Application
$Excel.visible = $False
$Excel.DisplayAlerts = $False
$Wkbk = $Excel.Workbooks.Add()
$Sheet = $Wkbk.Worksheets.Item(1)

$Sheet.Cells.Item(1,1) = "ID Admin"
$Sheet.Cells.Item(2,1) = "Outstanding Task Summary"
$Sheet.Cells.Item(3,1) = "Date: " + $Date
$Sheet.Cells.Item(7,2) = $disableuser_1days
$Sheet.Cells.Item(8,2) = $disableuser_2days
$Sheet.Cells.Item(9,2) = $disableuser_3days
$Sheet.Cells.Item(10,2) = $disableuser_4days
$Sheet.Cells.Item(11,2) = $disableuser_5days
$Sheet.Cells.Item(12,2) = $disableuser_gt5days

$Range = $Sheet.Range("A1:H1")
$Range.MergeCells = $true
$Range = $Sheet.Range("A2:H2")
$Range.MergeCells = $true
$Range = $Sheet.Range("A3:H3")
$Range.MergeCells = $true
$intRow = 7
	Do {
	$Sheet.Cells.Item($intRow,2).HorizontalAlignment = -4108
	$Sheet.Cells.Item($intRow,2).VerticalAlignment = -4160
	$intRow++
	} Until ($intRow -gt 12)
$WorkBook = $Sheet.UsedRange
$WorkBook.EntireColumn.AutoFit()

$Wkbk.SaveAs($SLAReportXLFile,1)
$Excel.Quit()

Open in new window

0
reason4xistence
Asked:
reason4xistence
1 Solution
 
Rory ArchibaldCommented:
I suspect:
$Sheet.Range("B7:B12").NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
0
 
reason4xistenceAuthor Commented:
Wow! I am going to look up NumberFormat. That doesn't look like normal Powershell regular expressions. At any rate, it works perfectly. Thanks so much, rorya!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now