Comを利用したExcel操作時 リトライしたり スリープを挟まないと シートの削除 、ブックの保存などが多々失敗する のを回避したい 出来なければ VBAでいうところのResume が無いか知りたい
- 評価
- クリップ 2
- VIEW 1,107
前提・実現したいこと
最終的には
エラーが起きないように、もしくは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
-
気になる質問をクリップする
クリップした質問は、後からいつでもマイページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
クリップを取り消します
-
良い質問の評価を上げる
以下のような質問は評価を上げましょう
- 質問内容が明確
- 自分も答えを知りたい
- 質問者以外のユーザにも役立つ
評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。
質問の評価を上げたことを取り消します
-
評価を下げられる数の上限に達しました
評価を下げることができません
- 1日5回まで評価を下げられます
- 1日に1ユーザに対して2回まで評価を下げられます
質問の評価を下げる
teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。
- プログラミングに関係のない質問
- やってほしいことだけを記載した丸投げの質問
- 問題・課題が含まれていない質問
- 意図的に内容が抹消された質問
- 過去に投稿した質問と同じ内容の質問
- 広告と受け取られるような投稿
評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。
質問の評価を下げたことを取り消します
この機能は開放されていません
評価を下げる条件を満たしてません
質問の評価を下げる機能の利用条件
この機能を利用するためには、以下の事項を行う必要があります。
- 質問回答など一定の行動
-
メールアドレスの認証
メールアドレスの認証
-
質問評価に関するヘルプページの閲覧
質問評価に関するヘルプページの閲覧
checkベストアンサー
+1
今更ですがWorksheet クラスの Delete プロパティを取得できません。
で検索したところ以下のページが見つかりました。
Excel 2013 をオートメーションで実行し、異なるウィンドウのシートをコピー後にブックのシートを削除すると、エラーが発生する
質問に記載されたコードも同じ処理をしており、記事の回避策、
削除対象のシートのウィンドウをアクティブ ウィンドウにしてから、Sheets.Delete メソッドを実行します。
でエラーが出なくなることも確認出来ました(削除の前に$BK.Activate()
を追加)。
まずはエラーメッセージで検索するのが一番という事ですかね。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
0
まずはtry catchして握りつぶしているのが気になります。
下記のようにしてエラー内容を把握してみてはいかがでしょうか。
catch{
$error[0] | Format-List -force | Out-File [出力先のファイルパス]
}
その上で対応を考えるのが近道だと思います。
また、while文をかけているようですが、確実にエラーが起こり続ける場合処理が終わらなく成る(無限ループ)が発生し危険ですので必ず抜けることができる様にする等の対応も必要かと思います。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
15分調べてもわからないことは、teratailで質問しよう!
- ただいまの回答率 88.33%
- 質問をまとめることで、思考を整理して素早く解決
- テンプレート機能で、簡単に質問をまとめられる
質問への追記・修正、ベストアンサー選択の依頼
imihito
2018/12/01 16:21
「Excel.Applicationのインスタンスを別スレッドで作成、取得」はそれぞれ別にExcelを起動している、ということでよろしいですか?
kamikazelight
2018/12/02 01:21
過去の質問にある_MultiThreadに Excelを起動してcomを返す スクリプトブロック渡して 別スレッドで実行 あとで完了確認をしてから用意してあるメソッドGetResult()を使いcomを受け取っています。説明が下手ですみません。。
kamikazelight
2018/12/03 14:29
問題が再現出来なかったのであまり意味が無いかもしれませんが 同じようなコードを載せました。
kamikazelight
2018/12/04 10:20
問題の再現(シート削除でリトライが必要)が出来ましたので載せました。