0

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?

8
  • 1
    Change Write-Host to Write-Output and check if $i is null. Commented Aug 20, 2020 at 10:22
  • Does it work without the -Parallel? Commented Aug 20, 2020 at 10:39
  • $Sheet.Cells is $null Commented Aug 20, 2020 at 10:57
  • If $Sheet.Cells is $null, error should be thrown here itself rite? $Sheet.Cells.Item(1,3) = "Substraction" . But, it is not happening. Commented Aug 20, 2020 at 11:01
  • No, the only method you are calling on that line is .Item(); so $Sheet.Cells is null. What does $Sheet.Cells($i,3) return? Commented Aug 20, 2020 at 12:24

1 Answer 1

1

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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.