More than 1 year has passed since last update.

データのコピペを自動化してリストから神エクセルを量産する

Last updated at 2023-07-12Posted at 2023-07-05

改良しました

本記事のコードを改良して新たに記事を書きました。本記事も一応残しておきますが、これからやる方は新しいほうの記事↓を基にしたほうが良いです。

改良前の記事ここから

※当記事に登場する人名地名等はすべて架空です。実在の人物地名その他いかなる個人情報とも関係ありません。
※※当記事は随時サイレント修正しています。コードの修正や重大なバグがあった場合のみお知らせいたします。

これを

こうする（そしてたくさんつくる）

データを雛形にひとつひとつコピペすればいいのですが、とにかく数が多いので自動化します。
ひな形のフォーマットは問わないので、「指摘なし」のレビュー記録表なんかも量産できます。

どうやる

① ファイルを2つ作る

下記コードをメモ帳にコピーして指定の通りに名前を付けて保存します。

Excelファイルを量産.bat

powershell -NoProfile -ExecutionPolicy Unrestricted "./Excelファイルを量産.ps1" >> Excelファイルを量産.log
exit

Excelファイルを量産.ps1

####################
#生成方法
#①「template.xlsx」（ひな形）を用意する。
#②「template.xlsx」を同じフォルダにコピーする。コピーしたファイルの名前を「target.xlsx」に変更する。
#③「template.xlsx」の一番後ろにシートを追加し、シート名を「List」に変更する。
#④「List」シートのA列に作成予定のファイル名をリストアップする。B列以降に他の項目のリストを書いたり貼り付けたりする。数式を使ってもOK 1行目は見出しとする
#⑤「List」シートの空白セルに何もデータが入っていないことを確認する（④で入力したセル以外は一度削除すると確実）
#⑥「target.xlsx」の、レビュー記録表のテンプレートが記載されているシートを全選択し、セルの結合を解除する
#⑦「template.xlsx」「target.xlsx」を保存し、エクセルを閉じる
#⑧当ps1ファイルの「設定」部分(18行目～)に変数を設定し、保存する
#上記①～⑧を実行し、下記の状態になったら準備完了：
#「template.xlsx」編集していない状態のひな形がある。このほか、「List」シートがある
#「target.xlsx」すべてのセルの結合を解除した状態の、レビュー記録表のひな形シートがある
#当ps1ファイルの設定部分(18行目～)に情報を入力してある
####################

####################
#設定
$targetPath = Convert-Path "./target.xlsx"
$templatePath = Convert-Path "./template.xlsx"
#
#ひな形が何シート目にあるか
$sheetNo = 1
#
#セルの設定
#コピー先のセルを、target.xlsxのListシートの列数分だけ定義する
$targetCellMap = @{
    #A列はファイル名なので定義しない
    "B" = "I2" #B列のデータのコピー先
	"C" = "C2" #C列のデータのコピー先（以下同様にD,E,F…の要領でコピー先をリストに追加）
	"D" = "C4"
	"E" = "C5"
	"F" = "C6"
	"G" = "C7"
}
#
####################


#　アセンブリの読み込み インプットボックス表示に必要
Add-Type -Assembly System.Windows.Forms

function Log($str) {
	$timestamp = Get-Date
	Write-Host "$timestamp $str"
}

try {

	Log "********* Excelファイルを量産.ps1 起動 ***********"

	$msgBoxTxt = "実行前にテンプレートを用意してください。用意手順はソースをご覧ください。実行してもよろしいですか？"
	$msgBoxInput = [System.Windows.Forms.MessageBox]::Show($msgBoxTxt,'アプリの実行確認','YesNo','Question')

	if($msgBoxInput -ne "Yes") {
		Log "ユーザー入力により処理を中止します。"
		Exit
	}
	
	# エクセルを開く準備
	$excel = New-Object -ComObject Excel.Application
	$excel.Visible = $false
	$excel.DisplAyalerts = $false
    $excel.ScreenUpdating = $false
    $excel.EnableEvents = $false

   	$updateLinks = 0
	$isReadOnly = $false

	try {
		#テンプレートファイルを開く
		$templateBook = $excel.Workbooks.Open($templatePath, $updateLinks, $isReadOnly)
	}
	catch {
		Log "エクセルのオープンでエラーが発生しました。"
		throw
	}
	
	try {

        $row = 2 #見出しを飛ばすため2
		$listSheet = $templateBook.Sheets.Item("List")
		$templateSheet = $templateBook.Sheets.Item($sheetNo)

		while ($true) {
		
            $bookName = $listSheet.Cells.Range("A" + $row).Text
		
            if(($bookName -eq $null) -or ($bookName -eq "")) {
                Log "A列にファイル名がないため、ループを抜けます。"
                break
			}
			
			Log "$bookName を作成中..."
			
            #ターゲットファイルを開く
			$targetBook = $excel.Workbooks.Open($targetPath, $updateLinks, $isReadOnly)
            $targetSheet = $targetBook.Sheets.Item($sheetNo)

            #TemplateブックのListシートから情報をコピー＆Targetブックに貼り付け
			$targetCellMap.Keys | ForEach-Object {
                $targetCell = $targetCellMap[$_]
                $sourceColumn = $_
				
				$listSheet.Range($sourceColumn + $row).Copy() | Out-Null
                $targetSheet.Range($targetCell).PasteSpecial(-4163) | Out-Null #値のみ貼り付け
			}
			
            #Templateブックのレビュー記録表シートから書式をコピー＆Targetブックに貼り付け
			$templateSheet.Cells.Copy() | Out-Null
			$targetSheet.Cells.PasteSpecial(-4122) | Out-Null #書式のみ貼り付け
			
			$path = Join-Path (Convert-Path .) $bookName
			
			$targetSheet.Cells(1,1).Select() | Out-Null
			$targetBook.SaveAs($path)
			$isSaveChanges = $true
			$targetBook.Close($isSaveChanges)
			
			Log "$bookName を作成しました。"
			
			$row++
		}
	}
	catch {
		Log "ファイルの作成でエラーが発生しました。"
		throw
	}
}
catch {
	$errorStr = $error[0] | Out-String
	Log $errorStr
	Log "エラーが発生したため、処理を終了します。"
}
finally {
    try {
		if ($excel -ne $null) {
			$excel.Quit()
		}
		$targetSheet = $null
		$listSheet = $null
		$templateSheet = $null
		$targetBook = $null
		$templateBook = $null
		$excel = $null
		[System.GC]::Collect()
	}
	catch {
		Log "finally処理の中でエラーが発生しました。"
		$errorStr = $error[0] | Out-String
		Log $errorStr
    }
	Log "********* Excelファイルを量産.ps1 終了 ***********"
	pause
}