Using Powershell to convert text to number in Excel

Question

I have an excel file that has three columns that are set to Number. However, when I open the file I have the this :

I found a helpful link here: stackoverflow link

I have tried this method but I am getting the following error. Is there something I am doing wrong:

$wb = 'C:\Users\user1\Documents\Working Folder\239\239_uploadFile.xlsx'
$excel = new-object -ComObject excel.application
$excel.visible = $false
$excel.DisplayAlerts = $false
$wb = $excel.workbooks.open($wb)
$ws1 = $wb.worksheets.item(3)

$ws1.columns.item(1).numberformat = 0
$ws1.Columns.item(14).Value = $ws1.Columns.item(14).Value



$wb.Save()
$wb.close()
$excel.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
Remove-Variable excel`

[ Powershell error

I think they are under the misconception that `.Item(1)` indicates the first cell in the column. Either that or it is a typo and they just missed the `4`. — TheMadTechnician, Feb 17 '22 at 19:47
@TheMadTechnician Yes, please ignore.. I was thinking `Columns(1)`, not `Columns.Item(1)` — Theo, Feb 17 '22 at 19:48
@Theo Those are the same thing in this case. If you want to reference cells you need to do `$ws1.Columns.item(14).cells.item(1).numberformat = 0` or something like that, to reference cell N1 — TheMadTechnician, Feb 17 '22 at 19:53

mklement0 · Accepted Answer · 2022-02-19T14:13:55.727

In recent versions of Excel (as of at least Excel 2019), the .Value property is now a parameterized property and therefore requires an argument in order to be used.
- A simple workaround is to use the .Value2 property instead - shown below.
- However, .Value2 differs from .Value in that "it doesn’t use the Currency and Date data types" - if those types are required, use .Value(10) (sic) instead (10 is the value of the xlRangeValueDefault constant).
While using the .Item() method explicitly used to be required in earlier PowerShell (Core) versions, this no longer the case as of (at least) PowerShell (Core) 7.2.1.

Therefore, try the following:

& {
  $wb = 'C:\Users\user1\Documents\Working Folder\239\239_uploadFile.xlsx'
  $excel = new-object -ComObject excel.application

  $wb = $excel.workbooks.open($wb)
  $ws1 = $wb.worksheets(3)

  $ws1.columns(1).numberformat = 0
  # Note the use of .Value2, not .Value
  $ws1.Columns(14).Value2 = $ws1.Columns(14).Value2

  $wb.Save()
  $wb.close()
  $excel.Quit()
}

Note the use of & { ... }, i.e. the execution of the Excel-related code in a child scope. This makes the calls to and Remove-Variable excel unnecessary - see this answer for more information.

Using Powershell to convert text to number in Excel

1 Answers1

Linked