Modifying excel data in powershell help

PowerShell Help
1

Hi, I am creating a PowerShell script to append data to a certain range of cells in an .xls worksheet. The existing cell data are numbers IE:1234 and I need to append /60 to the end of each which converts seconds to minutes. This is what I have so far and I cannot seem to get passed this. Ideas?

$excel_file_path = 'C:\test.xls'
$Excel = New-Object -ComObject Excel.Application
$ExcelWorkBook = $Excel.Workbooks.Open($excel_file_path)
$ExcelWorkSheet = $Excel.WorkSheets.item("sheet1")
$ExcelWorkSheet.activate()
$DataType = '/60'
foreach ($Cell in $ExcelWorkSheet.Range('G2:G4').Cells) {
 $Cell.Value2 += ($DataType)
}
$ExcelWorkBook.Save()
$ExcelWorkBook.Close()
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
Stop-Process -Name EXCEL -Force

Here is the error I get:
Cannot convert value “/60” to type “System.Double”. Error: “Input string was not in a correct format.”
At line:8 char:2

  • $Cell.Value2 += ($DataType)
  •  + CategoryInfo          : InvalidArgument: (:) [], RuntimeException
 + FullyQualifiedErrorId : InvalidCastFromStringToDoubleOrSingle

Thanks!

2

Without having the excel sheet I can’t be sure but I would guess it’s a datatype issue.
As the error states it can’t convert the string ‘/60’ to a double value.

The first thing is to check the datatype of $Cell.Value2
Once you know that you can the decide what you want to do.

3

Thanks a bunch. In Line 8, instead of using $Cell.Value2 I used $Cell.Formula += $DataType which worked like a charm.