Convert .xls to .xlsb Running PowerShell Script from Python, ERROR: "You cannot call a method on a null-valued expression"

Question

I am attempting to run PowerShell script from python to convert .xls files to .xlsb. by looping through a list of file names. I am encountering a PowerShell error "You cannot call a method on a null-valued expression" for command 3 (i.e. cmd3), and I am unsure why (this is my first time with python and running PowerShell script in general). The error is encountered when trying to open the workbook, but when the command is run in PowerShell directly, it seems to work fine.

Code:

import logging, os, shutil, itertools, time, pyxlsb, subprocess

# convert .xls to .xlsb and / transfer new terminology files
for i in itertools.islice(FileList, 0, 6, None):

    # define extension
    ext = '.xls'

    # define file path
    psPath = f'{downdir}' + f'\{i}'

    # define ps scripts
    def run(cmd):
        completed = subprocess.run(["powershell", "-Command", cmd], capture_output=True)
        return completed
    
    # ps script: open workbook
    cmd1 = "$xlExcel12 = 50"
    cmd2 = "$Excel = New-Object -Com Excel.Application"
    cmd3 = f"$WorkBook = $Excel.Workbooks.Open('{psPath}{ext}')"
    cmd4 = f"$WorkBook.SaveAs('{psPath}{ext}',$xlExcel12,[Type]::Missing, 
           [Type]::Missing,$false,$false,2)"
    cmd5 = "$Excel.Quit()"
    
    # ps script: delete.xls files
    cmd6 = f"Remove-Item '{psPath}{ext}'"
    

    run(cmd1)
    run(cmd2)
    run(cmd3)

    # change extension
    ext = '.xlsb'

    run(cmd4)
    run(cmd5)
    run(cmd6)

    # copy .xlsb files to terminology folder
    shutil.copy(i + ext, termdir)

Error:

Out[79]: CompletedProcess(args=['powershell', '-Command', "$WorkBook = > > $Excel.Workbooks.Open('C:\Users\Username\Downloads\SEND Terminology.xls')"], returncode=1, stdout=b'', stderr=b"You cannot call a method on a null-valued expression.\r\nAt line:1 char:1\r\n+ $WorkBook = $Excel.Workbooks.Open('C:\Username\User\Downloads\SEND Ter ...\r\n+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\r\n + CategoryInfo : InvalidOperation: (:) [], RuntimeException\r\n + FullyQualifiedErrorId : InvokeMethodOnNull\r\n \r\n")

Any input would be helpful.

Thank you!

The cmd1..n are being run in separate subprocesses, so the Excel COM object created in cmd2 doesn't exist in cmd3's process space. — vonPryz, Dec 05 '20 at 07:22

score 0 · Accepted Answer · answered Dec 05 '20 at 09:34

Problem

As commenter vonPryz correctly stated, the Powershell commands run in separate processes. The memory spaces of processes are isolated from each other, and will be cleared when a process ends.

When you run the commands in separate Powershell processes, the cmd3, cmd4 and cmd5 won't have the variable $Excel available. Powershell defaults to a $null value for non-existing variables, hence the error message "You cannot call a method on a null-valued expression". The same happens for variable $xlExcel12. These variables only exists as long as the processes that created them were running and would only be visible within these processes, even if you managed to create two processes in parallel.

Solution

Commands cmd1..5 need to be run in the same Powershell process, so each command will be able to "see" the variables created by previous commands:

run( cmd1 + ';' + cmd2 + ';' + cmd3 + ';' + cmd4 + ';' + cmd5 )

You will need to change cmd4 to use another variable for the extension that will be used for saving, e. g. extSave

cmd4 = f"$WorkBook.SaveAs('{psPath}{extSave}',$xlExcel12,[Type]::Missing, 
       [Type]::Missing,$false,$false,2)"

The cmd6 is completely independent, because it does not depend on Powershell variables. It only depends on python variables, which are resolved before the process starts, so it could still be run in a separate process.

Convert .xls to .xlsb Running PowerShell Script from Python, ERROR: "You cannot call a method on a null-valued expression"

1 Answers1

Problem

Solution