Zenn
🪄

Power Automate Desktop CSVをExcelに転記 Power Query編

2023/11/07に公開
Excel
VBA
Power Automate
tech

Power Queryを使ってみる

はじめに

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

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

Power QueryとVBAで良いんじゃないかな。と思っちゃいました。最早PAD関係ないんですけど。
一応、ファイルをSharePointからダウンロードしたりアップロードする部分で……。

データの取り込み

ということで、やってみます。ダミーデータを用意しました。
Excelを起動して操作していきます。

  • データ > テキストまたはCSVから
    データの変換
  • 列の選択
    保持したい項目を選択します。
    「氏名、氏名（ひらがな）、年齢、生年月日、メールアドレス」にしてみました。
  • 新しいソース > ファイル > テキスト/CSV
    同じ手順でのこりも行う

    必要なデータを読み込んだのでまとめます。
  • クエリの追加 > クエリを新規クエリとして追加
  • 並べたい順に追加します。 (あとで編集可能)
  • 追加できました。
    式を編集すると並べ替えできます。
  • 閉じて読み込みます。
  • 右クリック > プロパティ
  • クエリ プロパティ
    PADでこのファイルを開いたときに、データの更新が終わってから後続処理を行いたいので
    バックグラウンドで更新する：オフ

テンプレートファイルとして使いたいので、データを残さないようにします。
☑ ファイルを開くときにデータを更新する
☑ ブックを保存する前に外部データ範囲からデータを削除する

  • 保存しておしまい
    ここまでだったらxlsxで保存になるんですけど、今回は新規シートにこの表を転記して
    見た目を整えて保存するところまで行いたいと思います。コードを書いてxlsmで保存します。

VBAでクエリを更新、書式設定、出力

PADで開いた場合は自動更新されないので、VBAを使ってデータを更新します。

  • クエリを指定して更新する場合は、Connections("クエリ - hoge").Refresh
  • すべてのクエリを更新する場合は、RefreshAll

バックグラウンド更新が終わってから、次の処理を行うのをVBAで実現する方法を調べると…………ちょっと面倒。なので、素直にブックから、クエリのプロパティをバックグラウンド更新オフにするのが簡単で良いかなと思います。こうしておくと、以下のコードでもちゃんと更新を待ってくれます。

VBA
Option Explicit

Sub 出力()
    Dim 保存名 As String, 日付 As String
    日付 = Format(Now, "yyMM")
    保存名 = "fileName_" & 日付 & ".xlsx"
    Call 追加("hogehoge", 保存名)
End Sub

Sub 追加(newSheet As String, newFile As String, _
	Optional isRefresh As Boolean = False)
    Dim origBook As Workbook, origSheet As Worksheet
    Set origBook = ThisWorkbook
    
    'ブックを開いたときに自動更新されるので通常は不要
    'ただし、PADで開いた場合は更新されないので明示的に更新する必要がある
    If isRefresh Then
        origBook.Connections("クエリ - 一覧").Refresh
    End If
    
    Dim destBook As Workbook, destSheet As Worksheet
    Set destBook = Workbooks.Add

    Set origSheet = origBook.Worksheets(1)
    Set destSheet = destBook.Worksheets(1)
    Call 貼付(origSheet, destSheet, newSheet)
    
    Application.DisplayAlerts = False
    destBook.SaveAs origBook.Path & "\" & newFile
    Application.DisplayAlerts = True

    destBook.Close True
End Sub

Sub 貼付(origSheet As Worksheet, destSheet As Worksheet, sheetName As String)
    Dim row As Long, col As Long
    row = origSheet.UsedRange.Rows.Count
    col = origSheet.UsedRange.Columns.Count
    
    With destSheet.Range("A1").Resize(row, col)
        .Columns("A").NumberFormatLocal = "@"
        .Value = origSheet.UsedRange.Value
        .Cells.EntireColumn.AutoFit
        .Borders.LineStyle = True
        .AutoFilter
    End With
        destSheet.Name = sheetName
End Sub

PAD

PADからはこのファイルを開いてマクロを実行するだけ。

PAD
FUNCTION Main_copy GLOBAL
    SET ExcelFile TO $'''C:\\Sample\\Book1.xlsm'''
    SET NewFile TO $'''Book2.xlsx'''
    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: ExcelFile Visible: True ReadOnly: False Instance=> ExcelInstance
    Excel.RunMacro Instance: ExcelInstance Macro: $'''追加;%NewSheet%;%NewFile%;%True%'''
    Excel.CloseExcel.Close Instance: ExcelInstance
END FUNCTION

データの追加が出来ました。

おまけ

マクロブックにして保存しておけば直接ファイルを開いて実行することもできて、一石二鳥で良いかなあと思うのですが、ついでなのでVBScriptで実行する方法も作ってみました。

VBScript
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("%ExcelFile%")
objExcel.Application.Visible = True

objWorkbook.Connections("クエリ - 一覧").Refresh

Set destWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
Set destWorksheet = destWorkbook.Worksheets(1)
Call DoPaste(objWorksheet, destWorksheet)
    
objExcel.DisplayAlerts = False
destWorkbook.SaveAs objWorkbook.Path & "\" & "%NewFile%"
objExcel.DisplayAlerts = True

destWorkbook.Close True
objWorkbook.Close False
objExcel.Quit

Sub DoPaste(origSheet, destSheet)
    row = origSheet.UsedRange.Rows.Count
    col = origSheet.UsedRange.Columns.Count
    
    With destSheet.Range("A1").Resize(row, col)
        .Columns("A").NumberFormatLocal = "@"
        .Value = origSheet.UsedRange.Value
        .Cells.EntireColumn.AutoFit
        .Borders.LineStyle = True
        .AutoFilter
    End With
        destSheet.Name = "%NewSheet%"
End Sub

なんとなく、拡張子で分岐してxlsxで開いた場合はVBScript実行するようにしてみました。

PAD
FUNCTION Main_copy GLOBAL
    **REGION 仮変数
    Folder.GetSpecialFolder SpecialFolder: Folder.SpecialFolder.Personal SpecialFolderPath=> Documents
    SET ExcelFile TO $'''%Documents%\\Sample\\Book1.xlsx'''
    SET NewFile TO $'''Book2.xlsx'''
    SET NewSheet TO $'''list'''
    **ENDREGION
    File.GetPathPart File: ExcelFile Extension=> FileExtension
    IF FileExtension = $'''xlsm''' THEN
        Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: ExcelFile Visible: True ReadOnly: False Instance=> ExcelInstance
        Excel.RunMacro Instance: ExcelInstance Macro: $'''追加;%NewSheet%;%NewFile%;%True%'''
        Excel.CloseExcel.Close Instance: ExcelInstance
    ELSE
        @@copilotGeneratedAction: 'False'
Scripting.RunVBScript.RunVBScript VBScriptCode: $'''Set objExcel = CreateObject(\"Excel.Application\")
Set objWorkbook = objExcel.Workbooks.Open(\"%ExcelFile%\")
objExcel.Application.Visible = True

objWorkbook.Connections(\"クエリ - 一覧\").Refresh

Set destWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
Set destWorksheet = destWorkbook.Worksheets(1)
Call DoPaste(objWorksheet, destWorksheet)
    
objExcel.DisplayAlerts = False
destWorkbook.SaveAs objWorkbook.Path & \"\\\" & \"%NewFile%\"
objExcel.DisplayAlerts = True

destWorkbook.Close True
objWorkbook.Close False
objExcel.Quit

Sub DoPaste(origSheet, destSheet)
    row = origSheet.UsedRange.Rows.Count
    col = origSheet.UsedRange.Columns.Count
    
    With destSheet.Range(\"A1\").Resize(row, col)
        .Columns(\"A\").NumberFormatLocal = \"@\"
        .Value = origSheet.UsedRange.Value
        .Cells.EntireColumn.AutoFit
        .Borders.LineStyle = True
        .AutoFilter
    End With
        destSheet.Name = \"%NewSheet%\"
End Sub'''
    END
END FUNCTION

PAD Sample

あとがき

今さらですけど、今回は一つずつファイルを読み込んで追加したけど、並び順を気にしないならフォルダーから読み込みで「データの結合と変換」をした方が良いような気がしました……。

Discussion