PowerAutomateDesktopをExcelVBAと連携

PowerAutomateDesktopとExcelの連携の有効利用について

PowerAutomateDesktopはExcelを操作するアクションが複数あります
これらはそのアクション名を見ればおおよそ操作方法については分かるとは思います
Automate自体は処理速度がそれほど早いものでは無いので、VBAで実行できることであればそちらで行う方が圧倒的に早く処理できます

しかし、記事にあるようにAutomateと連携させることで今までVBAだけでは少し難しい処理が可能となります
・ユーザーフォームの操作を自動化
・IE以外のWebブラウザでの処理化

これらの処理はVBA単体で行うことを考えるとかなりハードルの高いものです
ですが、Automateと連携させることで驚くほど簡単に実装出来ます

VBA処理をAutomateから実行

まずAutomateからVBA処理の実行の方法を確認しておきます
操作方法は難しくありませんが、VBA処理の呼び出しで最も最初に注意しておく必要があるのが引数に引き渡せるのが「テキスト型」のみになっている点です
これは処理をすでに作成している場合に引数を設定している場合は要注意です
まあ、あまりオブジェクト等の引き渡しをトリガーになる処理に入れたりは無いとは思いますが…
VBA側で工夫すればテキスト型に限定されていたとしても、だいたい何とかなるとは思いますのでここは仕様と割り切って対応が必要な部分です

Excelのインスタンスを作成するアクション

まずは処理の作成されたExcelブックを開く必要があります
「Excelの起動」アクションから簡単に実行できます

アクションの設定画面

「Excelの起動」は次のドキュメントを開くに設定します
「ドキュメントパス」は絶対パスでファイルを指定します
「読み取り専用として開く」はオンにします
もちろん処理の内容を保存する必要がある場合はオフのままにしておく必要があります
ただ通常のExcelの開く処理と違い、すでに開かれているブックを開こうとした場合に注意のメッセージが表示されずに読み取り専用で起動されます
読み取り専用で開くことを前提としておくことで処理の安定化を図れます

残りの項目は初期設定のままで問題ありません、インスタンスの表示に関しては処理次第で変更しても良いです

プロシージャ名と引数を指定して処理を実行するアクション

処理の実行には「Excelマクロの実行」アクションを利用します

実行する処理

まずこちらが今回実行する処理です
引数に指定された文字列をメッセージ表示する処理です
分かりやすくするために引数を2つ指定しています

アクションの設定画面

アクションの設定画面の1つ目のインスタンスは先ほどExcelを開くアクションで生成された変数を指定します

「マクロ」の設定ですが、「プロシージャ名;引数1;引数2;…」のような形式で全て文字列のまま入力を行います
最初にプロシージャ名を入力しますが、プロシージャ名のみだけの指定となっていますのでモジュールは指定の必要がありません
と、いうより実際にはプロジェクト内全てを参照しているので別のモジュールに同名のプロシージャがあった場合は下記画像のエラーが発生するので注意してください

同名プロシージャが存在しているとエラーになる

とはいえ、別のモジュールでVBA上はさほど問題にならないとはいえ同じ名前のプロシージャが存在するというのはちょっと考えモノですね
動けばええやんという考えの自分ですがさすがにそれは是正した方が良いと思います

少し脱線しましたが、プロシージャ名の後に「;」(半角セミコロン)で引数を区切って入力します、複数ある場合も同形式でそのまま入力していきます

ちなみに、Automate側で数値型の変数を指定したとしてもVBAは文字列として受け取っているので問題が無いので、「”」で囲んだりする必要はありません

実行時の状態

実際に実行してみると、ブックが起動してプロシージャが実行されます
メッセージも渡した文字列がしっかり受け取っていることが分かります

ちなみに、このアクションはVBA処理が終了するまでアクションが停止する点は注意が必要です
もし、Automateで複数の処理を連続実行させたい場合はメッセージ表示などの処理は無くしておいた方が良いですね

ブックを閉じるアクション

蛇足ですが、開いたブックを閉じるには「Excelを閉じる」アクションを実行します
設定項目も生成されたExcelインスタンスの変数を指定するだけですね
ここは実際に操作してみると難しい場面ではないと思います

ユーザーフォームの操作を自動化

ユーザーフォームの操作の自動化ですが、この処理はVBAでは実装はめちゃくちゃに難しいと思います
そもそもVBAにはマウス操作すら行うことが出来ませんし、入力にしてもSendKeysメソッドでシビアなタイミング管理で実行する必要があります

そもそもこのユーザーフォームの操作を自動化する場面自体がほぼ無いかもしれません
ただ入力内容が転記レベルの単調なものであったり、フォームの作成を行ったのが自分では無くさらにその処理の全貌があまり定かではない場合が考えられます

とはいえ、基本的にユーザーフォームの操作自体も全てVBAで処理作成されているわけですから上記の様に処理の全貌が分かっていれば何とかなるかもしれませんが
やはりクリックイベントやチェンジイベントなどで連鎖的に処理が行われるのはユーザーフォームでは当たり前に行われる処理です
それらを全て単純にCallステートメントで呼び出すだけで動くとも限りません

と、前置きが長くなりましたが要はVBAではユーザーフォームの操作を行うのが困難ということです
ですが、Automateでは通常のウィンドウ同様にユーザーフォームの各コントロールを操作が可能です

コントロールの追加

まずはユーザーフォームの操作したいコントロールを登録します
Automateのウィンドウ左にある「UI要素」ボタンをクリックして、表示された画面から「UI要素の追加」ボタンをクリックします

追加の画面

追跡セッションという画面が表示されて、ユーザーフォーム上にマウスを移動させると認識するコントロールに赤枠が表示されます
この赤枠の表示された状態でCtrl+左クリックをすることで、追跡セッション画面に登録されます(画像の状態)
登録出来たら完了ボタンをクリックします

コントロールが追加された状態

これでコントロールの登録が出来ました
同じ要領で必要なコントロールを追加してください
この作業自体は後でアクションの追加時点でも可能なので、ここで追加しなくても都度追加しても大丈夫です

数が少ないうちは気になりませんが、UI要素の数が多くなると管理が大変になりますので各要素の右クリックメニューから名前の変更が可能なので多くなりそうなときは活用してください

登録内容

今回は3つのコントロールを登録しました
閉じるボタンについては作成出来るコントロールではありませんが、ウィンドウ操作が可能なためこれも自動化の対象に出来ます

今回の作成する操作
・テキストボックスに「test」と入力する
・クリアボタンをクリックしてテキストボックスの内容を消去
・フォームの閉じるボタンをクリックしてフォームを閉じる
この3つの操作をAutomateで自動操作してみます

使用するアクション

使用するアクションは2つで、テキスト入力とボタンのクリックアクションです

作成したフロー

いずれのアクションも設定自体は全く複雑なものはありません
直感的に設定可能なのでここでは割愛します

1ではテキストボックスに入力する処理を実行
2ではクリアボタンを押しています、これでテキストボックス内容が消去されます
3では閉じるボタンを押しています、これでフォームが閉じられます

実際の動きに関しては静止画では分かりづらかったので動画を作成しました
動きの気になる人は確認してみてください

フォームの自動操作

この処理において注意の必要なのが、Automateの仕様ではウィンドウのUI操作はマウスの操作も含まれるため他の作業を行いながらの処理実行は出来ません
そもそも自動化なのでしょうがないですけど

IE以外のWebブラウザでの処理化

IEの警鐘が鳴らされてから久しいですが、全くアプリの停止はありませんが
やはりIEの表示が行われないサイトであったり、IE未対応のWebシステムの出現もあり可能であればVBAでの情報収集等のWeb操作もIE以外のアプリを使えるようになると良いです

主なアプリと言えば、GoogleChromeかIEの後継であるMicrosoftEdgeなどが代表的なものと思います

しかしこれらの操作をVBAから行うには「Selenium」の導入が必要となります
ただIE操作のコードともプロパティやメソッドに違いがあったりするので
Web上からのコピペでようやくIE操作が出来たのにChromeに移行するのも大変です

そんな時にAutomateが出番となるかもしれません
もちろんVBAで操作するほどの処理速度を実現するのは不可能ですし、VBAで操作するほど細かい操作はまだAutomateは対応できません
ただこの対応に関しては、今後増加していくことは容易に想像できます

さらに、先般訳あってChromeの使用が出来なくなったのでEdgeに振り替えを行う必要があったのですがAutomateはインスタンス関係のアクションを振り替えるだけで終了しました
このメンテナンス上の優位性はすごいと感じた次第です

今回はChromeを操作して、この部品庫サイトの記事一覧のリストをExcelに取得してみたいと思います
なんせこの部品庫サイトは更新いつしたか分からんもんね

処理の流れとしては、Chromeの起動から情報の取得・Excelの起動をしてVBAに情報を引き渡してVBA処理で入力して保存終了・Chromeを終了の流れで処理化してみましょう
ちなみに、今回の記事では細かい作成要領は割愛します

まずは先にVBA処理を作成しましょう
引数にはテキスト型しか指定出来ないことは解説済みです
そして今回Automateから引き渡すテキストデータは記事一覧が改行されたテキストデータです
それを踏まえて以下のような処理を作成しました

作成した処理

今回の処理に関してはテスト処理なので画像で紹介しています
VBA処理の流れは最初に前回入力値の消去を行っています、全てA列に入力しているのでA列の入力データを消去しています
次に入力用のセルを変数に取得しています
この辺の入力処理は色々想定できるのでなんでも良いです、配列で一括入力もしようと思えば出来ますが、今回は変数セルを更新していって入力を行っていく形にしました
さすがに記事数が数万になったりすることは無いですからね、これからの人生全てを捧げても無理でしょうね、それは♪

次に引数のテキストデータを1行ごとに分割して入力するループ処理です
ここに関してはAutomateからどんなデータが引き渡されるかも事前に知っておく必要はありますが、引き渡すデータは改行されて改行コードはCrLfになっています
なのでそれをSpiritで分割して処理します

次はAutomateのフローを作成します

Web操作の開始アクション

前述しましたが、AutomateではいくつかのWebアプリを操作可能です
現状は画像の4つのアプリですね
今回はChromeを利用したいと思います

各アプリの操作前に拡張機能のインストールが必要になります

拡張機能の設定メニュー

メインメニューの「ツール」⇒「ブラウザー拡張機能」⇒任意のWebアプリを選択するとそのアプリで拡張機能のインストール画面に移行しますのでインストールを実行してください
以降はそのまま操作が可能となります

作成したフロー

今回の作成フローはこんな感じです
1はChromeを起動しています
画像のものはテスト用なのですでに起動済みのものを取得していますが処理化であれば新しく起動で良いでしょう

2行目の「Webページ上の要素の詳細を取得します」アクションで記事一覧のリストを取得しています

リスト範囲の取得

UI要素を指定してその範囲のデータを取得しますので、UI要素を追加します
画像の様にsectionタグになっている範囲がそうなのでその辺にマウスを移動させると取得可能です

取得されたデータ

Automate上での取得データは画像のようなものになっています
表示された文字列を単純なテキストデータとして取得していますね
行数値にあるように改行されたテキストデータです

3~5のアクションではこの記事前半で解説しているExcelのマクロ実行を行っています、今回の場合はデータの更新が必要になるので読み取り専用では開いていませんし、閉じるアクションでは保存するようにしています
ただ、見える必要のない処理なのでExcelのインスタンス表示はしていません

この処理ではWebアプリの操作は要素の取得のみで、Excelもインスタンスの表示が必要ない点から、処理はバックグラウンド実行が可能です
他の作業を行いながら処理を実行できるという事ですね

Automateの自動化も工夫次第でバックグラウンド処理にすることが可能なので、より有効活用が出来ると思います

処理の動きを確認出来る動画を作成しましたので動きの気になる人は確認してみてください

部品庫サイトの記事一覧のリストをExcelに取得

動画の方は動きの確認用にフローを少し編集しています
3行目のアクションをExcelの起動ではなくすでに起動済みのExcelを取得するアクションに変更してブックの閉じるアクションは無効化しています

動画の最初と最後でExcelのA列にデータが入力されていることを確認してください

そして最後にとても大切なことを1つ
この記事の例を試すことにより、部品庫サイトに高アクセスでサーバー攻撃しないでね

ワークシートを再計算する

特定のワークシートの関数を再計算させるコード

'アクティブシートを再計算
ActiveSheet.Calculate
'特定のシートを再計算
Worksheets("Sheet1").Calculate
'指定のブックの特定のシートを再計算
Workbooks("Book1.xlsx").Worksheets("Sheet1").Calculate

ワークシートの再計算を行うには「Calculate」メソッドを使用します
このコードを実行すると、アクティブシートに含まれる関数が全て再計算されます
処理中等に手動に切り替えた関数計算をコード中に更新する場合によく利用します

ワークシートオブジェクトを任意に指定することで、特定のシートだけを再計算を行うことが可能です
2つ目の例コードはワークシート名を指定することで特定のシートを対象としています

また3つ目のコードの様に、ブックから指定することで別のブックのシートを再計算させることも可能です
これを応用することで、特定のブックの全シートの再計算を行うことが出来ます
以下の記事にて解説しています

すべてのブックやセル単体で再計算を行うことも可能です
以下の記事で解説しています

特定のブックの全シートを再計算する

複数のブックを開いた状態で特定のブックのみを再計算するコード

'ループ用の変数宣言
Dim zz対象Ws As Worksheet

'特定のブックの全シートを再計算
For Each zz対象Ws In Workbooks("Book1.xlsx").Worksheets
zz対象Ws.Calculate
Next zz対象Ws

再計算を行うには対象のオブジェクトに対して「Calculate」メソッドを使用します
しかし、Workbookオブジェクトにはこのメソッドがありません
そのため、複数のブックを開いた状態で1つだけのブックを再計算するには少し工夫が必要となります
それがこの記事コードになります

このコードを利用することで、複数のブックを開いた状態であっても特定のブックのみを再計算することが可能となるので極端に時間のかかる再計算の不要なブックを除外することが可能になります

コード解説

コードの流れ自体は非常に簡単なものです
Worksheetオブジェクトをループで再計算を実行していくだけです

'ループ用の変数宣言
Dim zz対象Ws As Worksheet

まずはループで利用するためのWorksheetオブジェクトを代入する変数を宣言します
この時の型はObject型でも構いませんが、Worksheet型にしておくとコード入力の際にインテリセンスが利用できるので便利です

'特定のブックの全シートを再計算
For Each zz対象Ws In Workbooks("Book1.xlsx").Worksheets
~~~
Next zz対象Ws

次にループ処理の個所です
ForEachループによって特定のWorkbookに含まれるWorksheetを全てループします

ここで大切なこととして、Excelのシートにはオブジェクトが複数あります
主にはWorksheetとSheetです
特に後者のSheetオブジェクトはマクロの記録でよく出てきます

For Each zz対象Ws In Workbooks("Book1.xlsx").Sheets
~~~
Next zz対象Ws

このコードの様にWorksheetオブジェクトでは無く、 Sheetオブジェクトとしてもほとんどの場合問題なく動作します

このSheetオブジェクトにはグラフシートやマクロシートなどの通常Excelで使用する方眼紙シート以外のものも含まれます
そのため再計算を行う目的であれば、Worksheetオブジェクトを指定する方が良いです

そして、「Workbooks(“Book1.xlsx”)」の引数の文字列を対象とするブックの名前に変更すれば任意のブックを指定することが出来ます

zz対象Ws.Calculate

ループ内処理はWorksheetオブジェクトに対してCalculateメソッドを実行しているだけです

この様にして複数のブックを開いた状態で特定のブックのみの全シートを再計算させることが出来ます
使いどころが限定的ですが、知っていれば処理時間を短くすることも可能なので有用だと思います

全ブックまとめて再計算するほうがええねん、という方は以下の記事を確認してください

指定セル以下の行を全て削除する

指定セル以下の行全てを削除・消去するコード、見出しを除外した範囲全てを初期化する際に使用します

'2行目以降を削除
Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete
'2行目以降を消去
Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Clear

関数などで参照しているセル範囲である場合にはこちらを利用してください

データ集計を行ったり、フィルタデータの貼り付けなどで元々あるデータは削除してデータを貼り付けする処理はよくあります
この際に見出し行は削除したくない場合があります
そんな場合に使用するコードです

指定セル以下の行全体を削除する動き
コード実行時の動き

このコードを使用すると2行目以降のすべてのセルを削除しますが、1行目はそのままなので見出しを再度作成したりする必要が無くなります

ちなみに画像ではテーブル機能のデータ範囲以下全てを削除しています
テーブル機能の場合はデータが無くても、データ範囲1行目には背景色の設定が表示されますので、実際にはちゃんと削除されています

データ範囲のみを選択する場合は以下の記事で解説しています

ですがこの選択範囲はデータ行のみになるので、もし未保存の削除セルがあった場合LastCellなどでうまく最終セルが取得できない場合があったり、コピー元のセル範囲がそういったセルを作成してしまっていたりすることがあるので、なるべく例コードの様にデータ範囲を初期化する場合は全てのセル範囲を指定する方が無難です

コード解説

Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete

このコードは1行ステートメントですが、複数のプロパティを使用しているのでプロパティごとに分割して解説を行います

Range("A2").Select
コードを実行した場合

そのRangeオブジェクトの1つ目の引数はこれまたRangeオブジェクトとなっています
その引数にはA2が指定されています
なのでこの引数でSelectメソッドを実行すると画像のセルが選択されます
ここが始点のセルになるということですね
なのでここのA2を変更することで任意の行数に変更することが出来るということになります
A3にすれば3行目以降全てが削除対象となり、1・2行目はそのまま保持される事になります

Range("A" & Cells.Rows.Count).Select
コード実行後のセル選択

次の2つ目の引数にもRangeオブジェクトが指定されていますが、今度は単純にセルAddressが入力されているわけではありません

「Cells.Rows.Count」というのは、このシートに存在するセル範囲の行数を数えています
その数字の前にAが入力されているので、A列の最終行が指定されます
画像の通りで行数値は「1048576」となりA1048576セルが選択状態になっています
ここが終点セルとなります、要は以降全部ってことですね

Range(Range("A2"), Range("A" & Cells.Rows.Count)).Select
範囲選択された状態

この2つを始点と終点としてセル範囲指定をすることで、A2からA1048576までが範囲選択された状態となります
画像の様に2行目以降がすべて選択されていることが確認できます

Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Select
対象範囲が選択された状態

ここまでの対象範囲の取得ではA列だけになってしまいます
そこでこの対象範囲を最終列まで広げていきます

EntireRowプロパティは、A列だけの範囲を列方向に広げてくれます
これを使用することで画像の通りに最終列まで範囲を広げることが出来ます

Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete

最後にDeleteメソッドを使用して、その範囲を削除します
上記までで指定行範囲を取得できているので、そこに対して削除を実行します

この範囲を別のシートで関数等で参照している場合、セル参照がエラー値(#REF!)になってしまいます
その場合はDeleteメソッドではなくClearメソッドを使用することでセルの初期化を行うことが出来ます

それが例コードの2つ目のものです
単純にメソッドをClearメソッドに変更しただけですが、これを行うと書式含めすべてが初期化されるので、見た目的には削除とほぼ同等の効果です

上記に記載していますが、このメソッドではセルが削除されないため、関数などの参照を行っている場合に自動的に範囲が修正されたり「#REF!」エラーになることを防ぐことが出来ます

作り込みの凄まじい関数ファイルなどで処理を行う場合はこちらを利用しておいた方が無難です
なんせ作り込まれた関数ファイルはVBAより遥かに難解ですので

なおブックのサイズにもよりますが、こういった削除処理を行った際は処理最後にでもブックの上書き保存を行っておいた方が良いです

なぜなら、削除したセル範囲のLastCellなどはそこでしか更新されないためです
またこれを行うことで、スクロールバーの可動域も新しいデータ範囲に調整されますので、あまりにも削除前とのデータ範囲に差がある場合にはユーザーにとっても意味のある保存と言えます

ただ保存はサーバー上であったり、ブックサイズが大きいと時間がかかってしまい
待ち時間がユーザーにとってのストレスになることもあることは認識しておきましょう

パスワードを取得する汎用フロー

パスワードをExcelに保存してそれを取得するフローの作成とそのフローの外部フロー化として汎用性をあげる方法について

PowerAutomateDesktopでWebアプリやサービス等を自動化するフローは、だいたい最初に想定することかもしれません
そこで重要となってくるのがログインアカウントとパスワードの取得です
基本的にはテキスト入力のアクションのプロパティに設定しても良いのですが、パスワードが定期的に変更が必要だったり、強制的に数か月経過すると変更が必要となるものもあります

そんな時にいちいちAutomateのプロパティを編集するのも面倒です
しかも1つなら大して問題にはなりませんが、これが複数あった場合かつそれも複数のフローになっていた場合は非常に面倒です

そこで利用したいのがExcelです
Excelにアカウント名とパスワードを一覧として保存しておきます
そして必要なアカウント情報を取得しに行ってそれをAutomateに入力をしてもらいます

こうすることにより、Excelファイル1つで必要なアカウント情報を一元化できかつ編集も楽になります
またセルの位置さえ変動しなければ、フローへの影響もなく
関連するフロー全てに対して有効に出来ます

さらにこの記事中ではそのフローを別フローとして保存して実行することで、あらゆるフローから汎用的に利用できるようにしています

実際に作成してみる

パスワードファイルを開く

最初のアクション追加

最初のアクションはExcelファイルを開く操作になります
Excelファイルを直接開くには「Excelの起動」アクションから行えます
このアクションをダブルクリックします

Excelの起動アクションのプロパティ設定画面

プロパティ設定を上から順番に行います

まずは起動時の動作の「Excelの起動」ですが、ここは選択肢から「次のドキュメントを開く」を指定します
これで2つ目のドキュメントパスの指定が可能となりますので、パスワードファイルをダイアログから選択してください
これでフルパスの入力が行われます

基本的に目的に対してはこの2つの設定を行えば十分ですが、さらに以下の設定も行っておきます

「インスタンスを表示する」はオフにする
セルデータが欲しいだけなので表示する意味が無いからです
後述しますが場合によってはファイルのデータ閲覧を防ぎたい場合にも有効です

「読み取り専用として開く」はオンにする
これもインスタンスを表示しないのでどちらでも良いのですが、自動化処理の場合には編集しないならなるべくこの設定を行っている方が良いですね
自動処理というのはスケジュールやなんかでいつ発生するか分からないので、編集したいときに実行されてると迷惑です

詳細項目の「パスワード」はExcelの標準機能の読み取りパスワードの文字列です
これはこのファイルにあるデータはアカウント情報やパスワード情報という観点からファイル自体にパスワードを設定して、だれでも閲覧できるようにしたくない場合に設定して、フローで開く場合にはここでパスワードを指定することになります

前述にあるようにこの閲覧制限をしたいファイルを利用する場合はインスタンス表示(Excel表示)をしない方が良いですね

最後に保存ボタンをクリックしてアクションの作成完了です

セルの値を取得する

まずはファイルの中身を先に説明します

ファイルのデータ

このように特に見栄えは全く考慮する必要もなければ、A列にアカウント名、B列にパスワードとします
別のアカウント情報は改行して入力を行っていきます
ここは別にこれと同じでなくても、データがどこにあるか分かれば問題ないです
このデータをフローに取得してもらいます

そして取得アクションの前にもう1点設定を行っておきます
それは入出力用変数の設定です

前述しましたが、このフローは別のフローから外部呼出しを可能とすることを目的にしています
なので別のフローからどのアカウント情報が欲しいかを入力変数、取得した情報を出力変数として代入する必要があるためです

変数の設定画面

Automateの画面左側上部に変数のウィンドウがあります
もしこのウィンドウが表示されていない場合は、閉じる×ボタンのすぐ下の「{x}」というボタンをクリックすると表示されます
ちなみに{x}というのはAutomateでの変数の表現となります
他にも利用可能な場面で表示されています

そのウィンドウ上部の入出力変数の範囲から+ボタンをクリックしてさらに「入力」を選択します

入力変数の設定画面

変数の設定画面が表示されます
基本的に変更の必要も無いのですが、そのままで分かりにくい場合はそれぞれの項目を任意のものに設定を行います
ちなみに、データの種類は選んだら分かりますが数値型とかは無いのでテキストで問題ありません
そもそもAutomateではあまり変数の型は意識が必要ないです

また、選べる項目に機密テキストがありますが、これはこのフロー上でも確認が不可能になる暗号化されたものに利用します
これは別途アクションが必要になるのでまた別のお話とします

最後に作成ボタンをクリックして作成を完了させます

入力変数の作成時と同様の画面で今度は出力変数の作成を行います
これがフローで取得したアカウント名とパスワードを格納して返す変数です
ここではアカウント名とパスワードの2つの出力変数の作成を行ってください

まとめて取得してリストとして1つの変数で返すことは可能ですが
このフローの目的からするとまず間違いなく呼び出し元ではアクションがそれぞれ別のアクションで変数を利用します
連続したデータであるよりは、別々の変数の方が利用しやすいです

出力変数の作成

変数名と外部名を任意に指定して作成します
ちなみに、この変数名と外部名に関しては後でフロー呼び出しの解説で説明します

変数の作成完了した状態

画像の様に入出力変数が3つ作成された状態となれば、準備完了です

アクションの選択

実際のデータの取得アクションを作成します
アクションはExcelの起動の下にある「Excelワークシートから読み取り」を使用します
ダブルクリックすると設定画面が表示されます

アクションのプロパティ設定画面

Excelインスタンスと取得は初期上のままで問題ありません
もし違っていたら画像の様に設定してください

このアクションではアカウント名を取得したいので、先頭列は「1」を指定します

次の先頭行は先ほど作成した入力変数を指定します
変数を利用する場合は、入力ボックスの右側にある変数ボタンをクリックします
画像の様に変数の選択画面が表示されますので、そこから作成した入力変数を選択します

代入先の選択

次に詳細項目内の「セルの内容をテキストとして取得」を有効にします
しなくてもほぼ問題は無いとは思いますが、アカウント名とパスワードが数値であることは無いと思います

最後に生成された変数項目内で取得したデータの格納先を指定します
初期状態ではローカル変数になっているので、変数ボタンをクリックして出力変数を指定します

最後に保存ボタンをクリックして作成完了です

2つ目の取得アクション

同手順で2つ目のパスワードを取得するアクションを作成してください
設定は画像のような形で設定します
先頭列が「2」になっていることに注意してください

パスワードファイルを閉じる

取得が出来たらパスワードファイルは閉じます

閉じるアクションの選択画面

画像の「Excelを閉じる」を使用します
これをダブルクリックしてください

Excelを閉じるアクションのプロパティ設定画面

特に設定を変更する必要はありません
保存ボタンをクリックして作成してください

フローの全体

完成すると画像のような4ステップのフローが完成します
途中の読み取りアクションでは出力変数に代入しているので特に返し値を意識する必要はありません

完成したらフローを保存してください
このフローは入力変数である引数が必要になるので動作確認は別フローで行います

フローの動作確認

別のフローを新規に作成してください

別フローの実行アクションの選択画面

別のフローを実行するアクションはフローコントロール項目内の「Desktopフローを実行」を使用します
これをダブルクリックしてください

アクションのプロパティ設定画面

先ほど作成したフローをリストから選択します
ここでは「PasGet」を選択しています

設定完了状態

フローを選択すると自動的に同期されて、入力変数が作成されたフローであれば設定画面に自動的に表示されます
ここに任意のデータを入力します

また前述にて後ほど説明するといっていました、入出力変数の外部名ですが
ここに初期状態で表示された変数名がそれに一致する事になります
なので基本的にはどちらも同じものを利用している方が分かりやすいです

作成完了状態

アクションを作成すると画像の様に1ステップのアクションが作成され、フロー変数が自動的に作成されます

呼び出し元としてはこれだけで取得フローを実行して返し値を受け取る事ができます
これを実際に実行してみます

実行後の画面

こちらが実際に実行した後の画面です
フロー変数に指定した行のアカウント名とパスワード文字列が取得されていることが確認できます
入力変数には「2」を指定したので、Excel2行目の情報が取得されています

この様に外部フローとして汎用的に取得処理を作成することで、パスワードの変更等があった場合でもフローの修正が必要なくなります
行数を変更さえしなければですが…

サブフローよりもさらに強力に汎用性を上げることの出来る外部フローの呼び出しを有効に活用しましょう

個人用マクロブックを読み込まない

個人用マクロブックが読み込みされなくなった時の対処について

ファイルタブを選択

まずは落ち着いてExcelを起動してください
そしてファイルタブを選択します

オプションを選択

次に左側メニューの一番下のオプションをワンクリックします
焦ってダブルクリックしてしまっても問題無いので落ち着いてクリックしましょう

アドインを選択

開いたウィンドウのExcelのオプションのメニューからアドインを選択します
手の震えを無理に抑えなくても…(しつこい)

オプションを選択

表示された画面の下部にある、管理項目を確認します
ここの選択肢から画像の「使用できないアイテム」を選択して、設定ボタンをクリックします

設定の確認画面

ここに「PERSONAL.XLSB」があれば、選択して下にある有効にするボタンをクリックしてください

これで再起動すると読み込みが正常に行われるようになります
ここにない場合は、マクロブックが存在していないかもしれません
こうなったらお手上げですね…この操作で復活出来ることを祈っております

先日発生した話

個人用マクロブックとは、そのPCにおいてExcelを起動すると自動的に読み込みされる
マクロ保存用のブックの事です。
個人用と名前の通りで、個人で利用するには非常に便利な機能です。

個人用のファイルは特定の個所に保存され、初期設定の状態でマクロセキュリティでは安全な場所として登録されているため起動時のマクロセキュリティ確認も必要ありません。
特に作成用のコードを保存しておくなど、作成に役立てる目的でも自分はよく使います。

そんな便利なファイルですが、いきなり使用が不能となりました。

これはかなり愕然としました
上記にあるように自分の仕事PCの個人用マクロブックはまさにこのサイトにあるような自分の知識の蓄え場でもあるため、結構な量のコードやフォームが存在しています。
日々仕事の中で作成した処理などで閃いたことや後に残しておきたいものがそこには貯め込まれています。

これを一気に失ったのです。

何か月もかけて作ったExcelVBA処理の比では無く
マジでくじけそうになるほどの衝撃でした。
「からっぽやないかい…」
「ゴミ箱も何も入ってへんやないかい…」

正直なところ、作成したExcelVBA処理ならあきらめもつきます
所詮はこのマクロブックからコピペなどして作成したモノですから

それほどの重要なファイルが消失したと思っていました

と、いうのも
このExcelの起動時読み込みには実際にはユーザー・Excel・Officeという3つの種類があります
これのうち、利用されるのが最初のユーザーになります

それも認識が無かったため、Excelの起動フォルダを確認しに行って
ファイルが存在していないと勘違いをしていました

実際にはユーザーの読み込みフォルダ内にファイルが存在することは確認できました

ところが、ところがです
この記事のタイトルの現象に気づいたのです
そこにファイルが間違いなく存在しているのに、なぜかExcel起動で読み込みが行われない

個人用マクロブックをダブルクリックで開いてあげると、VBAが実行できます

なので、もう一生こうして自動的に読み込まれていたファイルを手動で毎回起動しないといけないんだな、と半ば諦めかけていました
なぜならファイルが無くなっていなかったのでVBAコードが残っていた事に安堵していたからです
もうとにかく生きていてさえくれれば、それでええ…
そんな気持ちでした

ですが、ふと思い出しました
Google先生に聞いたら分かるんちゃうか、と

実際すぐに解決しました
とはいっても、画像のあるサイトが無かったので焦っていた時にはそのページを見ても
ちゃう、これが原因ちゃう
と、すぐに別のことを確認してしまっていました

結果無駄な時間を過ごすことになってしまいました

とにかく、ファイルが消失してなくて良かった~
VBA処理を作ったファイルはバックアップ取っていたけど、個人用マクロブックのバックアップは取っていませんでした
これを機に個人用マクロブックもバックアップを取るようにします
みなさんも個人用マクロブックを大切にして下さい

Officeスクリプトの実行について

Web版ExcelのOfficeスクリプトの実行方法とその注意点について

OfficeスクリプトはWeb版のExcelで利用可能な機能です
またこれは組織アカウントなので個人アカウントだけでは利用できません
とはいえ、この機能を個人で利用したい場面は少ないので、問題はないと思います

それよりもタイトルにありますように、この機能のパッとしない点が実行方法が限定的である点です
何よりExcelを利用している時にこの機能で何かをしようというのは現時点では現実的では無いかもしれません

その最大の理由がイベント処理が無い点です

VBAをある程度触ってきた人には良くわかると思いますが、このイベント処理が無いというのは結構大変です
処理の開始は常に手動実行以外の方法が無いからです

しかもVBAと比べるのも土俵が違いすぎるかもしれませんが、処理速度が目で追えるレベルです
まだまだ発展途上の機能ではあるので、いずれはイベントも対応するかもしれません

が、現時点ではWeb版Excelとしては手動実行のみです
しかもこのスクリプトはブックとは別に存在しているので、ブックを渡せば処理が実行できるわけではありません
その処理ファイルが必要となります

これ自体は一長一短かとは思うので、VBAのイメージでいくと面倒に感じる部分もあるかもしれませんが、仕様と割り切って良いでしょう

あと気になるのが、所持してるスクリプトが全て表示されるので数が多くなってくるとちょっと管理が面倒です
これも何か対応を検討して欲しいな、と思ってます

自動実行(PowerAutomate)について

実行は手動のみと上記で記載しましたが、あくまでもWeb版のExcel単独でいうと
ということになります

と、いうのも
PowerAutomateから実行することが可能です

ちなみにDesktop版のPowerAutomateではスクリプトは実行できません
だってExcelがDesktop版の操作になるので

そして、このPowerAutomateからの実行では引数を渡して、返し値を受け取ることも可能です
これを応用すれば、PowerAutomate経由でPowerAppsでExcelデータを制限を受けずにデータの取得を行うことが出来ます

またPowerAutomateによりトリガーを設定することで、何かしらのイベントでスクリプトを実行することが出来ます
メールを受信したらその内容をExcelに転記したり、OneDriveにファイルが作成されたらファイル情報を転記したり等々

さらにこのスクリプトを併用することで、PowerAutomateではExcelのデータ入力はテーブル機能に対してしか行えなかったのが、任意のセルに入力することが出来ます
これが現状では最大の利用点かと思います

ただ、残念なことがあります
それはPowerAutomateでは2次元配列が作成出来ない点です
もちろん静的なものはコードの書き方で可能ですが、動的なものは作成できません

VBAでも同じですが、データの入力は配列で一括処理が早いので良く利用されます
これが引数として渡せないのです
もちろんスクリプト自体は多次元配列を引数に受け取ることも可能ですし、処理も可能です

そして、この処理時間に関して結構重要で最近ハマったのですが
PowerAutomateのアクションのタイムアウトの存在です
これがネックとなります

つまり、PowerAutomateでスクリプトを実行するアクションを実行した後に
その処理時間がタイムアウトの時間を超えてしまうと、PowerAutomate側でエラー終了となりスクリプト側でエラーは発生していないにも関わらずフローが失敗します

スクリプトをWeb版Excelで手動実行したら、エラー無く処理が完了するのに
PowerAutomateから実行するとエラーでフローが停止する

この現象に悩まされました

結果としてはタイムアウトが絡んでいることが分かったのですが
その設定を伸ばしても処理の引数が可変であった場合、どこまで保険を置くかは判断が難しいです

なので、タイムアウトの設定は変更せず
その時間内で処理が完了できるように調整する必要があります

例えば、全行の処理を実行したいのならば
スクリプト側で全行の処理にするのではなく、数百行ずつ実行する、といった処理の分割を行う必要があります
これが出来ない処理であれば、もはや現時点では仕様として対応出来ません

2000行ほどある処理を実行しようとした場合にタイムアウトとなっていたので
それを少しずつ減らして検証を行っていきました
結果として300行ほどがギリギリのラインであることが分かったので、処理は200行ずつ実行するようにしました
もちろんこれは処理内容によるので、ここに記載した数字には全く何の意味もありませんのであまり気には留めないで下さい
2000行だと毎回エラー終了するけど、200行ずつの分割実行にしたらエラー終了は無くなったという事が重要な点です

もう一つこの方法で重要なのが、返し値に処理が終端に達したことを知らせる必要があることです
その返し値になるまでPowerAutomateではループ処理を実行することになります

この辺はおいおい説明します

今回としてはWeb版Excelでは手動実行しかできない
PowerAutomateから自動実行は出来るがタイムアウトに注意する

ここを念頭において作成を進めてください

PowerAutomate Desktopについて

PowerAutomateのDesktop版のインストールについて

ついに提供開始されました
最初に書いておきますが無償提供はあくまでも無償版という形での提供になっている点には注意が必要です

正直、Web版との連携が取れないのは致命的ですが、それでもローカルな作業であれば画期的な対応が出来るようになるはずです
何よりほんの半年で無償版の提供まで行っているくらいですから、この無償版をみんなが利用するようになれば更なる機能追加も期待大です

導入も簡単です、既にWeb版で解説している管理画面からダウンロードサイトへ移動可能です

メニュー画面

作成メニューを選択して、その中から「デスクトップフロー」を選択します

起動画面

起動画面が表示されます
ここから「アプリのダウンロード」を選択してください
インストールファイルがダウンロードされます
ダウンロードファイルから、PCにインストールしてください

アカウントログイン等の初期作業を行います
こちらの作業も複雑なものはなく数ヶ所設定すれば完了です

初期表示

新しいフローを作成するには画像のボタンをクリックして作成を始めます

フローの名前設定画面

フローの名前を設定する画面が最初に表示されます
ここで名前を付けなくても、後で編集可能なためとりあえず作成をしてみます

編集画面

開いてみて分かるのは、Web版とは全く違う作成画面であることです
こちらにもなれていく必要がありますが、やはりコードの記載は全く不要で日本語で記載された各項目も難解な解読を必要としません

最初に書いた通り、トリガーの設定はありません
処理アクションを追加していきます

細かいフローの作成手順などは別の記事で解説していきますが、ここでは無償で利用できる環境を整えれれば完了です
また、このサイトでは無償版の解説を行うこととし、有償版の解説については扱いませんのでご了承ください

UNIQUE関数について

Office365とWeb版で利用可能なUNIQUE関数は重複除外リストが簡単に取得出来るやつです

Office365・Web版で利用可能な関数にUNIQUE関数というものがあります
この関数は指定した配列データの中で重複したデータを削除したデータを返す関数です
この関数を利用することで、表のデータから簡単に重複しないデータを取得できます

この重複しないデータの作成は、案外利用場面が多くあったのですが、標準機能ではデータタブ内にある重複データの削除という機能から行うことが出来ます
後述しますが、まだこちらの機能に完全に有利になってはいないので、この機能を知らない人はデータタブ内を確認してみてください

ただ、こちらは関数のためいちいちデータを転記して操作を行わなくても関数の再計算のタイミングで自動的に取得できるのは便利です(常に最新情報)

関数の書式

引数(太字は必須引数)
(配列, [列の比較], [回数指定])

「配列」は重複しないデータを作成する配列データです
連続していれば複数列を範囲指定することが出来ます

「[列の比較]」は、重複参照時に列方向を対象にするかどうかの指定です
列を対象にすると、列方向に重複しない場合はその列が取得されません
あまりExcelのデータ形式からすると使用しないかもしれません
省略時は行方向が指定されることになります

「[回数指定]」は、重複データの中でも1度しか出てこないデータだけを指定できます
1度しか出てこないデータは、配列データ内で完全に一意になっているデータという事になります
案外欲しい、この逆説のデータ取得は出来ません

実際の使用例

使用するデータベース

今回はこのデータを元に関数を使用してみます

UNIQUE(Sheet1!A2:A21)
関数で取得されたデータ

基本的な使い方として、指定データ範囲を配列データとして指定します
今回はA列のデータ範囲を指定しています

この中から重複したデータだけを削除したものが取得されています
データの総数は20個ですが、重複データが15個あるのがこれで分かりますね

=UNIQUE(Sheet1!A2:A21,,TRUE)
関数で取得されたデータ

次に完全な一意のデータのみを取得してみます
3つ目の引数に「TRUE」を指定します
指定配列内の中で、1度しか出てこなかったデータが取得されます

「kkk001」と「kkk003」が複数回出てきていたことが、前回の結果と比べると分かりますね
ここが前述の逆説のデータをとれないという点ですが、この複数回出てきているデータだけを取得する設定が無いんですね、なんでやろね

=UNIQUE(Sheet1!A2:B21)
関数で取得されたデータ

次に複数条件での重複除外データの取得を行ってみます
A列とB列の両方を配列データに指定することで、複合的な条件でリスト作成を行うことが出来ます

この場合ですと、A列とB列の両方が重複するものが除外される形になります

ただ少し残念なのが、連続した範囲でしかセル指定が行えない点です
A列とC列を参照したい場合に、そのままでは指定が行えないのですね
なので、C列のデータをB列に持ってくるような工夫が必要になります
ここが最初の方で記載した、既存の重複データの削除機能に優位になっていない点です
重複データの削除機能は飛び飛び列で条件指定を行うことが出来ます

この様に単独列の重複しないデータのリストが簡単に作成できる関数であることが分かってもらえると思います

XLOOKUP関数について

Office365とWeb版で利用可能なXLOOKUP関数はLOOKUP関数系の最終進化系です

XLOOKUP関数を利用していますでしょうか?
この関数はOffice365・Web版で利用可能な関数です

この関数は、ある程度関数を知っている人にはVLOOKUP関数等のLOOKUP関数系であることはイメージできると思います
VLOOKUPでもHLOOKUPでも無く、XLOOKUPが存在します

この関数は引数の配列内から検索して、その配列と同じ順番にあるデータを返す関数です
ただこれだけだと、あまり既存の関数と差があるとは思わないかもしれません

決定的に違うのは、前述の検索対象が「配列」であることです

これは意外に今までの関数ではあまり無い考え方になります
というのも、VLOOKUP関数等は引数に指定するデータは「セル範囲」であることが前提です
このデータの取得の行い方で全く動作と利便性が変化します

今までのLOOKUP関数は、セル範囲の中で検索を行う、見つかったセルと同じ行・列の指定した行数・列数を移動させたセルのデータを取得します
そのため、制限として「検索個所と取得個所が連続したセル範囲であること」「取得セルは検索セルより、左の列・上の行は指定できない」という大きな点がありました

そのため、LOOKUP関数を利用するために、本来別のシートやブックにあるデータを1つのシートにコピペしてデータを結合して検索可能な表を作成する必要がありました
これはセルという概念が前提であることが理由でした

しかし、XLOOKUP関数はセルではなく配列データから検索・取得を行うため
上記の制限が全くありません

つまり、「検索するデータ範囲と取得するデータ範囲が連続していなくていい」「取得するデータが検索データよりセルの位置が上でも左でもいい」ということになります
特に大きい利便性向上につながるのが、1点目の連続していない範囲でよくなった点です

要するに、同じシートである必要もなくなり、なんならブックを超えた検証を行うことも可能となりました
そのため、検証作業が非常に簡単になります

さらに地味にありがたいのが、見つからない場合の返し値を指定できる点です
LOOKUP関数系では見つからない場合エラーとなってしまうため、そのデータを参照した関数は連鎖的に全てエラー表示となってしまいます
そのため、LOOKUP関数をIFERROR関数でネストさせて見つからない場合は特定の値を返すように関数を組んでいることが多々あります

これが必要なくなりますので、作り込みによってはすさまじくネスト数を減らすことも可能となります

関数の書式

引数(太字は必須引数)
(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])

「検索値」は検索対象のデータです
これはLOOKUP関数系と差は無いと思いますので、特に解説不要と思います

「検索範囲」は検索対象の配列データです
配列データですが、基本的にExcel上ではセル範囲で指定します

「戻り範囲」は取得対象の配列データです
検索対象で見つかった配列のデータ番号と同じ順番のデータが返されます
基本的に列を指定している場合は、同じ行数のデータになります

「[見つからない場合]」は検索配列に検索対象が見つからなかった場合に返す値の設定です
数値の0を指定したり「””」(空白)を設定したり、なんでも指定できます

「[一致モード]」は検証方法です、基本的には完全一致が条件です
ですが、なんとここでワイルドカードを指定することができる様に設定できます
ワイルドカードまで対応しているなんて、なんてことをしてくれるんでしょうか
ワイルドカードが分からない人にはピンと来ないかもしれませんが、本記事では割愛します
ワイルドカードが利用可能、という点が認知できていれば十分です

「[検索モード]」は検索方向の設定です
末尾から検索したいとき程度に利用しますが、あまり頻度は高くないと思います

実際の使用例

検証を行うデータテーブル
データ検証対象

こちらのテーブルデータをもとに検証を行う場面を想定してみます
実務となると検証対象が数百件以上となることもあると思います
今回はとりあえず20件で検索を行います

このデータの中で存在するデータと、そうではないデータを検証します

作成した関数
実際に作成した関数
=XLOOKUP(B2,Sheet1!A:A,B:B,"×")

上記のような関数を作成しました
この関数では、関数のセルの左にある入力値をSheet1のA列データから検索して、見つかったデータと同じ行数のSheet2のB列から取得を行います
見つからない場合は「×」を返します

そのため、取得が行えているデータの場合は条件セルと関数セルが同じ内容が表示されます
上記の画像でも同じものが2列に並んでいることが確認できます

検証結果の画像
検証を行った状態

このまま条件の行数分に関数をオートフィルで作成を行います
この画像の入力値は前提としてマスターデータと考えてください
登録すべきデータ自体は過不足なくここのリストにあります

最初に見せたデータテーブルに、このマスターデータの内容がちゃんと存在しているかを確認するための検証作業です
この画像の様にマスターデータを元に検証を行うことで、データの検証を行えます

実際画像を確認して分かるように、「kkk012」が入力テーブルには存在していないことが分かります
これでこのデータが入力漏れ等のミスが発生していることを発見することが出来ます

XLOOKUP関数は、別のシート、別のブックも可能なのが強みだと思います
また配列データなので、セルの行数が同じでなくてもセルの個数さえ一致していれば検証と取得を行えますので、非常に柔軟にセルデータに対して対応を行えます

VBAで検証処理を作成するのも良いのですが、やはり関数が利用しやすい場合は利用するべきですね
今後、この関数がExcelを席捲していくこと間違い無しです