After stop process, im getting an RPC exception. how do i resolve it?

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
                        {
                            "<code>r</code>nHidden column [{0}] found in hidden [{1}] worksheet. Deleting..." -f $columnname, $($worksheet.name)
                        }
                        else {
                            "<code>r</code>nHidden column [{0}] found in [{1}] worksheet. Deleting..." -f $columnname, $($worksheet.name)
                        }

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

                            "<code>r</code>nHidden column [{0}] was Deleted! Proceeding with Export to CSV operation...<code>r</code>n" -f $columnname
                        }
                        catch {
                            Write-Error -Message "<code>r</code>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"))
                {
                    "<code>r</code>n'{0}' is currently in use.<code>r</code>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

                        "<code>r</code>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

Related

The issue is that you’re closing your own instance of excel. When you run

 $filename = [System.IO.Path]::GetFileNameWithoutExtension($Path)
$workbook = $excel.Workbooks.Open($Path)

This will open an excel process which you are forcefully closing along with the others. Change the logic to close excel first, then proceed with your script.

I see you added/adapted some of the techniques you’ve been shown. It’s looking good!

[quote quote=224346]I see you added/adapted some of the techniques you’ve been shown. It’s looking good!

[/quote]

[quote quote=224343]The issue is that you’re closing your own instance of excel. When you run

PowerShell
5 lines
<textarea class="ace_text-input" style="opacity: 0; height: 18px; width: 6.59781px; left: 44px; top: 0px;" spellcheck="false" wrap="off"></textarea>
1
2
3
4
5
$filename = [System.IO.Path]::GetFileNameWithoutExtension($Path)
$workbook = $excel.Workbooks.Open($Path)
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
This will open an excel process which you are forcefully closing along with the others. Change the logic to close excel first, then proceed with your script.

[/quote]

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 for the encouragement and assistance! :slight_smile:

Just pay it forward! Take care.

[quote quote=224361]Just pay it forward! Take care.

[/quote]
hey, just one more quick delimma im facing with logging output

Ive added some meaningful statements for logging purposes, such as

try {
$ws.SaveAs($name, 6)
rn’$($name)’ generated successfully!”
}

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?

Does COS have only one worksheet? Seems though, since you are in a foreach worksheet loop, it will print once foreach worksheet?

[quote quote=224427]Does COS have only one worksheet? Seems though, since you are in a foreach worksheet loop, it will print once foreach worksheet?

[/quote]
thats what i thought initially, but actually COS has more than 1 column: Total Countries,Region,Sub Region,Country,Country Code

so it must be because of the hidden aspect that Org is printing that many times…

These are the final columns for Org: Total Company,Region,Sub Region,Org,Org Code,Alt Org Desc

Not column, worksheet. The tabs across the bottom of excel for a different page.

[quote quote=224442]Not column, worksheet. The tabs across the bottom of excel for a different page.

[/quote]
its just one sheet. so the input to this script, i.e. get-child item part, is an excel file named something.xlsx. within that xlsx file, there are multiple sheets, amongst them is COS, and Org.

i do see two csv files generated in the actual destination folder, COS.csv, and Org.csv

but the output being like that, Org.csv printing multiple times, bothers me

Ok. Can you post your new code or gist to it?

[quote quote=224466]Ok. Can you post your new code or gist to it?

[/quote]
here you go:

$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 {
        # https://stackoverflow.com/a/32475836/8397835
        # https://stackoverflow.com/a/61532751/8397835

        "<code>r</code>nTerminating Excel process to prevent 'Can't access' (file in use) exception..."

        Get-Process 'exce[l]' | Stop-Process -Force #this is to avoid "Can't access" exception if file is opened or in use
        #Only 'excel' will be matched, but because a wildcard [] is used, not finding a match will not generate an error.

        $excel = New-Object -ComObject Excel.Application -Property @{
            Visible       = $false
            DisplayAlerts = $false
        }
    }
    Process {
        #$root = Split-Path -Path $Path
        $filename = [System.IO.Path]::GetFileNameWithoutExtension($Path)

        "<code>r</code>nGenerating CSV files from '$Path' Worksheet(s)..."

        $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
                        {
                            "<code>r</code>nHidden column [{0}] found in hidden [{1}] worksheet. Deleting..." -f $columnname, $($worksheet.name)
                        }
                        else {
                            "<code>r</code>nHidden column [{0}] found in [{1}] worksheet. Deleting..." -f $columnname, $($worksheet.name)
                        }

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

                            "<code>r</code>nHidden column [{0}] was Deleted! Proceeding with Export to CSV operation...<code>r</code>n" -f $columnname
                        }
                        catch {
                            "<code>r</code>nFailed to Delete hidden column [$columnname] from [$($worksheet.name)] worksheet! $($error[0])<code>r</code>n$($error[0].InvocationInfo.PositionMessage)<code>r</code>n$($error[0].ScriptStackTrace)"
                            #$_ | 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

                "<code>r</code>n'$name' generated successfully!"
            } 
            catch {
                "<code>r</code>nFailed to save csv! Path: '$name'. $($error[0])<code>r</code>n$($error[0].InvocationInfo.PositionMessage)<code>r</code>n$($error[0].ScriptStackTrace)"
            }
        }
    }
    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

Yeah, I’m not sure. I recommend you not run it as a function, running it step by step or debug it so you can inspect each object along the way to figure out what is populated and why. Good luck!

[quote quote=224490]Yeah, I’m not sure. I recommend you not run it as a function, running it step by step or debug it so you can inspect each object along the way to figure out what is populated and why. Good luck!

[/quote]
hey, on the topic of this, i have a question. as part of the export to csv, lets say i want to add a sequence column for the csv files being exported (basically appending a column at the beginning of each csv file as a number series i.e. 1,2,3,4…etc). How do i achieve this? it would help me extremely in case theres no way to do this so that i wouldnt have to add the column manually for each csv file getting generated