I've a problem when I'm assigning currency format to a specific range in a Excel sheet. The code in powershell I use to set the format is below :
$LC_ws_tab.range("B1 :B5").NumberFormat = "_(€* #.##0,00_);_(€* (#.##0,00);_(€* ""-""??_);_(@_)"
The thing i find strange is that - iff I run the code I see the screenshot below (euro sign = â,-) .
But when I run the code a second time via "run selected code" when excel document is still open there is a euro sign. Does anyone know why I've this result?
The complete sample code u see below:
function Excel_new {
$object = New-Object -ComObject "Excel.Application"
$object | Add-Member -MemberType ScriptMethod -Name FullQuit -Value {Excel_FullQuit}
$object
}
function Excel_FullQuit {
while ( $this.Workbooks.Count -gt 0 ) {
$this.Workbooks.Item(1).Close()
}
$this.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($this)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
Stop-Process -processname EXCEL
}
$Excel = Excel_new
$Excel.Visible = $true
$Excel.displayalerts = $false
$path = "C:\somefolder\test.xlsx"
$LC_wb = $Excel.Workbooks.Open($path)
$LC_ws_tab = $LC_wb.Worksheets.Add()
$LC_ws_tab.name = "test"
# $LC_ws_tab.range("A1 :A5").NumberFormat = "#.##0,00 € "
# $LC_ws_tab.range("B1 :B5").NumberFormat = "€ #.##0,00"
# $LC_ws_tab.range("C1 :C5").NumberFormat = "0,00 % "
$LC_ws_tab.Cells.Item( 1 , 1 ) = 49999
$LC_ws_tab.Cells.Item( 1 , 2 ) = 1234.879
$LC_ws_tab.Cells.Item( 1 , 3 ) = 1234.879
$LC_ws_tab.range("B1 :B5").NumberFormat = "_(€* #.##0,00_);_(€* (#.##0,00);_(€* ""-""??_);_(@_)"
