CODEWITHSUNDEEP
powershellpowershell-7.0
subha
subha

Reputation: 133

Error while executing in PowerShell7.0: You cannot call a method on a null-valued expression

I tried the sample program to execute an Excel subtraction operation in parallel using the ForEach-Object -Parallel option in Powershell 7 version.

Param(
   [Parameter(Position=1)]
   [string]$input_filename,
  
   [Parameter(Position=2)]
   [string]$output_filename
)

# Instantiate Excel instnace
$excel_test = New-Object -ComObject Excel.Application

# Make the instance visiable to work with it
$excel_test.visible = $False

# Catch alerts
$excel_test.DisplayAlerts = 'False'

# Add in the file source
$workbook = $excel_test.Workbooks.Add($input_filename)

# Choose a sheet in the workbook
$Sheet = $excel_test.Worksheets.Item(1)

$totalNoOfRecords = ($Sheet.UsedRange.Rows).count

# Assign the formula to the target variable
$Sheet.Cells.Item(1,3) = "Substraction"

Measure-Command {

    2..$totalNoOfRecords | ForEach-Object -Parallel {

        # Assign a formula to the target variable
        $i = $_
        $strFormula_1 =  "=(A$($i)- B$($i))"
        Write-Host $strFormula_1
        Write-Host $i
        $Sheet.Cells.Item($i,3) = "=(A$($i) - B$($i))"
    } -ThrottleLimit 3
}

# Exit the XLS
$workbook.SaveAs($output_filename)  
$workbook.close($false)
$excel_test.Quit()

But, it throws an error as following,

InvalidOperation:
Line |
   8 |          $Sheet.Cells.Item($i,3) = "=(A$($i) - B$($i))"
     |                                                   ~~
     | You cannot call a method on a null-valued expression.

How to fix the error here?

Upvotes: 0

Views: 326

Answers (1)

js2010
js2010

Reputation: 27423

Put $using:sheet inside the loop, like with jobs and invoke-command.

Upvotes: 1

Related Questions