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

Welcome Forums General PowerShell Q&A After stop process, im getting an RPC exception. how do i resolve it?

Viewing 13 reply threads
  • Author
    Posts
    • #224277
      Participant
      Topics: 39
      Replies: 108
      Points: 620
      Rank: Major Contributor

      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
                              {
                                  "rnHidden column [{0}] found in hidden [{1}] worksheet. Deleting..." -f $columnname, $($worksheet.name)
                              }
                              else {
                                  "rnHidden column [{0}] found in [{1}] worksheet. Deleting..." -f $columnname, $($worksheet.name)
                              }
      
                              try {
                                  $column.Delete() | out-null
      
                                  "rnHidden column [{0}] was Deleted! Proceeding with Export to CSV operation...rn" -f $columnname
                              }
                              catch {
                                  Write-Error -Message "rnFailed 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"))
                      {
                          "rn'{0}' is currently in use.rn 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
      
                              "rnExcel 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

      • This topic was modified 3 weeks, 5 days ago by cataster16.
    • #224343
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

      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.

    • #224346
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

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

    • #224349
      Participant
      Topics: 39
      Replies: 108
      Points: 620
      Rank: Major Contributor

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

      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.

      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! 🙂

      • This reply was modified 3 weeks, 5 days ago by cataster16.
    • #224361
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

      Just pay it forward! Take care.

    • #224418
      Participant
      Topics: 39
      Replies: 108
      Points: 620
      Rank: Major Contributor

      Just pay it forward! Take care.

      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?

    • #224427
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

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

    • #224433
      Participant
      Topics: 39
      Replies: 108
      Points: 620
      Rank: Major Contributor

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

      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

    • #224442
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

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

    • #224451
      Participant
      Topics: 39
      Replies: 108
      Points: 620
      Rank: Major Contributor

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

      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

      • This reply was modified 3 weeks, 4 days ago by cataster16.
      • This reply was modified 3 weeks, 4 days ago by cataster16.
      • This reply was modified 3 weeks, 4 days ago by cataster16.
    • #224466
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

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

    • #224469
      Participant
      Topics: 39
      Replies: 108
      Points: 620
      Rank: Major Contributor

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

      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
      
              "rnTerminating 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)
      
              "rnGenerating 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
                              {
                                  "rnHidden column [{0}] found in hidden [{1}] worksheet. Deleting..." -f $columnname, $($worksheet.name)
                              }
                              else {
                                  "rnHidden column [{0}] found in [{1}] worksheet. Deleting..." -f $columnname, $($worksheet.name)
                              }
      
                              try {
                                  $column.Delete() | out-null
      
                                  "rnHidden column [{0}] was Deleted! Proceeding with Export to CSV operation...rn" -f $columnname
                              }
                              catch {
                                  "rnFailed to Delete hidden column [$columnname] from [$($worksheet.name)] worksheet! $($error[0])rn$($error[0].InvocationInfo.PositionMessage)rn$($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
      
                      "rn'$name' generated successfully!"
                  } 
                  catch {
                      "rnFailed to save csv! Path: '$name'. $($error[0])rn$($error[0].InvocationInfo.PositionMessage)rn$($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
    • #224490
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

      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!

    • #227857
      Participant
      Topics: 39
      Replies: 108
      Points: 620
      Rank: Major Contributor

      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!

      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

Viewing 13 reply threads
  • You must be logged in to reply to this topic.