Modifying excel data in powershell help

Welcome Forums General PowerShell Q&A Modifying excel data in powershell help

This topic contains 2 replies, has 2 voices, and was last updated by

1 year, 9 months ago.

  • Author
  • #71101

    Points: 0
    Rank: Member

    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")
    $DataType = '/60'
    foreach ($Cell in $ExcelWorkSheet.Range('G2:G4').Cells) {
     $Cell.Value2 += ($DataType)
    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


  • #71102

    Points: 305
    Helping Hand
    Rank: Contributor

    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.

    • #71215

      Points: 0
      Rank: Member

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

The topic ‘Modifying excel data in powershell help’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort