none
Excel crashes with an RPC Server is unavailable. RRS feed

  • Question

  • Good AfterNoon Scripting Guys and Gals,

    I have a problem where I am creating 218 sheets in excel and it crashes around creating sheet128. and I get RPC server is unavailable. So it would be ideal if I could create more sheets with out it crashing or if there is a way to reopen the same sheet when it crashes and finish creating the sheets.  option 2 would be if yall could educate me on how to split the array in half or at a particular line #. below is my code any help yall can offer I greatly appreciate it. 

    $greaterthan30 = gci |select-object -expandProperty name | Where-Object { $_.length -gt 30 }
    $greaterthan30 = $greaterthan30.substring(0, 30)
    $lessthan30 = gci |select-object -expandProperty name | Where-Object { $_.length -lt 30 }
    $justright = $greaterthan30 + $lessthan30
    [array]::Reverse($justright)

    $excel = new-object -comobject Excel.Application
    $excel.visible = $true
    $WorkBook = $Excel.Workbooks.Add()
    $justright | ForEach-Object {
     $newsheet = $workbook.worksheets.add()
     $newsheet.name = $_
     }

    Monday, June 11, 2018 7:01 PM

Answers

  • The number of worksheets is only limited by the amount of installed memory.  It is likely you are running out of memory.  It is also possible that your Excel needs to be repaired.

    I can create thousands of blank worksheets with no issue and it is extremely fast.

    Memory will be a bigger problem, on 32 bit Windows and on old versions of Windows.


    \_(ツ)_/


    • Edited by jrv Monday, June 11, 2018 8:58 PM
    • Marked as answer by cmoneyg Monday, June 11, 2018 9:49 PM
    Monday, June 11, 2018 8:58 PM
  • I would recommend not (ab)using Excel in this way and use an intermediate, faster, and simpler format such as CSV.

    -- Bill Stewart [Bill_Stewart]

    • Marked as answer by cmoneyg Monday, June 11, 2018 9:49 PM
    Monday, June 11, 2018 8:13 PM
    Moderator

All replies

  • I have no issues with creating 218 or more sheets in Excel.

    Try just this:

    $excel = new-object -comobject Excel.Application
    $excel.visible = $true
    $WorkBook = $Excel.Workbooks.Add()
    1..300 | ForEach-Object {
        $newsheet = $workbook.worksheets.add()
        $newsheet.name = "text$_"
    } 


    \_(ツ)_/

    Monday, June 11, 2018 7:52 PM
  • I would recommend not (ab)using Excel in this way and use an intermediate, faster, and simpler format such as CSV.

    -- Bill Stewart [Bill_Stewart]

    • Marked as answer by cmoneyg Monday, June 11, 2018 9:49 PM
    Monday, June 11, 2018 8:13 PM
    Moderator
  • I tried it and it still crashed. Right at 126 this time. below is the out put. displayed for each line after 126


    PS C:\> $excel = new-object -comobject Excel.Application
    PS C:\> $excel.visible = $true
    PS C:\> $WorkBook = $Excel.Workbooks.Add()
    PS C:\> 1..300 | ForEach-Object {
    >>     $newsheet = $workbook.worksheets.add()
    >>     $newsheet.name = "text$_"
    >> }
    The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
    At line:3 char:5
    +     $newsheet.name = "text$_"
    +     ~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : OperationStopped: (:) [], COMException
        + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

    You cannot call a method on a null-valued expression.
    At line:2 char:5
    +     $newsheet = $workbook.worksheets.add()
    +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : InvokeMethodOnNull

    Monday, June 11, 2018 8:33 PM
  • I refer the honorable gentleman to my previous response.

    -- Bill Stewart [Bill_Stewart]

    • Marked as answer by cmoneyg Monday, June 11, 2018 9:49 PM
    • Unmarked as answer by cmoneyg Monday, June 11, 2018 9:49 PM
    Monday, June 11, 2018 8:53 PM
    Moderator
  • The number of worksheets is only limited by the amount of installed memory.  It is likely you are running out of memory.  It is also possible that your Excel needs to be repaired.

    I can create thousands of blank worksheets with no issue and it is extremely fast.

    Memory will be a bigger problem, on 32 bit Windows and on old versions of Windows.


    \_(ツ)_/


    • Edited by jrv Monday, June 11, 2018 8:58 PM
    • Marked as answer by cmoneyg Monday, June 11, 2018 9:49 PM
    Monday, June 11, 2018 8:58 PM
  • I have uninstalled office and reinstalled it works like a champ now. thanks jrv. Also Bill I am not oppose to using csv. Will you show me how you would code it. Also guys I really am curious as to how to split an array like this in to two arrays, if y'all can show examples using the code I have as context I would appreciate it.  
    Monday, June 11, 2018 9:48 PM
  • There is no array split method.

    You can use the indexer:

    $array[0..([int]$array.Count/2))


    \_(ツ)_/

    Monday, June 11, 2018 9:52 PM