Powershell expression that throws an error as as job, but executes otherwise

Question

I am new to Powershell scripting. I am trying to run a script to convert an Excel spreadsheet into a PDF file. This is the script I am using:

$excelInputPath = <Path1>;
$pdfOutputPath = <Path2>;
$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -As [type];
$objExcel = New-Object -ComObject excel.application;
$objExcel.visible = $False;
$workbook = $objExcel.workbooks.open($excelInputPath, 3);
$workbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $pdfOutputPath);
$objExcel.Workbooks.close();
$objExcel.Quit()

On its own, it executes perfectly. I now wish to add a timeout and so I am trying to run it as a job. However, this job throws an execution error which I catch via the Recieve-Job command:

$excelInputPath = <Path1>;
$pdfOutputPath = <Path2>;
$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -As [type];
$objExcel = New-Object -ComObject excel.application;
$objExcel.visible = $False;
$workbook = $objExcel.workbooks.open($excelInputPath, 3);
$job = Start-Job -ScriptBlock {$workbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $pdfOutputPath);}
$job | Wait-Job -Timeout 10;
If ($job.State -eq 'Running')
    {$job.StopJob();
    $objExcel.Workbooks.close();
    $objExcel.Quit()
    throw "Error encountered: Operation timed out"}
else
    {if($job.Childjobs[0].Error)
        {$objExcel.Workbooks.close();
        $objExcel.Quit()
        $job | Receive-Job}
    else
        {$job | Receive-Job;
        $objExcel.Workbooks.close();
        $objExcel.Quit()}
    }

The output I receive is quoted below. The message is in German and roughly translates to: "It is not possible to execute a method for an expression that has a NULL".

Id     Name            PSJobTypeName   State         HasMoreData     Location             Command
--     ----            -------------   -----         -----------     --------             -------
57     Job57           BackgroundJob   Completed     True            localhost          $workbook.ExportAsFixe...

Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
    + PSComputerName        : localhost

Another confusing thing here is that the Job -State is reflected as complete even when it doesn't perform the conversion and throws this error message.

Thank you! And apart from an explanation on why this is happening, I would appreciate any input on how I can perform this task better.

score 0 · Answer 1 · answered May 22 '20 at 13:10

0

The scriptblock has no knowledge of the variables $workbook, $xlFixedFormat::xlTypePDF and $pdfOutputPath. You need to send these as parameters using the -ArgumentList parameter.

Try:

$scriptBlock = {
    param($workbook, $format, $outPath) 
    $workbook.ExportAsFixedFormat($format, $outPath)
}
$job = Start-Job -ScriptBlock $scriptBlock -ArgumentList $workbook, $xlFixedFormat::xlTypePDF, $pdfOutputPath

answered May 22 '20 at 13:10

Theo

57,719
8
24
41

Thank you @Theo. That explains the error message I was getting. Unfortunately, I now receive another error message: Method invocation failed because [Deserialized.System.__ComObject#{000208da-0000-0000-c000-000000000046}] does not contain a method named 'ExportAsFixedFormat'. – Anupam May 22 '20 at 13:41
@Anupam I cannot test now, but could you try `param([ref]$workbook, ..` and call using `-ArgumentList [ref]$workbook, ..` ? – Theo May 22 '20 at 15:02
Thank you for the suggestion - totally makes sense. I tried it and got the following error however: Cannot process argument transformation on parameter 'workbook'. Reference type is expected in argument. I checked up on how to pass references to Start-job. A comment in the answer below talks about how that might not be possible. https://stackoverflow.com/questions/54089109/how-to-pass-variable-by-reference-to-powershell-job-or-runspace – Anupam May 22 '20 at 16:35

Powershell expression that throws an error as as job, but executes otherwise

1 Answers1