system._comobject doesnt not have a method named Cells in powershell while updating excel

Question

I have been trying this below code snippet to update a certain cell value in an excel file with 'xls' extension. This code snippet worked on my local system but its failing on the server giving the error as: Method invocation failed system._comobject doesnt not have a method named Cells

function updateAprroval
{
    [CmdletBinding()] 
    Param 
    ( 
        [Parameter(Mandatory=$true, 
                   ValueFromPipelineByPropertyName=$true)] 
        [ValidateNotNullOrEmpty()] 
        [Alias("change")] 
        [string]$changeNo, 
 
        [Parameter(Mandatory=$true)]
        [Validateset('Email1','Email2','Email3')] 
        [Alias('email')] 
        [string]$mail, 
         
        [Parameter(Mandatory=$true)] 
        [ValidateNotNullOrEmpty()] 
        [Alias('state')] 
        [string]$status,

        [Parameter(Mandatory=$false)] 
        [Alias('start')] 
        [string]$sDate,

        [Parameter(Mandatory=$false)]  
        [Alias('end')] 
        [string]$eDate
        
               
    ) 

    $searchFor            = $changeNo

    $excel                = New-Object -ComObject Excel.Application
    $excel.Visible        = $false
    $excel.ScreenUpdating = $false
    $excel.DisplayAlerts = $false

    $workbook  = $excel.Workbooks.Open( $approvalFile ,$null, $false )

    $ws        = $workbook.WorkSheets.item(1) 

    

    #[void]$ws.Activate()

    $searchRange  = $ws.UsedRange

    $searchResult = $searchRange.Find( $searchFor, [System.Type]::Missing, [System.Type]::Missing, 
                                                   [Microsoft.Office.Interop.Excel.XlLookAt]::xlWhole, 
                                                   [Microsoft.Office.Interop.Excel.XlSearchOrder]::xlByColumns, 
                                                   [Microsoft.Office.Interop.Excel.XlSearchDirection]::xlNext )


    if($mail -eq 'Email1')
    {
        $column = 3
    }
    elseif($mail -eq 'Email2')
    {
        $column = 4
    }
    else
    {
        $column = 5
    }
    if($searchResult) 
    {

        $row = $searchResult.Row
        $col = $searchResult.Column

        $ws.Cells( $row, $column ).Value = $status
        if($sDate -and $eDate)
        {
            
            
            $ws.Cells( $row, 6 ).Value = ([datetime]$sDate)
            $ws.Cells( $row, 7 ).Value = ([datetime]$eDate)
        }
        #$searchResult = $searchRange.FindNext( $searchResult )

        #if( $searchResult -and $searchResult.Row -le $row ) {
            #break
        #}
    }

    $workbook.Save()
    $workbook.Close()
    $excel.Quit()
    #[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

}

#updateAprroval -changeNo 'CHG0158032' -mail Email1 -status 'new dates' -start '2021-02-12 15:30:00' -end '2021-04-12 15:30:00'

In my local desktop, the powershell version is 5, whereas on server it is version 4. The excel version in my desktop is 2016 and on server it is 2013

Please help me resolve this issue.

Answer 1

The code worked for me after replacing this line in the code:

$ws.Cells( $row, $column ).Value = "assign something"

with

$ws.item( $row, $column ) = "assign something"

May be its because of the difference in versions.