この記事ではPower Automate for desktopでExcelファイルのデータを別のExcelに転記する方法について学習します。
今回のゴール
▲こんな感じの請求データ(請求データ.xlsx)を、
▲フォーマットの決まった別のExcelファイルの請求書(請求書.xlsx)へ転記します。
作成したフローの完成図
フローの作成
請求データExcelを読み込む
まずは請求データのExcelを読み込む部分を作っていきます。
▲「Excel」グループの中の「Excelの起動」アクションを追加します。パラメータを入力します。
- Excelの起動:次のドキュメントを開く
- ドキュメントパス:C:\Users\user\Desktop\請求データ.xlsx
- インスタンスを表示する:オン
- 読み取り専用として開く:オン
- 生成された変数:ExcelSeikyuData
今回はExcel転記ということで同一フローの中で2つのExcelを扱うのでExcelInstance変数の名前を変更します。ExcelSeikyuDataとしました。
PADでExcelを使うときは安全のためなるべく読み取り専用で開いた方が良いかなと思います。
▲「Excel」グループの中の「アクティブなワークシートの設定」アクションを追加します。パラメータを入力します。
- Excelインスタンス:%ExcelSeikyusyo%
- 次と共にワークシートをアクティブ化:名前
- ワークシート名:請求データ
シートを選択します。「請求データ」という名前のシートをターゲットにしています。シートが複数存在する場合は必ずこのアクションを使いましょう。このシートを使わないと前回閉じたときにアクティブだったシートをターゲットとしてしまいます。
▲「Excel」グループの中の「Excelワークシートから最初の空の列や行を取得」アクションを追加します。パラメータを入力します。
- Excelインスタンス:%ExcelSeikyusyo%
▲「Excel」グループの中の「Excelワークシートから読み取り」アクションを追加します。パラメータを入力します。
- Excelインスタンス:%ExcelSeikyusyo%
- 取得:セル範囲の値
- 先頭列:1
- 先頭行:1
- 最終列:%FirstFreeColumn – 1%
- 最終行:%FirstFreeRow – 1%
請求データのExcelファイルはA1セルから表のデータが入力されているので開始列・行は「1」となります。最終行・列は「Excelワークシートから最初の空の列や行を取得」アクションが生成する利用しています。詳細は下記の記事をご覧ください。
▲「Excel」グループの中の「Excelを閉じる」アクションを追加します。
- Excelインスタンス:%ExcelSeikyusyo%
- Excelを閉じる前:ドキュメントを保存しない
▲フローを実行して、ExcelData変数をダブルクリックして中身を確認します。Excelのデータを読み込むことができたので次に進みます。
書き込む処理を作成します
請求書に書き込む部分を作っていきます。
▲「Excel」グループの中の「Excelの起動」アクションを追加します。
- Excelの起動:次のドキュメントを開く
- ドキュメントパス:C:\Users\user\Desktop\請求書テスト.xlsx
- インスタンスを表示する:オン
- 読み取り専用として開く:オン
- 生成された変数:ExcelSeikyusyo
書き込みを行いますがベースファイルに変更を加えたくないので「読み取り専用として開く」にチェックを入れます。読み取り専用で開いても書き込みを行うことはできるので安心してください。読み取り専用で書き込みを行った場合、上書き保存ではなく「名前を付けて保存」で保存します。
生成された変数の名前は「ExcelSeikyusyo」としました。
▲「変数」グループの中の「変数の設定」アクションを追加します。
ここには書き込む先の請求書が、11行目から入力欄となっているため最初の入力行として使用します。この変数をループの中で1足しながら書き込む位置を調整します。
▲「ループ」グループの中の「For each」アクションを追加します。
- 反復処理を行う値:ExcelData
▲「Excel」グループの中の「Excelワークシートに書き込み」アクションを追加します。
- Excelインスタンス:%ExcelSeikyusyo%
- 書き込む値:%CurrentItem[‘品目’]%
- 書き込むモード:指定したセル上
- 列:2
- 行:%NewVar%
▲このアクションは品目を書き込みます。
▲「Excel」グループの中の「Excelワークシートに書き込み」アクションを追加します。
- Excelインスタンス:%ExcelSeikyusyo%
- 書き込む値:%CurrentItem[‘合計’]%
- 書き込むモード:指定したセル上
- 列:3
- 行:%NewVar%
▲このアクションは金額列を書き込みます。
▲「変数」グループの中の「変数を大きくする」アクションを追加します。
- 変数名:%NewVar%
- 大きくする変数:1
書き込みが終わったら、NewVarに入っている11という数字に1を足します。これによって次のループで12行目に書き込むことができます。
▲「Excel」グループの中の「Excelを閉じる」アクションを追加します。
- Excelインスタンス:%ExcelSeikyusyo%
- Excelを閉じる前:名前をつけてドキュメントを保存
- ドキュメント形式:Excelブック(.xlsx)
- ドキュメントパス:C:\Users\user\Desktop\請求書\請求書
Excelを別名で保存しています。ドキュメントパスには拡張子を付ける必要はありません。
実際に動いている動画
▲動画ではわかりやすいように請求書.xlsxを開くときにキーの送信コマンドで「ウィンドウズキー+↑キー」(ウィンドウ最大化のショートカット)を追加しています。
Robinソースコード
Robinのソースコードです。Power Automate for desktopのフローデザイナーに張り付けることで今回ここで作成したフローを再現できます。
Excel.LaunchExcel.LaunchAndOpen Path: $'''C:\\Users\\user\\Desktop\\請求データ.xlsx''' Visible: True ReadOnly: True LoadAddInsAndMacros: False Instance=> ExcelSeikyuData Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelSeikyuData Name: $'''請求データ''' Excel.GetFirstFreeColumnRow Instance: ExcelSeikyuData FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow Excel.ReadFromExcel.ReadCells Instance: ExcelSeikyuData StartColumn: 1 StartRow: 1 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData Excel.CloseExcel.Close Instance: ExcelSeikyuData Excel.LaunchExcel.LaunchAndOpen Path: $'''C:\\Users\\user\\Desktop\\請求書テスト.xlsx''' Visible: True ReadOnly: True LoadAddInsAndMacros: False Instance=> ExcelSeikyusyo SET NewVar TO 11 LOOP FOREACH CurrentItem IN ExcelData Excel.WriteToExcel.WriteCell Instance: ExcelSeikyusyo Value: CurrentItem['品目'] Column: 2 Row: NewVar Excel.WriteToExcel.WriteCell Instance: ExcelSeikyusyo Value: CurrentItem['合計'] Column: 3 Row: NewVar Variables.IncreaseVariable Value: NewVar IncrementValue: 1 IncreasedValue=> NewVar END Excel.CloseExcel.CloseAndSaveAs Instance: ExcelSeikyusyo DocumentFormat: Excel.ExcelFormat.OpenXmlWorkbook DocumentPath: $'''C:\\Users\\user\\Desktop\\請求書\\請求書'''
請求書のファイル名に今日の日付を自動挿入する場合は?
Excelを保存する時に日付を付けて保存したいケースがあるかと思います。例えば2022年1月18日にフローを実行した場合「請求書_2022-01-18.xlsx」のようにExcelファイル名を命名するケースです。このようなときは「現在の日時を取得します」アクションを使って文字列を作成する方法があります。下記の記事で紹介していますのでご参考になさってください。
読み込まれたExcelDataに空白行がたくさんある場合は?
▲このような空白行を含むExcelの場合、
▲ExcelData変数に無駄な行が含まれてしまいます。(3行目から6行目)
▲このままループをまわしてしまうと、データが入っていない行の分もループが回ってしまって無駄な時間がかかってしまうので上図のように空白かどうかの処理を入れるといいかなと思います。
▲品目列が空白だったら空行であると判断しています。空白だったら「ループを抜ける」アクションでループを抜けます。これによって無駄なループを避けることができます。
Robinソースコード
Excel.LaunchExcel.LaunchAndOpen Path: $'''C:\\Users\\user\\Desktop\\請求データ.xlsx''' Visible: True ReadOnly: True LoadAddInsAndMacros: False Instance=> ExcelSeikyuData Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelSeikyuData Name: $'''請求データ''' Excel.GetFirstFreeColumnRow Instance: ExcelSeikyuData FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow Excel.ReadFromExcel.ReadCells Instance: ExcelSeikyuData StartColumn: 1 StartRow: 1 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData Excel.CloseExcel.Close Instance: ExcelSeikyuData Excel.LaunchExcel.LaunchAndOpen Path: $'''C:\\Users\\user\\Desktop\\請求書テスト.xlsx''' Visible: True ReadOnly: True LoadAddInsAndMacros: False Instance=> ExcelSeikyusyo SET NewVar TO 11 LOOP FOREACH CurrentItem IN ExcelData IF IsEmpty(CurrentItem['品目']) THEN EXIT LOOP END Excel.WriteToExcel.WriteCell Instance: ExcelSeikyusyo Value: CurrentItem['品目'] Column: 2 Row: NewVar Excel.WriteToExcel.WriteCell Instance: ExcelSeikyusyo Value: CurrentItem['合計'] Column: 3 Row: NewVar Variables.IncreaseVariable Value: NewVar IncrementValue: 1 IncreasedValue=> NewVar END Excel.CloseExcel.CloseAndSaveAs Instance: ExcelSeikyusyo DocumentFormat: Excel.ExcelFormat.OpenXmlWorkbook DocumentPath: $'''C:\\Users\\user\\Desktop\\請求書\\請求書'''
高速に一括で書き込む方法
この記事ではExcelファイルに1セルずつ書きこみを行いました。より高速に書きこむ方法としてDatatableをダンプする方法があります。
書き込み先のExcelファイルのフォーマットによっては使えない場合もありますが、1回のアクションの実行で複数セルに書き込むことができるのでより高速にフローを実行することができます。下記の記事をご参考になさってください。
まとめ
この記事ではExcelの転記について学習しました。
このブログではRPA・ノーコードツール・VBA/GAS/Pythonを使った業務効率化などについて発信しています。
参考になりましたらブックマーク登録お願いします!
Power Automate学習教材
▲Kindleと紙媒体両方提供されています。デスクトップフロー、クラウドフロー両方の解説がある書籍です。解説の割合としてはデスクトップフロー7割・クラウドフロー3割程度の比率となっています。両者の概要をざっくり理解するのにオススメです。
▲Power Automate for Desktopの基本をしっかり学習するのにオススメです。この本の一番のメリットはデモWebシステム・デモ業務アプリを実際に使ってハンズオン形式で学習できる点です。本と同じシステム・アプリを使って学習できるので、本と自分の環境の違いによる「よく分からないエラー」で無駄に躓いて挫折してしまう可能性が低いです。この点でPower Automate for desktopの一冊目のテキストとしてオススメします。著者は日本屈指のRPAエンジニア集団である『ロボ研』さんです。
▲Power Automate クラウドフローの入門書です。初心者の方には図解も多く一番わかりやすいかと個人的に思っています。
Microsoft 365/ Power Automate / Power Platform / Google Apps Script…
▲Udemyで数少ないPower Automateクラウドフローを主題にした講座です。セール時は90%OFF(1200円~2000円弱)の価格になります。頻繁にセールを実施しているので絶対にセール時に購入してくださいね。満足がいかなければ返金保証制度がありますので安心してご購入いただけます。