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
– TheMadTechnician, Commented Feb 17, 2022 at 19:47
@TheMadTechnician Yes, please ignore.. I was thinking Columns(1), not Columns.Item(1) — Theo
– Theo, Commented Feb 17, 2022 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
– TheMadTechnician, Commented Feb 17, 2022 at 19:53

mklement0 · Accepted Answer · 2022-02-19 14:13:55Z

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.

Collectives™ on Stack Overflow

Using Powershell to convert text to number in Excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related