Power Automate Desktop フォルダー内のExcelブックをワークシートにまとめる

ピピル

毎月、各支店から売上が入力されているExcelブックが送られてくるのですが、別々のExcelブックに保存されています。
送られてくる支店別の売上表を1つのワークシートにまとめたいのですが、何か良い方法はないですか。

ピピン

デスクトップ用Power Automateを使用すれば、自動で複数のExcelブックを1つのワークシートにまとめることができます。
同じ形式のExcelブックを決まったフォルダーに置いてPADを実行すると、自動で1つのワークシートにまとめられます。
1つのワークシートにまとめることで、ピボットテーブルやフィルターを使用するときも便利です。

日報や報告書など、支店や部署及び従業員ごとに送られてくる、決まった様式の複数のExcelブックを1つのワークシートにまとめる方法をご紹介します。

今回は毎月支店から送られてくる売上の報告書を1つのワークシートにまとめる方法をご紹介します。

2つのExcelブックと各Excelブックに2つのワークシートが作成されています。

複数のExcelブックを1つのワークシートにまとめます。

設置するフロー

1.フォルダーの選択ダイアログを表示

まとめたいExcelブックが保存されているフォルダーを選択します。

今回は各支店から送られてくる売上日報が保存されているフォルダーを選択します。

1.アクションの「メッセージボックス」から「フォルダーの選択ダイアログを表示」をワークスペースにドラッグ＆ドロップします。

追加するアクション

・ダイアログの説明：説明が必要な場合は入力します。

・初期フォルダー：まとめたいExcelブックが保存されているフォルダーの場所が決まっている場合は、初期フォルダーにExcelブックの入っている親フォルダーを入力します。
※右にあるフォルダーアイコンから選択できます。
場所が決まっていない場合は空白のままにします。

2.If

上記の「フォルダーの選択ダイアログを表示」で表示されたフォルダー選択をキャンセルした場合に、それ以降の処理を行わないようにIFで処理します。

1.アクションの「条件」から「If」をワークスペースにドラッグ＆ドロップします。

追加するアクション

・最初のオペランド：カスタムフォームを表示で生成された変数 %ButtonPressed% を入力します。

・演算子：と等しくない(<>) を選択します。

・2番目のオペランド：Cancel と入力します。

IFの条件分岐で、キャンセルではない(等しくない）場合に処理が実行されます。
キャンセルした場合は、処理が中断されます。

3.フォルダー内のファイルを取得

フォルダー内のExcelブックを取得します。

1.アクションの「フォルダー」から「フォルダー内のファイルを取得」をIfの中にドラッグ＆ドロップします。

追加するアクション

・フォルダー：%SelectedFolder%
「フォルダーの選択ダイアログを表示」で生成された変数を使用します。

・ファイルフィルター：*.xlsx
ファイルの拡張子をExcelに制限したいので、*の後ろにExcelの拡張子を入力します。
※2つの拡張子に対応する場合は*.xl*と入力します。
Excelブック(xlsx)、マクロ有効ブック(xlsm)の両方に対応できます。

・サブフォルダーを含める：サブフォルダーを含める場合はチェックを入れます。

4.Excelの起動

「フォルダー内のファイルを取得」で取得したExcelブックを新規のExcelブックに転記します。

売上日報のデータをまとめる新規のExcelブックを起動します。

1.アクションの「Excel」から「Excelの起動」をIfの中の「フォルダー内のファイルを取得」の下にドラッグ＆ドロップします。

追加するアクション

アクションの「Excel」から「Excelの起動」を「フォルダー内のファイルを取得」の下のIfの中にドラッグ＆ドロップします。

Excelの起動：空のドキュメントを使用します。
生成された変数：変数を「Excel」に変更します。
※生成された変数をそのまま使用しても問題ないですが、自分で把握しやすい変数名に変えることもできます。

5.変数の設定

Excelの転記先の行を自動で変更する為に、変数を設定します。

1.アクションの「変数」から「変数の設定」をIfの中の「Excelの起動」の下にドラッグ＆ドロップします。

追加するアクション

アクションの「変数」から「変数の設定」を「Excelの起動」の下のIfの中にドラッグ＆ドロップします

変数：NewVar
変数名を変更したい場合は、NewVarをクリックして変数名を入力して変更します。
値：２
列名の次の2行目からデータを入力していくので、値を２に設定します。

6.End

1.「変数の設定」の下に「IfのEnd」を設置します。

7.Loop

Loopを設置して、「フォルダー内のファイルを取得」で読み取ったExcelブックを起動し、すべてのワークシートからデータを取得して、新規で起動したExcelブックに書き込んでいきます。

Excelブックの数だけ「ループ」で繰り返し処理を行います。

1.アクションの「ループ」から「Loop」をワークスペースにドラッグ＆ドロップします。

追加するアクション

開始値：0
終了：%Files.Count-1%　読み取ったExcelブックの番号が0から始まるので-1します。
増分：1

8.Excelの起動（転記元のExcelブック）

「フォルダー内のファイルを取得」で生成された変数「Files」に格納されているExcelブックを開きます。

追加するアクション

1.アクションの「Excel」から「Excelの起動」をワークスペースにドラッグ＆ドロップします。

・Excelの起動：次のドキュメントを開く
・ドキュメントパス：%Files[LoopIndex]%
LoopIndexを使用してExcelブックの数だけ開きます。
生成された変数：%ExcelInstance%

9.すべてのExcelワークシートの取得

LoopアクションでExcelブックのワークシートの数だけループ処理するため、「すべてのExcelワークシートの取得」から生成されたSheetNames.Countを取得します。

SheetNames.Countにはワークシートの数が格納されているので、その数を利用してループ回数を自動で決定します。

追加するアクション

1.アクションの「Excel」の「詳細」から「すべてのExcelワークシートの取得」をワークスペースにドラッグ＆ドロップします。

・Excelインスタンス：%ExcelInstance%
生成された変数：SheetNames

10.Loop

LoopアクションでExcelブックのワークシートの数だけ、ループ処理します。

今回はExcelブックに2つのワークシートが作成されています。

追加するアクション

1.アクションの「ループ」から「Loop」をワークスペースにドラッグ＆ドロップします。

・開始値：１

次のアクションで使用する「アクティブなExcelワークシートの設定アクション」の「次と共にワークシートをアクティブ化」をインデックスに設定する場合、インデックス番号は1から始まりますので、開始値に1を入力します。

・終了：%SheetNames.Count%

SheetNames.Countのカウント数だけ、ループさせます。

%SheetNames.Count%の選択方法
①パラメータ選択の終了：右側の{☓}をクリックします。
②SheetNamesの▷をクリックします。
③.Countをクリックして選択した状態にします。
④選択をクリックします。

・増分：1
ループの数を1ずつ増やしていきます。

フロー番号11で使用する「アクティブなExcelワークシートの設定」のワークシートインデックスに今回生成された変数LoopIndexを使用します。

11.アクティブなExcelワークシートの設定

Excelブックから読み取りたい、ワークシートを設定します。

今回は全てのワークシートからExcelDataを取得します。

追加するアクション

1.アクションの「Excel」から「アクティブなExcelワークシートの設定」をワークスペースにドラッグ＆ドロップします。

・Excelインスタンス：%ExcelInstance%

・次と共にワークシートをアクティブ化：インデックス
ワークシートを左から順番にインデックス番号で開いていきます。

・ワークシートインデックス：%LoopIndex%
すべてのワークシートを開くために、ワークシートインデックスにLoopで生成された変数LoopIndexを使用し、ループの回数でインデックスの番号を取得します。

12.Excelワークシートから最初の空の列や行を取得

「Excelワークシートから最初の空の列や行を取得」のアクションを設置し、Excelワークシートの最終行のデータを取得します。

追加するアクション

1.アクションの「Excel」から「Excelワークシートから最初の空の列や行を取得」をワークスペースにドラッグ＆ドロップします。

・Excelインスタンス：%ExcelInstance%

13.Excelワークシートから読み取る

Excelブックのワークシートから、読み取るセル範囲を設定します。

追加するアクション

1.アクションの「Excel」から「Excelワークシートから読み取る」をワークスペースにドラッグ＆ドロップします。

・Excelインスタンス：%ExcelInstance%
・取得：セル範囲の値
・先頭列：A
・先頭行：1
・最終列：G
・最終行：%FirstFreeRow%　・Excelワークシートから最初の空の列や行を取得で生成された変数を使用します。
生成された変数%ExcelData%