Zenn
🌊

Power Automate Desktop CSVをExcelに転記 アクション編

2023/11/09に公開
VBA
Power Automate
tech

PADのアクションでデータ結合してみる

はじめに

PAD(Power Automate Desktop)で複数のCSVデータを追加して1つの表にまとめます。

  • 読み込む順番が決まっている
  • 転記する項目は一部で良い
  • Excelファイルを作成する
  • 表に罫線を付ける
  • オートフィルターのボタンを表示する

Power Automate Desktop CSVをExcelに転記 Power Query編の続きになります。
前回はPower QueryとVBAを使いましたが、今回はPADのアクションのみで頑張ってみたいと思います。と言いつつVBS使いますが。非推奨にはなったけどExcelの操作が……。PowerShellにした方がいいですかね？

データの取り込み

  • Excelファイルを設定ファイルとして利用しています。記入している情報は以下の通り。

    • CSVファイルを取り込む順番（都市テーブル）
    • 保持するデータの項目（項目テーブル）

  • 設定ファイルからデータテーブルへ取り込みについてはここでは省略します。おそらく皆さんも共通フレームワークなど用意されていたりして、それを利用しているのではないでしょうか。

  • 仮変数のところは実際には既にデータテーブルに値が入っているものとしてみてください。

PAD 毎回リストを作り直す
FUNCTION データ取り込み GLOBAL
    **REGION 仮変数
    # 設定ファイル（Excel）から読み込んだ変数が入る予定
    SET workDir TO $'''%''%'''
    Variables.CreateNewDatatable InputTable: { } DataTable=> 項目テーブル
    Variables.CreateNewDatatable InputTable: { } DataTable=> 都市テーブル
    **ENDREGION
    Variables.RetrieveDataTableColumnIntoList DataTable: 項目テーブル ColumnNameOrIndex: $'''Item''' ColumnAsList=> 項目リスト
    SET 新規テーブル TO { ^項目リスト }
    **REGION 追加
    LOOP FOREACH 都市 IN 都市テーブル
        File.ReadFromCSVFile.ReadCSV CSVFile: $'''%workDir%\\%都市['Value']%''' Encoding: File.CSVEncoding.SystemDefault TrimFields: True FirstLineContainsColumnNames: True ColumnsSeparator: File.CSVColumnsSeparator.Tab CSVTable=> CSVTable
        LOOP FOREACH CurrentItem IN CSVTable
            Variables.CreateNewList List=> List
            LOOP FOREACH 列名 IN 項目リスト
                Variables.AddItemToList Item: CurrentItem[列名] List: List
            END
            Variables.AddRowToDataTable.AppendRowToDataTable DataTable: 新規テーブル RowToAdd: List
        END
    END
    **ENDREGION
END FUNCTION

Sample1

PAD リストのクリアを使う
FUNCTION データ取り込み2 GLOBAL
    **REGION 仮変数
    # 設定ファイル（Excel）から読み込んだ変数が入る予定
    SET workDir TO $'''%''%'''
    Variables.CreateNewDatatable InputTable: { } DataTable=> 項目テーブル
    Variables.CreateNewDatatable InputTable: { } DataTable=> 都市テーブル
    **ENDREGION
    Variables.RetrieveDataTableColumnIntoList DataTable: 項目テーブル ColumnNameOrIndex: $'''Item''' ColumnAsList=> 項目リスト
    SET 新規テーブル TO { ^項目リスト }
    **REGION 追加
    Variables.CreateNewList List=> List
    LOOP FOREACH 都市 IN 都市テーブル
        File.ReadFromCSVFile.ReadCSV CSVFile: $'''%workDir%\\%都市['Value']%''' Encoding: File.CSVEncoding.SystemDefault TrimFields: True FirstLineContainsColumnNames: True ColumnsSeparator: File.CSVColumnsSeparator.Tab CSVTable=> CSVTable
        LOOP FOREACH CurrentItem IN CSVTable
            LOOP FOREACH 列名 IN 項目リスト
                Variables.AddItemToList Item: CurrentItem[列名] List: List
            END
            Variables.AddRowToDataTable.AppendRowToDataTable DataTable: 新規テーブル RowToAdd: List
            Variables.ClearList List: List
        END
    END
    **ENDREGION
END FUNCTION

Sample2

リストの使い方で2つ用意してみました。
ざっくりと、PADに貼り付けた時の行数で説明してみます。

No.1 ～ 6

作りこむのが面倒なのでエラーにならない程度に仮で変数を置いています。

No.7

設定ファイルがデータテーブルで取り込まれると想定して、項目を記入した列をリストにします。

No.8

項目リストを使って、空のデータテーブルを作成します。
[変数の設定]アクションを使って、%{ ^項目リスト }%とすることで、見出しに変数を使用できます。

氏名 氏名（ひらがな） 年齢 生年月日 メールアドレス

こんな感じ。

No.9 ～

  • 都市一覧テーブルから都市を取り出す
    • CSVを読み込み(テーブルのValue列にファイル名が入っている)
      • 項目リストから列名を取り出す
        • %CurrentItem[列名]%で、順番にListに追加
      • 出来上がったListを新規テーブルに挿入

項目リストで指定した列の値が、新規テーブルに挿入されます。

テーブルをFor eachで１行ずつ処理しているので、デバッグだとめちゃくちゃ遅いです。実行だとまあ……普通か。という感じですけど。取り込むデータもそんなに量はないので。
でももっと良い感じに出来たら良いなあ。

転記

Excelに貼り付けるとき、データの先頭が0から始まっていて欠けたら困る場合にセルの書式設定で「文字列」にすることがあると思います。
貼り付けた後で文字列にしても意味がないので先に設定します。

VBScript セルの書式設定を「文字列」にする
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("%ExcelFile%")
objExcel.Application.Visible = False

Call SetNumberFormat(objWorkbook, 1)

objWorkbook.Save
objWorkbook.Close True
objExcel.Quit

Sub SetNumberFormat(objWorkbook, index)
	Set objSheet = objWorkbook.Sheets(index)
	objSheet.Columns("A").NumberFormatLocal = "@"
End Sub

次に、PADのExcelアクションでデータテーブルを貼り付けます。この辺は省略。
あとは、書式設定をしておしまい。

VBScript 表に罫線を引く。オートフィルターを設定
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("%ExcelFile%")
objExcel.Application.Visible = False

Call SetNumberFormat(objWorkbook, 1)

objWorkbook.Save
objWorkbook.Close True
objExcel.Quit

Sub SetNumberFormat(objWorkbook, index)
	Set objSheet = objWorkbook.Sheets(index)
	Set dataAll = objSheet.UsedRange
	dataAll.Borders.LineStyle = True
	dataAll.AutoFilter
End Sub

プロシージャを分割していると、複数のシートに同じ設定をする場合に便利です。実際そうしています。

あとがき

いちいちExcelを開きなおしていますけど、こうした方が記述がシンプルかなあと思ってのことで、既存のブックを使ってみたいなら調べてみてはどうでしょうか。
あと、今回、Application.Visible = Falseにしています。最初は表示した状態で試し、問題ないことを確認してから非表示にしています。非表示のまま時間がかかる処理をしていると逆に気になるので、その場合は表示していた方が良いかなとも思うのでこの辺もお好みで。

Discussion