Power Automate DesktopとVBAを掛け合わせたらすごく時短になった！
【1/2追記】😊🎍
PADでメール送信時、本文にリンクを設定する方法！
こんにちは！
小売業の仕入れ部門で働き、日々数値と時間に追われているものです💦
私が働いている部署ではExcelとOutlookをとても多く使い、かつ実施内容は毎回同じという業務が多いです。
本当は必要な情報なんだけど、データを加工するのが面倒でやっていないということもあります。
最近Power Automate Desktopを学びました。
作業を自動化してくれるだなんて、、、これを使ってデータ加工をなんとか簡素化したい！！！😲
今回やりたいこと
Excelのデータ更新、更新後通知メールを送信する。
・ファイルAのファイル名先頭2桁と先頭が一致するフォルダBを検索する
・ファイルAの指定した列のデータを、検索したフォルダB内にある複数のファイルC～Gに転記する。
・Outlookでデータが更新されたことを送信する。
完成品！！
PowerAutomateの実行ボタンを押すと、、、
フォルダB内にある複数のファイルC～Gのデータが更新され、
完了メールが送信されました！
今まで・・・所要時間約5分
完成品・・・所要時間約10秒！！
10秒ですよ、10秒！しかも1クリック！！
Power AutomateかVBAか
実は最初はVlookup的な機能もPowerAutomateで実施しました。
PowerAutomateにはVlookup機能は無いため（探した限り）、かわりにLoopアクションで実施しましたが、それが非常に時間がかかったのです、、、
ExcelといえばVBAが得意ではないかと思い、結果としてPowerAutomateとVBAを掛け合わせたものにしました。
参考までにLoopを使用したフローはこちら
Excelの処理データが多いものはLoopアクションには不向きなようです。。
（今回実施したフォルダの検索は除いています）
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''転記先ファイルパス名''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:データ元ファイルパス名''' Visible: True ReadOnly: False Instance=> ExcelInstance2
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.GetFirstFreeColumnRow Instance: ExcelInstance2 FirstFreeColumn=> FirstFreeColumn2 FirstFreeRow=> FirstFreeRow2
Excel.InsertColumn Instance: ExcelInstance Column: 34
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance2 StartColumn: 1 StartRow: 6 EndColumn: 2 EndRow: FirstFreeRow2 ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelData
LOOP Row FROM 5 TO FirstFreeRow STEP 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: $'''=IFERROR(VLOOKUP(AA%Row%,[41w契約残まとめ23.12.15.xlsx]スタイルJAN!$A$5:$B$440,2,0),0)''' Column: $'''AH''' Row: Row
END
それぞれの主なメリット
Power Automate
・プログラミング無しで自動化ロボット作成可能
・サービス間連携に強く、コネクタの種類も豊富
VBA
・大量データを高速に処理
・環境構築が不要
デジタルではツールの強みを生かして実装することが実現化する近道ですね！そのためにも引き出しは持っておきたいと思います。
使用したツール
Power Automate Desktop
VBA
Outlook
ChatGPT
事前準備
フォルダ、Excelファイルの作成
・ファイルAにはVLOOKUPで参照したいデータが入っています。
A列に検索値と一致する値、B列に返す数値。
ファイル名称は●●w契約残管理表。
・ファイルAにマクロを保存します（マクロ詳細はのちほど）。
・検索するフォルダ、サブフォルダ、ファイルの作成
今回は「子供」→「●●w」→複数ファイルとしています。
マクロの作成
コードはChatGPTに聞きましたが、どうしてもほしい結果のものが得られず、、、
社内の詳しい方に教えてもらい修正しました。
直接教えてもらえるのは本当に助かります。
出来たコードをChatGPTに逆質問してみました
このVBAコードは、特定のフォルダ構造内のExcelファイルを処理し、特定のシートに対して一連の操作を実行します。
まず、Const myDir As Stringで指定されたフォルダパスから始めて、指定された条件に基づいてフォルダを探索します。この条件は、マクロを実行しているExcelファイルの名前の先頭2桁と一致するフォルダを探します。
対象のフォルダ内の各サブフォルダに対して、条件に一致するものが見つかると、そのフォルダ内のExcelファイルを処理します。これらのExcelファイルは.xlsxまたは.xlsの形式であり、処理の対象となります。
各Excelファイルは開かれ、特定のシート("売変ツール")に対して以下の操作が行われます：
列を挿入し、特定の列にVLOOKUP関数を挿入します。この関数は、指定された条件に基づいて他のセルからデータを検索し、必要な情報を取得します。
データの計算と数式を値に変換します。
エラー処理が追加され、特定のエラーがあればそれをクリアします。
また、別の列を基準に降順で並び替えます。
最後に、各ファイルが保存されて閉じられ、指定されたフォルダ内のファイル操作が終了すると、処理は終了します。
これにより、特定の条件に基づいて指定されたフォルダ内のExcelファイルが処理され、特定のシートに対して一連の操作が実行されます。
コード詳細はこちら
Option Explicit
Const myDir As String = "（フォルダBパス名）"
Public Sub ProcessFilesInFolders()
Dim objFSO As Object
Dim objFolderParent As Object
Dim objFolderChild As Object
Dim objFile As Object
Dim wbA As Workbook
Dim wsA As Worksheet
Dim lookupRange As String
Dim lookupValue As String
Dim lastRow As Long
Dim folderParentPath As String
Dim macroFilePrefix As String
' マクロを保存するExcelファイルのファイル名の先頭2桁を取得
macroFilePrefix = Left(ThisWorkbook.Name, 2)
' FileSystemObjectを作成
Set objFSO = CreateObject("Scripting.FileSystemObject")
' フォルダBのパスを設定
folderParentPath = myDir
' フォルダ「子供」を取得
Set objFolderParent = objFSO.GetFolder(folderParentPath)
' フォルダ「子供」内の各サブフォルダを検索して条件に合致するフォルダを探す
For Each objFolderChild In objFolderParent.SubFolders
' フォルダ名にマクロを保存するExcelファイルの先頭2桁が含まれるかチェック
If InStr(objFolderChild.Name, macroFilePrefix) = 1 Then
' マッチするフォルダが見つかった場合の処理
For Each objFile In objFolderChild.Files
' 対象のファイルがExcelファイルかつ拡張子が.xlsxの場合のみ処理
If objFSO.GetExtensionName(objFile.Path) = "xlsx" Or objFSO.GetExtensionName(objFile.Path) = "xls" Then
' Excelファイルを開く
Set wbA = Workbooks.Open(objFile.Path)
Set wsA = wbA.Sheets("売変ツール")
' マクロを保存するExcelファイルのパスと範囲を設定
' lookupRange = "'" & ThisWorkbook.FullName & "'!$A$4:$B$1000"
' lookupValue = "=AA5" ' VLOOKUP関数の検索値を指定
' 対象のシートにVLOOKUP関数を挿入
lastRow = wsA.Cells(wsA.Rows.Count, "A").End(xlUp).Row
'マクロのファイルでスタイルJANが一番左にあるとして Worksheets(1)
lookupRange = ThisWorkbook.Worksheets(1).Range("A4:B1000").Address(True, True, xlA1, True) ' 対象範囲のアドレスを取得
lookupValue = "AA5" ' VLOOKUP関数の検索値を指定
'AH列を挿入
wsA.Columns("AH:AH").Insert Shift:=xlToRight
With wsA.Range("AH5:AH" & lastRow)
'見出し
.Offset(-1, 0).Resize(1, 1).Value = "契約残数"
'書式文字列を標準に変更
.NumberFormatLocal = "G/標準"
' VLOOKUP関数をセルに挿入
.Formula = "=VLOOKUP(" & lookupValue & "," & lookupRange & ",2,0)"
'計算
.Calculate
'数式VLOOKUPを値に変換
.Value = .Value
'#N/Aをクリア。エラーが見つからないときにエラーになるので、エラー回避をいれる
On Error Resume Next
.SpecialCells(xlCellTypeConstants, 16).ClearContents
On Error GoTo 0
End With
'売数AD列 降順で並び替え
wsA.Sort.SortFields.Clear
wsA.Sort.SortFields.Add2 Key:=wsA.Range("AD5"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With wsA.Sort
.SetRange wsA.Range("A5:CL" & lastRow)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'画面のスクロールと、カーソル位置を直す
Application.Goto wsA.Range("A1"), True
' ファイルを保存して閉じる
wbA.Close SaveChanges:=True
Set wbA = Nothing
Set wsA = Nothing
End If
Next objFile
' 見つかったフォルダ内のファイルに対する操作が終了したらループを抜ける
Exit For
End If
Next objFolderChild
' 解放
Set objFSO = Nothing
Set objFolderParent = Nothing
Set objFolderChild = Nothing
Set objFile = Nothing
End Sub
Power Automate Desktopのフロー
このようなフローにしました。
フローのソースコードはこちら
Excel.LaunchExcel.LaunchAndOpen Path: $'''ファイルパス名''' Visible: True ReadOnly: False LoadAddInsAndMacros: True Instance=> ExcelInstance
Excel.RunMacro Instance: ExcelInstance Macro: $'''ProcessFilesInFolders'''
Outlook.Launch Instance=> OutlookInstance
Outlook.SendEmailThroughOutlook.SendEmail Instance: OutlookInstance Account: $'''送信元メールアドレス''' SendTo: $'''宛先メールアドレス''' Subject: $'''BOX売価カレンダー 契約残入りを更新しました''' Body: $'''日々の業務大変お疲れさまです。<br><br>
BOX売価カレンダー 契約残入りを更新しましたのでご連絡いたします。<br><br>
以上、よろしくお願いいたします。<br><br>
<a href=\"ファイルパス名">リンク先はこちら</a>
''' IsBodyHtml: True
Excelの起動
ドキュメントパスのファイルの選択から指定するファイルを選択。
（直接入力でも可）
Excelマクロの起動
マクロが保存されているインスタンスを選択し、マクロを記入。
記入するのはマクロ名（マクロが保存されているファイルをチェック）
Outlookを起動します
下記ののように設定します
Outlookからのメール メッセージの送信【1/2追記箇所！！】
アカウントに送る側のメールアドレス、宛先に送りたいメールアドレスを記入します。
本文の中にリンクを設定したい場合
・下記の通りタグを記述
<a href="リンク先のURL">リンクさせたい文字列</a>
・本文はHTMLです、を有効に
作ってみて
マクロで大分時間を費やしてしまいましたが、、、PowerAutomateの方は感覚的に進めることが出来てとても作りやすいと思いました！
またとっても作業的に早く簡単に出来るので早速周りの方に使ってもらいました。
【私】
今までこのデータって情報が不十分でしたよね、、。
このデータを加えました。
しかもソフトを使って数秒で完成します！
ちょっと動き見てもらえますか？
（Power Automate 実行）
【バイヤーAさん】
すごーい！こんなに早く出来るんだね！
このデータ加工って作業が面倒だからやったりやってなかったりだった、、。
でも重要な数値だからこれがあって、しかもすぐ出来るってすごく助かる！
あとは通知のメール本文にファイル保存先のリンク貼ってくれないかな？
もっとありがたい！
（→後日探して修正しました！）
他のグループもこれあるといいよね～
（→確かに。他のグループは管理フォーマットや保存場所が違うのでまた作成するのに時間がかかる、、、いや、一緒にしてしまえばいいのか？？？）
【パートＢさん】
（実際この元データを毎週作成してくれる方）
ファイル名は注意が必要ですね。
ファイルを間違って作成してしまわないか心配です、、
（→フォルダ内は間違わないように整理が必要、、💦）
うーん、マクロがいろいろあって覚えるのが大変だし、作業手順が変わるのが正直ストレスです、、、
いかに使ってもらう人の手間を減らすか。
そのためにはデータの保存方法など今までとは違う考え方も必要かもしれません。でも一人ではできないことだから難しい、、。
フィードバッグをもらって改良して、またフィードバッグをもらって改良して、一人でも多くの人に使ってもらうよう続けて広げていこうと思います。
最後まで読んでいただきありがとうございました🤗