The remote procedure call failed, cannot call a method on a null-valued expression - works on restart

1

I have a PowerShell script that also calls on a VBA script in my Access database. When I try to run the PowerShell script, it does not work and invokes the following errors:

The remote procedure call failed. (Exception from HRESULT: 0x800706BE)
At Z:\Report\Run\RUN.ps1:18 char:1
+ $wb_con1 = $excel.Workbooks.Open($FilePath_con1)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

You cannot call a method on a null-valued expression.
At Z:\Report\Run\RUN.ps1.ps1:19 char:1
+ $ws_con1 = $wb_con1.sheets.Item(1)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

There are more errors that occur below all saying You cannot call a method on a null-valued expression.

When I restart my computer, the script works fine. The drive is a network shared drive. It seems like a file is open somewhere even though I make sure Excel and Access are not running at all. I am not entirely sure. My PowerShell script is below and I am happy to provide my Access VBA script as well, but I believe the issue is with the PowerShell script since the error happens in the initial steps with Excel.

My code is below. Any guidance or assistance would be greatly appreciated.

# start Excel
$excel = New-Object -comobject Excel.Application
$ms_access = New-Object -comobject Access.Application

#set path files
$FilePath_con1 = 'Z:\Report\Data\StatusReport_C.xls'
$FilePath_eval1 = 'Z:\Report\Data\StatusReport_E.xls'

$FilePath_con2 = 'Z:\Report\Data\StatusReport_E.xlsx'

$FilePath_eval2 = 'Z:\Report\Data\StatusReport_E.xlsx'

$FilePath_date = 'Z:\Report\Data\ReportDate.xlsx'

$FilePath_access = "Z:\Report\Data\Access\Processing.accdb"

#Open workbook where the data will be copied from
$wb_con1 = $excel.Workbooks.Open($FilePath_con1)
$ws_con1 = $wb_con1.sheets.Item(1)

#Open workbook where the data will be copied to
$wb_con2 = $excel.Workbooks.Open($FilePath_con2)
$ws_con2 = $wb_con2.sheets.Item(1)

#Clear the workbook that the data will be copied to
$ws_con2.Cells.Clear()

#make Excel visible ($true) or invisible ($false)
$excel.Visible = $false

#Find the row count and column count of the data
$lrow1 = $ws_con1.usedRange.Rows.Count
$lcol1 = $ws_con1.usedRange.Columns.Count

#Copy from the header row which is located on Row 3
$range1=$ws_con1.Range("A3:V$lrow1")
$range1.copy()

#Copy the Report Date
$cpy_range_con = $ws_con2.Range("A1")
$ws_con2.Paste($cpy_range_con)

#Save the workbook where the data was copied to and close it
$wb_con2.Save()
$wb_con2.Close()
$wb_con1.Close()

#Open workbook where the data will be copied from 
$wb_eval1 = $excel.Workbooks.Open($FilePath_eval1)
$ws_eval1 = $wb_eval1.sheets.Item(1)

#Open workbook where the data will be copied to
$wb_eval2 = $excel.Workbooks.Open($FilePath_eval2)
$ws_eval2 = $wb_eval2.sheets.Item(1)

#Open workbook where the data will be copied from 
$wb_date = $excel.Workbooks.Open($FilePath_date)
$ws_date = $wb_date.sheets.Item(1)

#Clear the workbook where the data will be copied to
$ws_eval2.Cells.Clear()

#Count the rows where the data will be copied from in E
$lrow2 = $ws_eval1.usedRange.Rows.Count

#Define the range as the cell starting in A3 until the last row
$range2=$ws_eval1.Range("A3:V$lrow2")

#Copy the data range
$range2.copy()

#Define the cell where the data will start in the workbook where the data will be copied to
$cpy_range_eval = $ws_eval2.Range("A1")

#Paste the data 
$ws_eval2.Paste($cpy_range_eval)

#Define the cell for the ReportDate and copy it
$date_range = $ws_eval1.Range("B1")
$date_range.copy()

#Define the cell where the Report date will be copied to and paste it
$cpy_range_date = $ws_date.Range("A2")
$ws_date.Paste($cpy_range_date)

#Save and close the workbooks
$wb_date.Save()
$wb_date.Close()

$wb_eval2.Save()
$wb_eval2.Close()
$wb_eval1.Close()

#$excel.quit() | Out-Null

$ms_access.OpenCurrentDatabase($Filepath_access)
$ms_access.Run("ExportExcel")
#$ms_access.Quit() | Out-Null

$wshell = New-Object -ComObject Wscript.Shell
$output = $wshell.Popup("The task has finished")

Add-Type -AssemblyName System.Windows.Forms
$global:balloon = New-Object System.Windows.Forms.NotifyIcon
$path = (Get-Process -id $pid).Path
$balloon.Icon = [System.Drawing.Icon]::ExtractAssociatedIcon($path)
$balloon.BalloonTipIcon = [System.Windows.Forms.ToolTipIcon]::Info 
$balloon.BalloonTipText = 'The reports are completed.'
$balloon.BalloonTipTitle = "Attention $Env:USERNAME" 
$balloon.Visible = $true 
$balloon.ShowBalloonTip(20000)

vba
powershell
ms-access
asked on Stack Overflow May 2, 2019 by MonkeyMonkey • edited Jul 15, 2020 by braX

1 Answer

2

You have commented out the Quit() actions on both Excel and Access.. That way you will leave these applications running and every time you run the script, new Com objects are created and never cleaned up from memory.

You need to quit and remove them from memory using

$excel.Quit()
$ms_access.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb_con1)   | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb_con2)   | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb_eval1)  | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb_eval2)  | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb_date)   | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)     | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ms_access) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
$excel = $ms_access = $null

when done with these objects.


I guess you need to check all file paths to see if the files are all present:

if (!(Test-Path -Path $FilePath_con1 -PathType Leaf)) {
    Write-Error "The file $FilePath_con1 could not be found"
    exit
}

Do that for all files you are trying to open.

However.. Checking if the files are actually present is one thing, but it still does not mean you can open them using $excel.Workbooks.Open($FilePath_con1) because maybe another Excel process has the file open already. Another reason could be that you don't have access to the file permission wise.

Maybe a utility function can help you with that. It tests if the file can be found and if so, if there is a lock on it already or not.

function Test-IsFileAvailable {
    [CmdletBinding()]
    param (
        [parameter(Mandatory = $true, ValueFromPipeline = $true, Position = 0)]
        [string]$Path
    )

    if (!(Test-Path -Path $Path -PathType Leaf)) {
        # the file is not found
        Write-Verbose "File '$Path' not found."
        return $false
    }

    try {
        $file   = New-Object System.IO.FileInfo $Path
        $stream = $file.Open([System.IO.FileMode]::Open, 
                             [System.IO.FileAccess]::ReadWrite, 
                             [System.IO.FileShare]::None)

        if ($stream) { $stream.Close() }
        Write-Verbose "File '$Path' is not locked."
        return $true
    } 
    catch {
        # file is locked by a process.
        Write-Verbose "File '$Path' is locked by another process."
        return $false
    }
}

Use it like this:

if (!(Test-IsFileAvailable $FilePath_con1 -Verbose)) { exit }

P.S. I see now that you also create a Com object to show a messagebox.
Perhaps an easier way to do that is like this:

Add-Type -AssemblyName Microsoft.VisualBasic

$Buttons = "OKOnly"    # other options are "OKCancel", "AbortRetryIgnore", "YesNoCancel", "YesNo", "RetryCancel"
$Icon = "Information"  # other options are "Critical", "Question", "Exclamation"
# show the messagebox
[Microsoft.VisualBasic.Interaction]::MsgBox("The task has finished", "$Buttons,SystemModal,$Icon", $Title)

Hope that helps

answered on Stack Overflow May 2, 2019 by Theo • edited May 2, 2019 by Theo

User contributions licensed under CC BY-SA 3.0