powershellでexcelシート操作時エラー

質問

powershellで複数のexcelファイルを各々一つの既存excelファイルの各ワークシトに書き込む方法として下記のコードで実行するとワークシート名にファイル名と同じシート名がある場合「sheet1(2)」となって追加されます。結果自体問題ないのですが、powershellの方に次のメッセージが出ます。このメッセージは、無視していいのかあるいは出ないようにする対処方法があるのかお教えください。
発生したエラーメッセージ
OperationStopped:
Line |
7 | $Workbook.Sheets.Item($Worksheet.Index - 1).Name = $Everyexcel.Name.R …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| この名前は既に使用されています。別の名前を入力してください。
実行コード
$ExcelObject=New-Object -ComObject excel.application
$ExcelObject.visible=$false
$ExcelObject.DisplayAlerts=$false
#$ExcelFiles=Get-ChildItem -Path D:\test_data\data\*.xlsx
$ExcelFiles=Get-ChildItem -Path @("D:\test\test\book1.xlsx","D:\test\test\book2.xlsx")
$outputFile = "D:\test_data\AAE.xlsx"
$Workbook=$ExcelObject.Workbooks.Open($outputFile)
$Worksheet=$Workbook.Sheets.Item(1)
foreach($ExcelFile in $ExcelFiles){
$Everyexcel=$ExcelObject.Workbooks.Open($ExcelFile.FullName)
$Everysheet=$Everyexcel.sheets.item(1)
$Everysheet.Copy($Worksheet)
$range = $Workbook.Sheets.Item($Worksheet.Index - 1).usedRange
$range.EntireColumn.AutoFit() | out-null
$Workbook.Sheets.Item($Worksheet.Index - 1).Name = $Everyexcel.Name.Replace(".xlsx", "")

$Everyexcel.Close()
}
$Workbook.SaveAs($outputFile)
$Workbook.Close($true)
$ExcelObject.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Worksheet) > $null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook) > $null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelObject) > $null
[gc]::Collect()
[GC]::WaitForPendingFinalizers()
よろしくお願いいたします。

回答No.1

luka3 · ベストアンサー

foreachの部分を次のように修正します。（動作未検証です）

foreach($ExcelFile in $ExcelFiles){
$Everyexcel=$ExcelObject.Workbooks.Open($ExcelFile.FullName)
$Everysheet=$Everyexcel.sheets.item(1)
$Everysheet.Name = $Everyexcel.Name.Replace(".xlsx", "")
$Everysheet.Copy($Worksheet)
$range = $Workbook.Sheets.Item($Worksheet.Index - 1).usedRange
$range.EntireColumn.AutoFit() | out-null
$Everyexcel.Close($false)
}

簡単に解説すると、シートをコピーする前に、コピー元シートの名前を先に変更してからコピーしてます。
ブックを変更するので、Closeする時「変更を保存しますか？」と聞かれるため、「保存しない」引数を指定します。

この変更は、コピー元のブックの2番目以降のシートに「ファイル名と同じ名前のシート」がない、という前提で動作します。
コピー元がSheet1のシートしかない、またはシート自体が1つしかないのであれば問題ありません。

お礼

lula3様
いつも適切な回答ありがとうございます。
foreach(・・・部分を入れ替えて実行したところコピー先のファイルにシートがsheei1,data1,data2が既にあった場合の結果は、既存3シートに加えdata1(2),data2(2)として新たに追加されていました。またメッセージも出ませんでした。
下記の前提条件に関しましては、コピー元を作成するためにDBから1シートの新規Excelファイルとしてしか出力できないので問題はありません。・・・DB関連のQ&Aサイトではたまに複数シートで出来ないかの質問が出てきていますが・・・
>この変更は、コピー元のブックの2番目以降のシートに「ファイル名と同じ名前のシート」がない、という前提で動作します。
コピー元がSheet1のシートしかない、またはシート自体が1つしかないのであれば問題ありません。

補足

質問時のコードと回答で試したコードでコピー元の部分が異なっていたのでお礼でシート名が違っています。