Comを利用したExcel操作時 リトライしたり スリープを挟まないと シートの削除 、ブックの保存などが多々失敗する のを回避したい 出来なければ VBAでいうところのResume が無いか知りたい

Question

前提・実現したいこと

最終的には
エラーが起きないように、もしくはVBAでいうところのresume のような
一括でのリトライ処理 をしたいです。


Excel操作時 リトライしたり スリープを挟まないと シートの削除 、ブックの保存などが多々失敗します。
Excel操作一つ一つに リトライ処理を施すのはつらいですし
スリープは処理時間が入れた分だけ伸びますし、実行環境によって必要な時間が変わってきてしまうと思うので避けたいです。

一つの処理の実行中に次の処理が実行されて失敗しているような感じがするのですが、
powershellでのExcel操作で あり得るのでしょうか?

思い当たる節としてはExcel.Applicationのインスタンスを別スレッドで作成、取得 していることくらいですが
影響はあるのでしょうか？
別スレッドは過去の質問にあるクラス _MultiThread を使った方法とほぼ同じです。

発生している問題・エラーメッセージ

Worksheet クラスの Delete プロパティを取得できません。
発生場所 ******************
+                 $BK.Sheets($($Page + $Index + 1)).Delete()
+                              ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

該当のソースコードに近いコード(二台以上のPCで問題の再現が出来ました)

<#
    情報源
        guitarrapc_tech 様
            タイトル : PowerShell による同期処理、非同期処理、並列処理 を考えてみる
            URL : http://tech.guitarrapc.com/entry/2013/10/29/100946

        *** 様
            タイトル : [properties] Powershellクラスはget setプロパティを実装します
            URL : https://code-examples.net/ja/q/25e096e
#>
using namespace  System.Management.Automation.Runspaces;
using namespace System.Collections;
using namespace System.Threading;
using namespace System.Runtime.InteropServices

# クラスを定義します
class _MultiThread
{

   # ノーマル プロパティ
   [array] $RunspaceCollection

   # 非表示 プロパティ
   hidden [RunspacePool] $runspacePool

   # 完了 プロパティ
   hidden [bool]$_AllCompleted = $($this | Add-Member ScriptProperty -Name 'AllCompleted' -Value {
            $($this.runspaceCollection.RunSpace | sort IsCompleted -Unique).IsCompleted -eq $true
        })

   # コンストラクター
   _MultiThread ($MyHost)
   {
        # create Runspace
        [initialsessionstate] $sessionstate = [initialsessionstate]::CreateDefault()
        [int] $minPoolSize = [int] $maxPoolSize = 5000
        $this.runspacePool = [runspacefactory]::CreateRunspacePool($minPoolSize, $maxPoolSize,  $sessionstate, $MyHost) # create Runspace Pool
        $this.runspacePool.ApartmentState = "STA"
        $this.runspacePool.Open() # open pool
   }
   
   # スレッドの追加
   [void]AddThread([scriptblock]$ScriptBlock)
   {
        # Main Invokation
        [powershell] $powershell = [PowerShell]::Create().AddScript($ScriptBlock)
        $powershell.RunspacePool = $this.runspacePool
        $this.RunspaceCollection += New-Object -TypeName PSObject -Property @{
            Runspace = $powershell.BeginInvoke();
            powershell = $powershell
        }
        # $powershell.Dispose()
   }

   # スレッドの追加
   [void]AddThread([scriptblock]$ScriptBlock,[psobject]$Parameters)
   {
        # Main Invokation
        [powershell] $powershell = [PowerShell]::Create().AddScript($ScriptBlock).AddParameters($Parameters)
        $powershell.RunspacePool = $this.runspacePool
        $this.RunspaceCollection += New-Object -TypeName PSObject -Property $(
            [ordered]@{
                Runspace = $powershell.BeginInvoke();
                powershell = $powershell
            }
        )
   }


   # 結果の取得
   [object]GetResult()
   {
        # get process result and end powershell session
        return $(foreach ($runspace in $this.runspaceCollection)
        {
            # get reuslt
            $runspace.powershell.EndInvoke($runspace.Runspace)
        })
   }

   # デストラクタの代わり手動で実行
   [void]Dispose()
   {
        if (!($this.runspacePool.runspacePool.IsDisposed))
        {
            foreach ($Runspace in $this.RunspaceCollection)
            {
                $Runspace.powershell.Dispose()
            }

            # Dispose Runspace
            $this.runspacePool.Dispose()
        }
   }
}

function _New_MultiThread()
{
[OutputType([_MultiThread])]
    $Thread = New-Object _MultiThread($host)
    return $Thread
}

$ExcelPath = "$PSScriptRoot\Tmp.xlsx"
$OpenPath = ""
if (Test-Path -Path $ExcelPath)
{
    $OpenPath = $ExcelPath
}
$Thread = _New_MultiThread

$NewBook = {
    #Param($Path)
    $App = New-Object -ComObject "Excel.Application"
    
    #if($Path -eq "")
    #{
        $null = $App.WorkBooks.Add()
        Write-host "新規ブック"
    #}
    #else
    #{
    #    $null = $App.WorkBooks.Add("$Path")
    #    Write-host "既存ブックをテンプレートとして立ち上げ"
    #}
    
    $App
}

clear-host
$Error.Clear()
try
{


    
    1..2 | foreach { $Thread.AddThread($NewBook<#,$Add_Args#>) }

    while(!($Thread.AllCompleted)) {Start-Sleep -Milliseconds 5}

    if ($OpenPath -ne "")
    {
        Remove-Item -Path $OpenPath
    }

    $Apps = $($Thread.GetResult())

    $Apps | foreach { $_.Caption = "Test_Apps" }

    $App = $Apps[0]

    $BK = $App.ActiveWorkbook

    $BK2 = $Apps[1].ActiveWorkbook
    
    $Sh = $App.ActiveSheet

    $i = 0

    $Tmp = "$PSScriptRoot\tmp2.xlsx"

    if (Test-Path -Path $Tmp)
    {
        Remove-Item -Path $Tmp
    }

    $Bk2.saveAs("$Tmp")

    $Bk2.close($false)

    $Bk2 = $Apps[0].Workbooks.Open($Tmp)
    
    1..10 | foreach { $Bk2.Sheets(1).Copy($Sh) }


    $BK.Sheets(1).Activate()

    $App.DisplayAlerts = $false

    
    Write-host "残りシートが5になるまで削除"
    while($BK.sheets.Count -gt 5){
        $Try = $true
        while ($Try)
        {
            
            try
            {
                $BK.Sheets(6).Delete()
                $Try = $false
            }
            catch
            {
                Write-host "Delete Retry"
            }

        }
    }

    Write-host "$ExcelPath`に保存"
    $Try = $true
    while ($Try)
    {
            
        try
        {
            $null = $BK.SaveAs("$ExcelPath")
            $Try = $false
        }
        catch
        {
            Write-host "Save Retry"
        }

    }

    $Apps | foreach { $_.WorkBooks | foreach {$_.close($false) } }

}
catch
{
    Write-Host "$Error"
}
finally
{
    Write-Host "クリーンアップしています..."
    
    $null = [Marshal]::FinalReleaseComObject($Sh)
    $null = [Marshal]::FinalReleaseComObject($Bk)
    $null = [Marshal]::FinalReleaseComObject($Bk2)
    $Apps | foreach { $_.quit() }
    $null = $Apps | foreach { [Marshal]::FinalReleaseComObject($_) }
    $Thread.Dispose()
    Remove-Item -Path $ExcelPath
    Remove-Item -Path $Tmp
}

試したこと

・リトライ

        $Try = $true
        while ($Try)
        {
            
            try
            {
                $BK.Sheets($($Page + $Index + 1)).Delete()
                $Try = $false
            }
            catch
            {
                Write-host "Delete Retry"
            }

        }

・Start-Sleep

・問題が起きているのと同じようなコードを書いてみたのですが
問題が再現できませんでした....
二台以上のPCで問題の再現が出来ました。

補足情報（FW/ツールのバージョンなど）

Win10

Comments

[imihito]

「Excel.Applicationのインスタンスを別スレッドで作成、取得」はそれぞれ別にExcelを起動している、ということでよろしいですか？

[kamikazelight]

過去の質問にある_MultiThreadに Excelを起動してcomを返す スクリプトブロック渡して 別スレッドで実行　あとで完了確認をしてから用意してあるメソッドGetResult()を使いcomを受け取っています。説明が下手ですみません。。

[kamikazelight]

問題が再現出来なかったのであまり意味が無いかもしれませんが 同じようなコードを載せました。

[kamikazelight]

問題の再現(シート削除でリトライが必要)が出来ましたので載せました。

Answer 1

今更ですがWorksheet クラスの Delete プロパティを取得できません。で検索したところ以下のページが見つかりました。

Excel 2013 をオートメーションで実行し、異なるウィンドウのシートをコピー後にブックのシートを削除すると、エラーが発生する

質問に記載されたコードも同じ処理をしており、記事の回避策、

削除対象のシートのウィンドウをアクティブ ウィンドウにしてから、Sheets.Delete メソッドを実行します。

でエラーが出なくなることも確認出来ました（削除の前に$BK.Activate()を追加）。

まずはエラーメッセージで検索するのが一番という事ですかね。

Comments

[kamikazelight]

Excel 側の問題だったのですね...
ありがとうございました。

Answer 2

まずはtry catchして握りつぶしているのが気になります。
下記のようにしてエラー内容を把握してみてはいかがでしょうか。

catch{
    $error[0] | Format-List -force | Out-File [出力先のファイルパス]
}

その上で対応を考えるのが近道だと思います。

また、while文をかけているようですが、確実にエラーが起こり続ける場合処理が終わらなく成る（無限ループ）が発生し危険ですので必ず抜けることができる様にする等の対応も必要かと思います。

Comments

[kamikazelight]

エラー内容は確認してあります。
毎回同じエラーなので無視しているのですが
それでもログをとったほうがいいのでしょうか?