Why am I getting 'The remote procedure call failed' error after stopping excel process?

0

I have the following code that converts an excel sheets to csv files. If the csv files do not exist/or exist already but not in use (e.g. opened in excel), the script generates the csv files successfully (overwriting them if they exist already)!

However, if the csv file is opened in excel, then i get an error "Can't access csv file" which i have determined is because its in use by excel (when opened). I know this is 100% the reason because if i have the existing csv file opened in notepad, the script still overwrites the csv file, running successfully.

so i tried implementing an automatic resolution, which is Get-Process 'exce[l]' | Stop-Process -Force , and although it does stop the process (closes excel), I get yet another error:

Convert-ExcelSheetsToCsv : Failed to save csv! Path: 'C:\Users\Documents\Folder1\CSV_Files\COS.csv'. The remote
procedure call failed. (Exception from HRESULT: 0x800706BE)

Convert-ExcelSheetsToCsv : Failed to save csv! Path: 'C:\Users\Documents\Folder1\CSV_Files\.csv'. The RPC server is
unavailable. (Exception from HRESULT: 0x800706BA)

After some research, I disabled my COM-Excel Addins, ran the script again, and the exceptions still occurred again...

com

why is that?

$currentDir = $PSScriptRoot

$csvPATH = Join-Path -Path $currentDir -ChildPath CSV_Files
New-Item -ItemType Directory -Force -Path $csvPATH | out-null

function Convert-ExcelSheetsToCsv {
    param(
        [Parameter(Mandatory, ValueFromPipelineByPropertyName, Position=1)]
        [ValidateNotNullOrEmpty()]
        [Alias('FullName')]
        [string]$Path,
        [Parameter(Mandatory = $false, Position=0)]
        [bool]$AppendFileName,
        [Parameter(Mandatory = $false, Position=2)]
        [bool]$ExcludeHiddenSheets,
        [Parameter(Mandatory = $false, Position=3)]
        [bool]$ExcludeHiddenColumns
    )
    Begin {
        $excel = New-Object -ComObject Excel.Application -Property @{
            Visible       = $false
            DisplayAlerts = $false
        }
    }
    Process {
        #$root = Split-Path -Path $Path
        $filename = [System.IO.Path]::GetFileNameWithoutExtension($Path)
        $workbook = $excel.Workbooks.Open($Path)

        foreach ($worksheet in ($workbook.Worksheets | Where { <# $_.Visible -eq -1 #> $_.Name -ne 'Security' -and $_.Name -ne 'Notes' })) {        
            if($ExcludeHiddenColumns) {
                $ColumnsCount = $worksheet.UsedRange.Columns.Count
                for ($i=1; $i -le $ColumnsCount; $i++)
                {
                    $column = $worksheet.Columns.Item($i).EntireColumn #$worksheet.sheets.columns.entirecolumn.hidden=$true
                    if ($column.hidden -eq $true)
                    {   
                        $columnname = $column.cells.item(1,$i).value2

                        if ($worksheet.Visible -eq 0) #worksheet hidden
                        {
                            "`r`nHidden column [{0}] found in hidden [{1}] worksheet. Deleting..." -f $columnname, $($worksheet.name)
                        }
                        else {
                            "`r`nHidden column [{0}] found in [{1}] worksheet. Deleting..." -f $columnname, $($worksheet.name)
                        }

                        try {
                            $column.Delete() | out-null

                            "`r`nHidden column [{0}] was Deleted! Proceeding with Export to CSV operation...`r`n" -f $columnname
                        }
                        catch {
                            Write-Error -Message "`r`nFailed to Delete hidden column [$columnname] from [$($worksheet.name)] worksheet! $PSItem"
                            #$_ | Select *
                        }

                        #$i = $i - 1
                    }
                }
            }

            if ($ExcludeHiddenSheets) {
                if ($worksheet.Visible -eq -1) #worksheet visible
                {
                    $ws = $worksheet
                }
            }
            else {
                $ws = $worksheet
            }

            if ($AppendFileName) {
                $name = Join-Path -Path $csvPATH <# $root #> -ChildPath "${filename}_$($ws.Name).csv"
            }
            else {
                $name = Join-Path -Path $csvPATH <# $root #> -ChildPath "$($ws.Name).csv"
            }

            try {
                $ws.SaveAs($name, 6) #6 to ignore formatting and convert to pure text, otherwise, file could end up containing rubbish
            } 
            catch {
                if ($error[0].ToString().Contains("Cannot access"))
                {
                    "`r`n'{0}' is currently in use.`r`n Attempting to override usage by trying to stop Excel process..." -f $name

                    try {
                        #Only 'excel' will be matched, but because a wildcard [] is used, not finding a match will not generate an error.
                        #https://stackoverflow.com/a/32475836/8397835

                        Get-Process 'exce[l]' | Stop-Process -Force

                        "`r`nExcel process stopped! Saving '{0}' ..." -f $name

                        $ws.SaveAs($name, 6)
                    }
                    catch {
                        Write-Error -Message "Failed to save csv! Path: '$name'. $PSItem"
                    }
                }
                else {
                    Write-Error -Message "Failed to save csv! Path: '$name'. $PSItem"
                }
            }
        }
    }
    End {
        $excel.Quit()
        $null = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
    }
}

Get-ChildItem -Path $currentDir -Filter *.xlsx | Convert-ExcelSheetsToCsv -AppendFileName 0 -ExcludeHiddenSheets 1 -ExcludeHiddenColumns 1 #0 for false, so that filename of excel file isnt appended, and only sheet names are the names of the csv files
excel
csv
powershell-5.0
asked on Stack Overflow Apr 30, 2020 by Cataster

1 Answer

0

That is because the excel object ends up getting destroyed as well. the correct way to do this is to end the process PRIOR to instantiating the excel object:

Begin {
    Get-Process 'exce[l]' | Stop-Process -Force
answered on Stack Overflow Apr 30, 2020 by Cataster

User contributions licensed under CC BY-SA 3.0