locked
How to resolve 'The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)' error? RRS feed

  • Question

  • 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?

    code:

    $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

    ----

    RelatedIfSolvedFIrst

    Thursday, April 30, 2020 6:39 PM

All replies

  • First you may want to ask the author of the scipt to help you understand why your solution won't work.

    The files can be open by other processes from other systems or by your AV software or by malware.  With your information there is really no way to advise you.

    Also note that your fix foes not make any technical sense which is why I recommended asking the author how to use the script.

    Note that you cannot kill the Excel process and have the script work after that.


    \_(ツ)_/

    Thursday, April 30, 2020 7:11 PM
  • First you may want to ask the author of the scipt to help you understand why your solution won't work.

    The files can be open by other processes from other systems or by your AV software or by malware.  With your information there is really no way to advise you.

    Also note that your fix foes not make any technical sense which is why I recommended asking the author how to use the script.

    Note that you cannot kill the Excel process and have the script work after that.


    \_(ツ)_/

    of course i incorporated some elements researching here and there, but generally speaking, i am the ultimate author of the script...

    i suspect it has to do with killing the excel process while script may be running...i could probably re-instantiate this line:

        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)

    but is that really the best approach?

    Thursday, April 30, 2020 8:16 PM
  • My point is that killing Excel will destroy your Excel object.  

    Also Excel will persist in memory so to many copies will trash future calls.

    Also the code needs to be debugged from the top.  

    Stop killing Excel.  Log off and back on and test again.  Report the complete error message if any.


    \_(ツ)_/

    Thursday, April 30, 2020 8:49 PM
  • My point is that killing Excel will destroy your Excel object.  

    Also Excel will persist in memory so to many copies will trash future calls.

    Also the code needs to be debugged from the top.  

    Stop killing Excel.  Log off and back on and test again.  Report the complete error message if any.


    \_(ツ)_/

    your're right. the right place to kill the process was supposed to be prior to instantiating the object, here:

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

    thank you :)

     
    • Marked as answer by cataster Thursday, April 30, 2020 9:07 PM
    • Unmarked as answer by jrv Thursday, April 30, 2020 11:45 PM
    Thursday, April 30, 2020 9:07 PM
  • My point is that killing Excel will destroy your Excel object.  

    Also Excel will persist in memory so to many copies will trash future calls.

    Also the code needs to be debugged from the top.  

    Stop killing Excel.  Log off and back on and test again.  Report the complete error message if any.


    \_(ツ)_/

    hey quick question

    Ive added some meaningful statements for logging purposes, such as

    try {
    	$ws.SaveAs($name, 6) #6 to ignore formatting and convert to pure text, otherwise, file could end up containing rubbish
    	
    	"`r`n'$($name)' generated successfully!"
    } 
    catch {
    	"`r`nFailed to save csv! Path: '$name'. $($error[0])`r`n$($error[0].InvocationInfo.PositionMessage)`r`n$($error[0].ScriptStackTrace)"
    }

    But as you can see in screenshot below, for one of the sheets converted to csv, its printing out many times!

    i suspect its because the ‘Org’ sheet has a hidden column, so this behavior could be explained because ‘COS’ sheet doesnt have a hidden column…

    but is there no way to make it print just once?

    Thursday, April 30, 2020 11:32 PM
  • Most of your original code is unnecessary and the5re is no need to kill anything.  

    If you would think about what you are doing and how PowerShell and Excel work none of this would be an issue.

    First take the time to learn the basics of PowerShell.  Your code shows that you are good at copying what you have seen but lack the basic understanding needed to use it well.


    \_(ツ)_/

    Thursday, April 30, 2020 11:44 PM
  • It is a very bad idea to blindly kill processes in any script or program.  It should never be done even if you sister-in-law swears it works when she bakes a pie.


    \_(ツ)_/

    Thursday, April 30, 2020 11:46 PM
  • It is a very bad idea to blindly kill processes in any script or program.  It should never be done even if you sister-in-law swears it works when she bakes a pie.


    \_(ツ)_/

    lol thats a good way to put it, but still, thats the only effective way, that i have trialed with, to eliminate the RPC exception i was getting earlier. 

    Of course i can always improve the structure of the code, but for now, im prioritizing certain things and so tahts not my top priority currently facing a deadline. 

    I just need to know why is that output behavior occurring (the two csv files are generated just fine in the target folder, just the display output is bothering me with that many printings for Org!). 

    Friday, May 1, 2020 12:38 AM