SE の雑記

先日開催された、緊急開催！Windows 10なら無料で出来るPower Automate Desktop勉強会！ に参加させていただいた際に、チャット欄で SQL を使用した Excel の操作というコメントがありました。

Power Automate Desktop (PAD) には、データベースについての次のようなアクションがあります。

SQL Server に対してのデータベース接続は試していたのですが、それ以外のデータベース接続については試したことが無かったので、この機会にデータベースのアクションを使用した Excel の操作について試してみました。

PAD には、Excel を操作するためのアクションがいくつか提供されており、本投稿のように、データベースのアクションを使用しなくても Excel の操作は可能です。

Excel の操作については、Excel がインストールされていないと使用することはできませんが、データベースのアクションを使用した Excel の操作については、Excel がインストールされていない環境でも利用することが可能です。

Microsoft Access データベース エンジン 2016 再頒布可能コンポーネント のインストール

PAD のデータベースのアクションですが、OLEDB 経由 (System.Data.OleDb) で、各データソースにアクセスが行われます。
そのため、Excel にアクセスを行うためには、Excel に対応した OLEDB プロバイダーをインストールする必要があります。

Excel に対応したOLEDB プロバイダーですが、Microsoft Access データベース エンジン 2016 再頒布可能コンポーネント をインストールすることで追加を行うことができます。

再頒布可能コンポーネントをダウンロードしてインストールをした後に PAD を起動すると、「SQL 接続を開く」のアクションで選択可能なプロバイダーとして「Microsoft Office 16.0 Access Database Engine OLEDB Provider」が選択できるようになります。

このプロバイダーを使用したデータベース接続を行うことで、Excel の内容を「データベース」のアクションを使用して操作をすることができるようになります。

データベースアクションを使用した Excel の操作

それではデータベースアクションを使用して、Excel を操作してみたいと思います。

今回は、「Sheet1」というシートに入力された次のようなデータを使用してみます。

今回は、上記の Excel を PAD を実行する環境の「E:\Work\Excel.xlsx」として保存して、データベースアクションで操作を行ってみたいと思います。

一つ一つのアクションの説明を書くのは大変なので、フローの全体とスクリプト (script.robin) の内容を最初に記載しておきたいと思います。

Database.Connect ConnectionString: $'''Provider=Microsoft.ACE.OLEDB.16.0;Data Source=E:\\Work\\Excel.xlsx;Persist Security Info=False;Extended Properties=\"Excel 12.0;HDR=YES\";''' Connection=> SQLConnection
Database.Execute Connection: SQLConnection Statement: $'''Insert Into [Sheet1$] VALUES(2,3,4)''' Timeout: 30 Result=> QueryResult
Database.Execute Connection: SQLConnection Statement: $'''SELECT * FROM [Sheet1$] ''' Timeout: 30 Result=> QueryResult
LOOP FOREACH CurrentItem IN QueryResult
    IF CurrentItem[0] = 1 THEN
        Display.ShowMessage Message: CurrentItem['C1'] Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
    END
END
Database.Execute Connection: SQLConnection Statement: $'''UPDATE [Sheet1$]  SET C2 = 100 WHERE C1 = 2''' Timeout: 30 Result=> QueryResult
Database.Close Connection: SQLConnection

今回は、UI オートメーションは使用していませんので、スクリプトの内容をコピーして、新しいフローに貼り付けていただければ、上記のフローを再現することができるかと思います。

本投稿ではポイントを軽く紹介しておきたいと思います。

SQL 接続を開く

SQL 接続を開く際には、OLEDB の接続文字列を使用する必要があります。

Excel を開きたい場合は、上記の内容をベースにすれば対応できるかと。

接続文字列の内容ですが、私は connectionstrings.com の内容を参考にさせていただいており、今回のケースであれば Microsoft ACE OLEDB 12.0 connection strings の情報を参考にすることができます。

これは古いバージョン (Office 2007) の接続文字列となりますが、「Microsoft.ACE.OLEDB.12.0」を「Microsoft.ACE.OLEDB.16.0」に変更し、「Extended Properties="Excel 8.0"」を「Extended Properties="Excel 12.0"」に修正すれば動作するかと。

Extended Properties で「HDR=YES」を指定しておけば、1 行目がヘッダー行として使用されます。

SQL ステートメントの実行

使用可能な SQL ステートメントですが「SELECT」「INSERT」「UPDATE」あたりになり、「DELETE」は使用することができません。

シート名については「シート名$」で設定する必要があり「Sheet1」というシートに対して SELECT を行うのであれば、

SELECT * FROM [Sheet1$] 

として実行する必要があります。

実行結果ですが、リストの変数に格納され、For each で行単位に処理を行うことができます。

foreach で繰り返す場合は、「%CurrentItem[‘C1’]% 」「%CurrentItem[0]% 」というように要素名や要素番号でアクセスすることができます。

リストの操作方法については、高度なデータ型 から学習することが可能です。

変数と % 表記を使用する も参考になりますので合わせてこちらを参照すると良いかと。

SQL 接続を閉じる

更新系のステートメント (INSERT / UPDATE) を実行した場合、SQL 接続を閉じるを行わないと、更新内容が反映されませんので、接続を開いたら、必ず閉じるアクションを入れてください。

閉じたタイミングで実際の Excel のファイルに対して反映が行われます。

最後に

Microsoft Access データベース エンジン 2016 再頒布可能コンポーネント ダウンロードページの詳細には次のように記載が行われています。

Excel をデータベースで操作するときの使用目的については少し気を付ける必要があるかと思いますが、いくつかのケースで利用することができるのではないでしょうか

このダウンロードを実行すると、Microsoft Office Access (*.mdb および *.accdb) ファイルや Microsoft Office Excel (*.xls、*.xlsx、*.xlsb) ファイルなどの既存の Microsoft Office ファイルと、Microsoft SQL Server などの他のデータ ソースとの間のデータ転送を簡単に行うためのコンポーネントのセットがインストールされます。既存のテキスト ファイルへの接続もサポートされています。インストールされた ODBC ドライバーおよび OLEDB ドライバーは、アプリケーション開発者が Office ファイル形式に対応したアプリケーションを開発する際に利用できます。

Access データベース エンジン 2016 再頒布可能コンポーネントを以下の目的で使用することはできません。

1. Jet の全面的な代替としての使用 (Jet の全面的な代替が必要な場合は、SQL Server Express Edition が必要です)。
2. サーバー側アプリケーション内での Jet OLEDB プロバイダーとしての使用。
3. 一般的なワード プロセッサ、スプレッドシート、データベース管理システムとしての使用 (つまり、ファイル作成の手段としての使用)。(Microsoft Office でサポートされているファイルは、Microsoft Office または Office Automation を使用して作成できます)。
4. システム サービスまたはサーバー側プログラム (コードがシステム アカウントの下で実行されるもの、複数のユーザー ID を同時に処理するもの、高度に再入可能で動作が不安定になるもの) による使用。これには、ユーザーがログインしていないときにタスク スケジューラーから実行されるプログラムや、ASP.NET などのサーバー側 Web アプリケーションから呼び出されるプログラム、COM+ サービスの元で実行される分散コンポーネントなどがあります。

RPA は「手段」の一つであり、「目的」ではないかと思います。

Excel を使用した操作の方がシンプルかと思いますが、データベース経由で使用することができることを知っていると、操作方法に汎用性を持たせることができるケースもあるのではないでしょうか？

自分が行いたい内容を実現するための手法として、操作を行うためのいくつかの方法を把握しておくことが重要なのではないでしょうか。