Modifying excel data in powershell help

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Dominic Dominic 2 days ago.

  • Author
    Posts
  • #71101
    Profile photo of Dominic
    Dominic
    Participant

    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!

  • #71102
    Profile photo of Fredrik Kacsmarck
    Fredrik Kacsmarck
    Participant

    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
      Profile photo of Dominic
      Dominic
      Participant

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

You must be logged in to reply to this topic.