はじめに

私たちはSAPデータモデルを使用して、州単位の顧客別シートを毎月作成し、それをファイルシステムに保存したいと考えています。

データモデル: SAP Business One
データベース: SAP Business One デモデータベース (Microsoft SQL データベース)

目的

テンプレートであるExcelワークブックを異なるパラメータで再計算し、実行日と州名をファイル名に含む新しいExcelワークブックとして作成します。

テンプレートワークブック
生成されたワークブック

実装

1. テンプレートワークブックの準備

分割のための基本クエリを準備する必要があります。
このクエリは、A/R 売上請求書テーブルからすべての顧客の要約情報を返します。

セル D3 にテーブル式が定義されています
クエリビルダーによるテーブル式の定義
フィルター: BP Typeは顧客のみのため C である必要があります
フィルター: Customer Nameはセル B3 を参照し、分割のための重要なフィルターです
フィルター: Document Dateはセル B4 を参照します
フィルター: Stateはセル B5 を参照します

a. 顧客リスト

別のシートに、Sharperlightのテーブル式を使用して顧客リストを準備しました。

セル C3 にテーブル式が定義されています
クエリビルダーによるテーブル式の定義
フィルター: Stateはセル B2 を参照しています。最終的にはセル B2 の値は、スケジュールされたタスクから渡される値により動的に変化します
年/月の値がセル B1 にスケジュールされたタスクから渡されます

さらに、Templateシートのセル B4 に =Filters!B1 を、セル B5 に =Filters!B2 を入力しました。

b. スプリット公式

セル A1 にスプリット公式を定義します。式は以下の形式になります。

=@mdSplitSheet("By Customer",AR_Sales_Invoice_Customer_Name[List of Customers],"",$B$3,"Green",0)

第1パラメータ

スプリッ公式の名前を指定します。

第2パラメータ

顧客のリスト。この値を使用して基になるシートが分割されます。別のシートにリストを準備し、式に記載します。

第3パラメータ

シート名のリスト。各スプリットシートの名前を指定できます。空白のままにすると、フィルタ値が使用され、この場合は顧客名が使用されます。

第4パラメータ

顧客名リストから各顧客名が渡される場所を指定します。

第5パラメータ

分割シートの色を指定します。

第6パラメータ

スプリット公式はデフォルトで親ワークシートの直系の子として抽出され、階層レベルは0です。レベルを1に設定すると、スプリットシートの式は子から抽出され、レベル2では孫からのみ抽出されます。任意のレベル番号をスプリットシートの式に追加できますが、その階層レベルに達したときのみ抽出されます。

c. テスト

定義が完了したので、式を実行してみます。
Filtersシートのセル B2 に QLD を入力し、ここで ワークシート - 再計算 アイコンをクリックして式を実行します。

顧客ごとに分割ワークシートが作成されます。

式が正常に動作することを確認したら、分割ワークシートを削除してテンプレートとして使用する必要があります。
ツリーアイコンをクリックしてツリーペインを開き、それを使ってワークシートを削除できます。

分割シートがぶら下がっているノードを選択し、削除アイコンをクリックすることで、そのノードを削除できます。

分割シートを削除したら、テンプレートワークブックを保存します。

2. 毎月、州単位で実行されるタスクの定義

テンプレートワークシートを再計算し、Report_{State}_{YearMonth}.xlsx という名前で指定されたフォルダーに保存するスケジュールされたタスクを作成します。
このタスクは毎月実行され、州および年/月の値がテンプレートワークブック内のクエリに渡されます。

a. スケジュールされたタスク

Sharperlightアプリケーションメニューからスケジューラを開きます。
タスクは毎月実行され、ワークブックは州ごとに再計算されます。

a.1 クエリの組み合わせ追加とクエリの組み合わせ終了

クエリの組み合わせとクエリの組み合わせ終了を選択します。これにより、州ごとにワークブックの再計算を繰り返し実行することができます。

クエリの組み合わせにはクエリが必要で、州のリストを返す必要があります。

クエリ定義

プレビュー結果

クエリから返された値は、その名前で参照することができます。

a.2 州ごとのExcelワークブックの再計算

クエリの組み合わせとクエリの組み合わせ終了の間にExcelワークブックアクションを追加します。
Excelワークブックは、Sharperlightで指定されたセキュアフォルダー内に存在する必要があります。これは、Sharperlightのサイトセットアップで行うことができます。

上記のケースでは、C:\Temp\、C:\QiitaSampleTemp\ および C:\QiitaSampleCopy\ フォルダがSharperlightからアクセス可能です。
テンプレートExcelワークブックは、これらのセキュアフォルダーのいずれかに配置しておく必要があります。

テンプレートワークブックの指定

話をスケジューラのタスクに戻します。
タスクに使用するテンプレートExcelワークブックを指定します。

パラメータの設定

テンプレートExcelワークブックに2つのパラメータを渡したいと思います。1つは {年/月} の値、もう1つは州です。ここで指定できます。
{年/月} の値はFiltersシートのセル B1 に渡されます。
州はFiltersシートのセル B2 に渡されます。

これは、以下の構文で実現できます。

最初のものは、Sharperlight式によって生成された現在の年/月をFiltersシートのセル B1 に渡しています。
2つ目は、クエリの組み合わせで生成された州の値を渡しています。

結果の保存

ワークブックを再計算した後、各州ごとにExcelワークブックとして保存します。
ここで指定することができます。

C:\QiitaSampleTemp\Document_{_Date}_{%State}.xlsx

その他のオプション

このオプションを有効にすると、結果が保存される際にすべてのSharperlight式が削除されます。

a.3 トリガーの追加

トリガータブでトリガーを設定します。SharperlightとMicrosoft Excelが同じPC上にある場合、Windowsスケジューラーよりもサービスタイマー（Sharperlightサービス）を使用する方が、設定が簡単です。

あとがき

これは、Sharperlightのスケジュールされたタスクを使用したオフィス自動化の簡単な例です。ただし、Microsoft Officeは自動化のために十分に設計されていないことが多いため、この種の自動化の設定は非常に難しい場合があります。そのため、いくつかの問題を経験しました。それに関するソリューションやワークアラウンドについては、別の投稿で紹介したいと思います。