Solved

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

Posted on 2010-09-23
2
2,718 Views
Last Modified: 2012-05-10
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
Comment
Question by:reason4xistence
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 33744350
I suspect:
$Sheet.Range("B7:B12").NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
0
 

Author Closing Comment

by:reason4xistence
ID: 33744823
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A procedure for exporting installed hotfix details of remote computers using powershell
A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question