analytic-vba のすべての投稿

コンテキスト遷移~DAXの何故こうなるのか?を解決する~

【このコンテキスト遷移の理解がDAX自体の理解の近道です】

DAXを使用していて「何故こうなるの?」ということに出会うことがあります

「何故こうなるの?」の不思議の一つが「コンテキスト遷移」です

早速、事例で見てみましょう

上の画像の2つの列は「大阪」で値が2300と1200と言う風に違いがあります

実際にメジャーの中身を見ると些細な違いしかありません

・MAXX_NoCal

  MAXX_NoCal = MAXX(‘担当者’,SUMX(‘売上’,’売上'[金額]))

・MAXX_withCal

  MAXX_withCal = MAXX(‘担当者’,CALCULATE(SUMX(‘売上’,’売上'[金額])))

MAXXの第二引数にCALCULATE関数があるかないかだけです

ところが、このCALCULATE関数のあるなしがとても大きいのです

CALCULATE関数があるメジャーは2300⇒1200となり、何らかの形でデータの絞り込み(フィルター)が行われています

今回の記事ではこの絞り込みが有効になる「コンテキスト遷移」の仕組みについて解説していきます

DAXで計算したいこと

今回は2つのテーブルを使用します

1つ目は担当者のテーブル、2つ目は売上テーブルです

売上テーブルは同一担当者が複数登場しています

DAXを使用して、この2つのテーブルを組み合わせて、東京と大阪で最大の売上をあげた担当者の金額を計算します

MAX関数での計算

「東京と大阪で最大の売上・・・」というように「最大」というキーワードが出てくるので、MAX関数を使用するのでは・と思った読者の方も多いと思います

では早速、MAX関数で計算してみましょう

MAX = MAX(‘売上'[金額])

そうすると上の画像のように、思ったよりも小さい金額算出されます

これはそもそも集計の単位が違っています

ですので、まずは担当者テーブルで新しい列を作成し、担当者別に売上金額を算出してみます

新しい列で担当者別の売上を算出した上で、MAX関数を使用してみます

これで意図とした通りの結果が出ました

ポイントは新しい列を作成する時、CALCULATE関数を使用していることです

画像に alt 属性が指定されていません。ファイル名: image-63.png

仮に、上の画像のCALCULATE関数を取り除いたらどうなるでしょうか?

全ての行の合計が算出されてしまいます

つまり、CALCULATE関数を使うことで遷移(全ての行⇒行別)が起こっているのです

MAXX関数/行評価

上のMAX関数の事例ではメジャーを作成するまえに、新しい列を作成しました

今度はMAXX関数を使用して、直接メジャーで担当者ごとの最高金額を大阪と東京で算出したいと思います

MAXX関数では単純に最大値を算出するのではなく、行別に式を評価した上で最大値を算出します

他の言葉で言い換えると、条件に合う「行の中」で最大値を算出してくれます

ちなみに、大阪と東京の合計金額は次の画像のようになっています

では、話を戻してMAXX関数を実際に使用して大阪と東京の担当者別の最大値を算出してみましょう

通常の合計金額と同じ結果になってしまっています

つまり、大阪もしくは東京という「行別」だけが考慮された計算金額になってしまっています

MAXX関数の第一引数はMAXX(’担当者’となっているのに「担当者の行別」は考慮されていません

これは、前に例に挙げた下の画像と同じ現象が起こっています

画像に alt 属性が指定されていません。ファイル名: image-65.png

この時はCALCULATE関数を使用して「行別への遷移」を起こしました

実は、今回も同じようにCALCULATE関数を使用すれば正しく算出されます

CALCULATE関数を使用することで、今回もMAXX関数内で「行別への遷移」を起こしているわけです

これで、DAXは何故こうなるのか?の謎が一つが解けたと思います

メジャー使用による行評価

番外編として、次の事例も紹介したいと思います

上の画像の一番右の式はCALCULATE関数を使用していません

MAXX_NoCal_withMEASURE = MAXX(‘担当者’,[売上合計])

その代わり、第二引数に[売上合計]というメジャーを使用しています

ですが、CALCULATE関数を使用したのと同じ結果がでています

これはメジャーを使うこと自体で、CALCULATE関数を使用するのと同等の効果があることを意味します

たとえメジャーの中身と同じ内容を関数の引数で使用しても、違う結果が出てしまうのはこの為です

<まとめ>

今回はCALCULATE関数を使用して、行別の評価を関数内で受け渡す「コンテキスト遷移」について解説しました

この概念を理解すると、格段にDAXの活用の幅が広がります

ぜひマスターしておきましょう!!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

Power Queryに強くなる~M言語/コードに慣れましょう~ 第10回

【Power Queryはあくまでコードでできています。これが今回のポイントです】

Power Queryはとても便利です。メニューをクリック操作していくだけでかなりのことができます

但し、メニュー操作に慣れてしまうと、エラーが起こった時に対処方法が分からなかったりします

そして、もう一つ問題があります

Power Queryが登場してから数年が経ちました

他の方が作成したPower Queryをメンテナンスする機会も増えていくと思います

この時、コード自体を理解できないとメンテナンスは行いにくいです

ですので、これからはPower Queryで書かれているMコードを理解できることも重要になっていくと思います

私も他の方が作成したPower Queryを修正する仕事をしていますが、Mコードを直接理解できないと仕事になりません

今回の記事を通じて、Mコードに慣れる機会にして頂けると嬉しいです

但し、今回の記事ではMコードを書くことは意図していません

あくまで、Mコードを読めるように訓練していく必要性と、Mコードを読む勘所を掴んで頂きたいと思います

今回のポイント

Power QueryのMコードはStepが連なってできています

ここがM言語の最大のポイントです

しかも前後左右、Step間で整合性が取られています

このStepに慣れるとそのままM言語に慣れることができます

Stepの中身の確認

ここからは実際に同じように手を動かしてみて頂くと分かりやすいと思います

たとえば、以下の画像のような平均金額を計算したPower Queryがあるとします

もちろんStepは「ソース」からはじまっています

Stepの中身は2つの方法で見ることができます

一つは数式バーです

二つめは詳細エディターです

こちらはホームタブから見ることができます

詳細エディターをクリックするとコードを丸ごと見ることができます

コードの中身を見て頂くと前後、Stepがつながっているのが良くわかります

let
①ソース = Excel.CurrentWorkbook(){[Name=”売上データ”]}[Content],
②変更された型 = Table.TransformColumnTypes(①ソース,{{“日付”, type datetime}, {“客先”, type text}, {“商品”, type text}, {“売上金額”, Int64.Type}}),
③計算された平均 = List.Average(②変更された型[売上金額])
in
③計算された平均

Stepの意味合い

試しにこちらの数式の中身を「ソース」に変えてみます

画像に alt 属性が指定されていません。ファイル名: image-40.png

そうするとどうなるでしょう?

表示はソースStepの内容になります

つまり、Step名自体に意味があります

Step名そのものが「データテーブル」を意味します

よくあるエラー

ここからは実践的な内容として、よく起こるエラーと対策について紹介します

ソースの変更

ソースステップの数式を見てみましょう!

数式の中身は次のようになっています

= Excel.CurrentWorkbook(){[Name=”売上データ”]}[Content]

試しにデータソースとなっている「売上データ」を「売上データ2」に変えてみましょう!

すると次の画像のようなエラーが発生します

この手のエラーはよく発生します

この場合、元のテーブル名に戻すか次のように数式を変更します

そうすればエラーは解消されます

変更された型

今度はソースステップで列名を変更してみます

そうすると以下の画像のようなエラーがでます

こちらはよく起こるタイプのエラーです

ところで「変更された型」Stepとはなんでしょう?

= Table.TransformColumnTypes(#”名前が変更された列 “,{{“日付”, type datetime}, {“客先”, type text}, {“商品”, type text}, {“売上金額”, Int64.Type}})

この「変更された型」Stepでは、各列の形式を設定しています

ここで1つ前にStepも含めて「変更された型」Stepを見てみましょう!

①名前が変更された列 = Table.RenameColumns(ソース,{{“商品”, “商品2”}}),
変更された型 = Table.TransformColumnTypes(名前が変更された列,{{“日付”, type datetime}, {“客先”, type text}, {“商品”, type text}, {“売上金額”, Int64.Type}}),

①名前が変更された列Stepでは「商品」列を「商品2」列に列名を変更しています

次の①変更された型では「Table.TransformColumnTypes(名前が変更された列」となっているので、①の名前が変更された列のテーブルデータを引き継いでます

つまり、「商品2」の列名を持つテーブルデータを引き継いでます

でも、②変更された型Stepでは商品列を「type text」、文字列形式にしています

{“商品”, type text}

ということは、既に存在しない列名を文字列形式に変更しようとしています

ですので、前述のエラーメッセージが出るわけです

画像に alt 属性が指定されていません。ファイル名: image-49.png

ではどうやってエラーを解消したらいいでしょう?

一番シンプルなのは変更された型Stepを削除することです

これで、Step間の整合性が取れるからです

let
ソース = Excel.CurrentWorkbook(){[Name=”売上データ2″]}[Content],
名前が変更された列 = Table.RenameColumns(ソース,{{“商品”, “商品2”}}),
計算された平均 = List.Average(名前が変更された列[売上金額])
in
計算された平均

では、どうやって形式変更を行ったらいいでしょうか?

手動で各列を変更するか、すべての列を選択した上で「データ型の検出」を行います

ちなみに、「変更された型」の自動設定を解除したかったら、ファイルタブから「クエリのオプション」を選択します

次に開く画面で「データの読み込み」から「非構造化ソース・・・を検出しない」を設定しておきます

そうすれば、次から「変更された型」Stepは自動で設定されなくなります

<まとめ>

今回の記事では、M言語/コードに慣れるきっかけになるような事例を幾つか紹介させて頂きました

この記事だけで飛躍的にPower Queryの技術が向上するわけではないですが、コードに注目していけば継続的に技術が向上していくはずです

今回の記事は本当に触りだけですが、本格的にM言語を学びたい方はこちらの記事をぜひご利用ください

尚、Udemyで動画コース「Power Queryのメニューにはない裏技ができる! M関数を学んで使えるようになる講座」をリリースしています

期間限定ですが、無料クーポンを提供させて頂きますので、ぜひご利用ください!

終了したら、レビュー投稿の程よろしくお願いします!

今後の参考にさせて頂きます!!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

Uploadしたエクセルファイルを直接コレクションにする~Power Apps~

【エクセルファイルをSharepointリストなどを経由せず、直接Power Apps内で編集できるようにします】

エクセルファイルの内容をSharepointリストに反映する前に、一旦編集したいケースもあると思います

その場合、一旦Sharepointリストに反映してしまうと「削除」を行う必要がでてくるなど、やたらと時間がかかることがあります

今回はJSON形式を経由して、直接エクセルファイルの内容をPower Appsに取り込みます

記事の解説は大きく分けると、次の3つになります

・エクセルをPower Appsから始動して一旦、OneDriveに名前を変えて保存する

・エクセルファイルをJSON形式に変えてPower Appsに送信する

・Power AppsでJSON形式を通常のコレクションにする

上記のうち「エクセルをPower Appsから始動して一旦、OneDriveに名前を変えて保存する」については、お手数ですが過去の記事をご参照願います

前提条件

・Uploadするエクセルファイルの中身は最初からテーブル形式になっていること

*OfficeScriptでPower Automateのフロー内で自動的にテーブル形式にすることもできます⇒過去記事

・Uploadするエクセルファイルの列は固定であること

ファイルのアップロード及びPower Automateの起動

フォームのパーツを使用してファイルをUploadする方法は過去の記事を参照してください

今回はファイルのアップロードしてコレクションを作成するまでの間は「処理中」と表示します

この「処理中」を表示するためには、ボタンの「OnSelect」プロパティにてUpdateContext関数を組み込みます

UpdateContext関数で更新する変数はラベルのVisibleプロパティに組み込んでおきます

そして、Power Automateフローを起動する時には変数:myflowで受け取れるようにします

UpdateContext({myflow: 

             エクセルインポート.Run(

                                         {

                                         contentBytes: First(DataCardValue.Attachments).Value,                                                              

             name: First(DataCardValue.Attachments).Name

                                    }

              )

});

Power Automate内でのJSON形式への変換

ファイルの作成

Power Automateフロー内ではPower Appsから受け取ったファイルの内容を基にして、まずはOneDrive内に新たなファイルを作成します

作成されるファイル名は過去に作成したものと同じにならないように、現在時刻をファイル名に入れます

*OneDrive内では作成したファイルを削除したり、ファイル自体を上書き更新するには一定期間を空けなくてはなりません。その為、このような処理を行います

JSON形式への変更

選択アクション

UploadしたエクセルファイルをJSON形式に変換するには、データ操作コネクタの選択アクションを使用します

「表内に存在する行を一覧表示」アクションで読み込んだ内容を「選択」アクションでJSON形式に当てはめていきます

この時に「日時」と「数値」形式の内容については、関数を使用してきちんと該当の形式に変換しておきます

・日時形式 ⇒例:formatDateTime(item()[‘Date’],’yyyy/MM/dd’)

・数値 ⇒例:int(item()?[‘Sales’])

注意

「選択」アクション内で、うまく動的コンテンツでエクセルの内容を拾えないことがあります

この時は2つ対応方法があります

・動的コンテンツの工夫

通常「表内に存在する行を一覧表示」アクション内では下の画像のように、ファイルの指定内容は動的コンテンツにて指定します

選択アクションにて動的コンテンツを使用するまでは「パス」ではなく、実際のファイルパスを指定しておきます

選択アクション内で動的コンテンツを設定したら、元通り「パス」を設定し直しておきます

・式での指定

一度、コードのプレビューを見てみましょう

上の画像を見ると、開始の「value」で処理する内容は<item()?[‘列の中身’]>で抽出できることがわかります

実際に<item()?[‘列の中身’]>を使用する時は、下の画像のように「式」の中で@を入れないことも大きなポイントです

JSONの解析

JSONの形式変更の最後では「JSONの解析」アクションを使用します

スキーマは「サンプルから生成」から作成しますが、こちらは一旦「””」で設定してフローを動かして設定します

*あまりUploadするファイルの行数が多いと取得できませんので、その場合は行数が少ないサンプルファイルを使用します

フロー動かした後の出力結果をコピーし、どこかメモ帳にでもコピーしておきます

その後、サンプルから生成をクリックし、次に表示される画面にコピーしてスキーマを作成します

Power Appsへの受け渡し

作成したJSONはPower Appsに受け渡します

上の画像では「msg」としてPower Appsに受け渡しますが、Power Apps内ではそのまま「msg」では抽出できませんので注意が必要です

Power Apps内でのコレクションの作成

Power Automateから受け取ったJSONの内容は、次の画像のようになっています

コンテキスト変数内で更に「msg」を経由することで「JSONの内容」を抽出できるようになっています

このJSONからコレクションを作成するには、2つの関数を使用します

ParseJSON関数」と「ForAll関数」です

最初のParseJSON関数では、受け取ったJSON形式の内容を解析します

解析した内容には形式がありませんので、次の画像のようにTable関数でTable形式にします

Table(ParseJSON(myflow.msg))

Table関数で作成したテーブルの内容は、ForAll関数でコレクションにします

ここで注意点です

ParseJSONで解析した内容はValueで抽出します

しかも、Valueで抽出しても形式が無いので、次のようにText関数やValue関数で形式を付与します

 {  

              Product:Text(Value.Product),  

              Date:Text( Value.Date),  

              Customer:Text( Value.Customer),  

              Sales: Value(Value.Sales)   

 }

ここで、JSON形式をコレクションにする内容を全てみてみましょう

 Clear(myJson);

ForAll(

        Table(ParseJSON(myflow.msg)),

          Collect(

                    myJson,

                    {

                      Product:Text(Value.Product),

                      Date:Text( Value.Date),

                      Customer:Text( Value.Customer),

                     Sales: Value(Value.Sales)

                   }

        )

)

 まず最初に作成するコレクション:myJSONをクリアしておきます

その後に、前述のようにParseJSON関数で解析しながら、JSONの内容を全てコレクションにします

<まとめ>

今回はエクセルファイルをアップロードしてコレクションにする方法を解説しました

具体的な内容としては①エクセル⇒JSON、②JSON⇒コレクションというように2段階で変換を行うので少し重い内容となっています

但し、意外と躓きやすい箇所が他にあります

「表内に存在する行を一覧表示」のアクションです

既存の設定だと2つ注意点があります

1.出力される行数が256行に限定される

こちらは設定を変えておきましょう!

上の画像の設定をクリックすると次の画面が開きます

こちらで「しきい値」を変えると出力する行数の上限を引き上げられます

2.DateTime形式

こちらは「ISO 8601」にしておかないと、日付形式の箇所はエラーになります

「ISO 8601」の他に「Serial Number」というのもありますが、その場合は日付形式の内容を設定するには「addDays関数」を使用します

こちらについては後日、別途詳細な記事を書きたいと思います

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

Power Automate Desktopでメールに表を入れて送る方法

【HTMLを使用して、とにかく手っ取り早く表をメールの中に入れて送ります】

添付のメールだと見てもらえるかわからないので、メールの中に表を入れて送るケースも結構あるようですね

但し、これはエクセルの表を貼り付けるなどの手間があるので、すごく面倒です

RPAで送るにしても、貼り付けだと安定性があるとは言えません

ですので、Power Automate Desktopで表をメールに入れるHTMLコードを自動で作成してメールを送ってしまおうというのが今回の趣旨です

メールで送る表

今回メールで送る表はエクセルで作成します

行数は可変という想定です

但し、列数は固定という想定になります

表を入れるHTMLコード

下が表をメールに入れるHTMLコードは以下のようになります

     <table border="1">
            <tr>
                <td>部門</td>
                <td>売上</td>
            </tr>
            <tr>
                <td>1行目部門</td>
                <td>1行目部門</td>
            </tr>
        </table>

これを見ても、HTMLコードを見たことが無い読者の方からすると、何のことか分からない・・・という風になるかもしれません

但し、<tr>x</tr>という「/」を介した組み合わせと、<tr></tr>の間に挟む内容が1行を表していることに注目して頂くと分かりやすいと思います

つまり、一旦、見出しを最初に<td></td>を介しながら設定した後は、エクセルの最終行まで読込処理を繰り返しながら<tr></tr>の間に読込内容を組み込んでいきます

なお、表を罫線で囲むには<table border・・・と表現します

使用するアクション

主に「テキストに行を追加」アクションを使うとフローが分かりやすくなると思います

追加した後は変数:Result(上の画像の場合)に格納するのですが、下の画像のような形にすると前述のHTMLコードが作られていく様子がイメージしやすいと思います

もちろん、こちらの変数:Resultはメールの本文に入れて送ります

繰り返し処理

まずは「テキストに行を追加」を使用して、表の見出し部分を作成します

その後は繰り返し処理を行列双方向で行い、表の中身を追加してきます

この時、行方向の繰り返し処理は最終行まで行うようにします

そして、1行の処理の開始及び終了時には<tr>と</tr>を追加します

開始
終了

表の中身を追加する際には<td></td>を組み合わせます

表の中身を追加し、繰り返し処理が終了した後は</table>を組み入れます

メールの送信

メールの送信時は、HTMLコードを設定した変数を組み込みます

そして、ここが最後のポイントですが、下の画像の「本文はHTMLです」をオンにしましょう

これで行数可変でメールの中に表を入れて送ることができます

<まとめ>

今回は手っ取り早く、コードを使用してメールの中に表を入れて送る方法を解説しました

今回紹介した内容では、罫線の形は下の画像の形でしか送れません

ぜひ、HTMLを解説する他サイトなどを参照して、罫線の形はご自身の好みに合うように変えてみてください

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

ファイルのアップロードを行えるようにするアプリ作成~Power Apps~

【どうしても煩雑になりがちなファイルの管理をPower Appsで行いましょう!】

今回の記事では、Power AppsからSharepointサイトのドキュメントにファイルをアップロードするアプリを作成します

このアプリを使用することで、ファイルの保存場所を管理しやすくなります

また、手間なファイルのアップロードの処理を簡略化できます

アプリ作成はそんなに手間暇かかりません

ただし注意点がが幾つかあります

記事の中でそちらについては明記してありますので、都度ご確認の程よろしくお願いします

ポイント

今回のアプリはPower Automateのフローと連携します

添付ファイルの内容をPower AppsからPower Automateフローに渡します

Power Automateフローでは、Power Appsから受けとった添付ファイルの内容を基にして指定Sharepointサイトのドキュメントにファイルを作成します

このファイルの受け渡し方が少々、とっつきにくいです

ファイルを添付する仕組みの作成~Power Apps~

最初にPower Appsでスクリーンを2つ用意します

そのうちの1つのスクリーンでフォームを作成します

フォームのデータソースは何でも構いません

欲しいのは「添付ファイル」のパーツです

下の画像のパーツを切り取り、別のスクリーンに貼ります

別のスクリーンにパーツを貼り付けた後は、元のスクリーンは削除しましょう

次に貼り付けたパーツのプロパティを以下のように設定します

・Items ⇒Blank()
・DisplayMode ⇒DisplayMode.Edit               ・MaxAttachments ⇒1

*パーツには扱いやすい名前を付けましょう!

ファイル作成をするフローの作成~Power Automate~

トリガー

ここからPower Automateフローを作成します

トリガーはPower Appsからファイルの中身を受け取れるように「ファイルコンテンツ」を指定します

ここで重要な注意点があります

必ず3点リーダーから「フィールドを必須にします」を指定しておきます

この設定が抜けるとうまく行きませんので確実に指定しましょう!

ファイル作成

次にファイル作成アクションを作成します

サイトのアドレスとフォルダーのパスには、ファイルの保管先を指定します

問題は次からです

まずファイル名です

こちらはそのまま以下をコピーしてください

triggerBody()[‘file’][‘name’]

コピー先は下の画像の「」の箇所ですので、間違いがないように注意をお願いします

コピー後は下のOKボタンを押します

すると以下のようになります

OKボタンを押した瞬間は上の画像のように、ファイル名が関数の表示になっています

こちらはフローを実行後は表示が変わるようになっています

画像に alt 属性が指定されていません。ファイル名: image-34.png

最後にファイルコンテンツを指定します

こちらはトリガーで受け取った内容を動的コンテンツから指定します

仕上げ~Power Apps~

それでは最後にPower Appsを仕上げます

Power Automateフローを動かすボタンを作成します

こちらのボタンでは、Power Automateフローに添付ファイルの内容を渡せるようにします

添付ファイルの内容は2つあります

こちらの2つの内容の渡し方が少し癖があります

下のように{}を使用します

UploadFile_Ver1.Run(   

     {     

      ①contentBytes:First( AttachmentFile.Attachments).Value,     

      ②name:First( AttachmentFile.Attachments).Name   

}

)

上記の①②の中身ですが、下の画像の名前を使用します

①contentBytes:First( AttachmentFile.Attachments).Value

②name:First( AttachmentFile.Attachments).Name

こちらの内容については、上記の内容をそのままコピーし、パーツ名だけを変更することをおすすめします

これで指定した箇所に、Power Appsを通じてファイルを保管できるようになります

<まとめ>

今回はPower Appsを通じてファイルを指定箇所にアップロードできるようにしました

そんなにアプリ作成には時間がかからないはずですが、何点か注意点があります

・Power Automate ⇒トリガーで必ず「必須」を指定する

・Power Automate ⇒ファイル作成アクションでファイル名を記事のコピーで作成する

・Power Apps ⇒Power Automateにファイル内容を渡す際、{}を使用して2つ引数を指定する

そのままアップロードしたエクセルファイルの内容をPower Apps内で使用する場合には、テーブルを事前に作成しておけば大丈夫です!

これでファイルの管理も楽になります

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

フォームに追加、編集、登録ボタンを設定する~Gallery連携~

【DisplayModeを使いこなして、表示が可変のプロっぽいボタン作成を行う】

Power Appsのフォームにはなかなか奥深い機能があります

何回かに分けてフォーム機能について記事を書いていきたいと思います

今回やりたいことはフォームに追加、編集、登録ボタンを設定することです

新規にデータを追加したいときは登録ボタンのみが表示されます

データを編集したい時には編集ボタンのみが表示されます

但し、編集ボタンを押すと登録ボタンのみが表示されます

上記の機能を作成するには、Display.Mode属性が特に重要になります

ポイント

今回はGalleryとフォーム(以降、Form)の連携の中で機能を追加します

*Gallery:名称/Gallery_Sales、Form:名称/Form_Sales

GalleryがあるページからFormのあるページに移動する時などに、Formの表示モード/DisplayMode切り分けることがとても重要になります

View:表示のみ

New:新規

Edit:編集

FormのDefultModeは「FormMode.New」を設定しておきます

ですので、+の追加ボタンを押したときにはFormのDisplayModeは各フォームコントロールに値がない新規モードになります

Galleryからレコード/行を選択したい場合は「FormMode」を「View」にします

登録ボタンの作成

こちらはOnSelectプロパティに定番のSubmitForm関数を設定します

但し、Visibleプロパティを工夫します

Formを閲覧するだけのViewモード以外の時に表示されるようにします

この時、数式内のIF式は省略できます

Form_Sales.DisplayMode<>DisplayMode.View

追加ボタンの作成

追加ボタンの機能はGalleryがあるページから移動するだけでなく、Fomをリセットします

ResetForm(Form_Sales);Navigate(Screen2)

編集ボタンの作成

編集ボタンの作成ですが、その前にGalleryとFormのItemプロパティに追加が必要です

GalleryからFormに表示するレコードを選択する際に、ViewForm関数を設定し、FormのDisplayModeを閲覧のみのViewに設定できるようにします

ViewForm(Form_Sales);Navigate(Screen2)

そして、編集ボタンはFormがViewモードの時のみ表示されるようにVisibleプロパティを設定します

Form_Sales.DisplayMode=DisplayMode.View

更に、編集ボタンのOnSelectプロパティにて、FormのDisplayModeを編集/Editモードに変更できるようにします

EditForm(Form_Sales)

これで追加や編集時にボタンが必要な時に表示されるようになります

<まとめ>

今回はFormに追加や編集ボタンを設定できるようにしました

Formの表示モードがとても重要になります

登録ボタン:FormのDisplayModeがView以外の時のみ表示

*Formを閲覧する時は登録ボタン不要

編集ボタン:FormのDisplayModeがViewの時のみ表示 

⇒OnSelectプロパティにはEditFormを設定⇒登録ボタン表示

少しややこしいですが、整理しながら設定すると必要な時に必要なボタンのみが表示されるようになります

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

脱エクセルについて考える~資金繰り表をPower Platformで作成~第9回

【エクセルは本当に便利なツール。但し、効率と効果を上げるには限界があります】

資金繰り表はエクセルに向いている業務の典型です

それは何故か?

上の画像の黄色のセルには数式が入っており、預金残高の予想残高を計算しています

但し、この数式を必要に応じて実際の預金残高「数値」で上書きすることができます

実はこれってエクセル以外のソフトだと難しいんです

「数式」と「数値」を共存できるエクセルは本当に便利なんです

ではエクセルで業務を続けていたらそれでいいのか?

それはそれで問題があります

下の画像の「2023/1/5」の売上高の欄には120と入っています

入力数字は120でも、実は120という数字を別な表で複数の顧客の数字を積み上げて入力されているかもしれません

それに、表に存在する日付/列単位が膨大になってくると、入力箇所を見つけるのも面倒です

つまり量が膨大になってくると、この資金繰り表の1セルを入力する作業がかなり面倒になってきます

その理由は一般的なエクセルの資金繰り表では「データ蓄積」「作業」「計算」「表示」が分かれていないからです

ですので、作業や計算スペースを作る関係上、データの並びが不規則になってしまったりしますし、入力しやすいようなスペースを作ることも困難です

更に言えば、資金繰り表のシートのデータを活用して他のシートで別の目的の表を作成しようとするとかなり複雑なものになってしまいます

実は、Power BIのDAXを使用すれば預金口座の残高も、実残高と予想残高を共存させながら計算できます

今回の記事ではPower BIPower Appsを使用して、エクセルの資金繰り表の「データ蓄積」「作業」「計算」「表示」を分けて管理できる仕組みの概要を紹介します

そして、Power Platformの便利さに触れて頂けたら幸いです

ポイント

Power Platformを使って資金繰り表を作成するには、分解が結合がポイントです

データの分解・結合

預金残高は数式と数値を共存できるように、以下にデータを分けて管理します

・預金残高(実残高)

・入金

・出金

更にカレンダーテーブル

上記の4つをDAXで結合して使用します

今回はSharepointリストを使用します

入出力と表示の分解

今回は入出力はPower Appsで行います

Power Appsならページを分けて、売掛金や経費などの入出力専用ページを作成できます

そして、表示や主な計算はPower BIで行います

Power BIは集約計算の簡単にできるし、DAXで複雑な計算も行えますので、Power BIの機能もフル活用します

エクセルで日付毎に複数の顧客の売掛金を合計していた作業は、前述のテーブルを作成しておけば勝手にPower BIが行ってくれます

入出力と表示が分かれていると不便だろうという場合は、後述するPower BI内でPower Appsを使う方法もあります

預金残高を表示するDAXの作成

ここがエクセルの資金繰り表を他のソフトで行う最大の難所です

日付の管理がとても重要です

・実際に預金残高を入力した日付の残高⇒そのまま残高を表示

・預金残高を入力していない日付の残高⇒実際に預金口座を入力した日付以降の入出金を加味して計算

例えば、1月1日の残高が100、1月2日の入金が100、1月3日の出金が100だったとします

この場合は

1月1日残高⇒100/実績残高

1月2日残高⇒200(100+100)/予想残高

1月3日残高⇒100(100+100ー100)/予想残高

となります

こちらを実際にDAX式に直したのが以下です

*CashBalance:実際の預金残高、Receipt:入金、Disbursement:出金

預金残高 =  

VAR currentDay=MAX(‘Calendar'[Date]) 

VAR maxDay=CALCULATE(MAX(‘CashBalance'[Date]),FILTER(All(CashBalance),’CashBalance'[Date]<=currentDay)) 

VAR cashAmount=SUMX(FILTER(ALL(‘CashBalance’),’CashBalance'[Date]=maxDay),’CashBalance'[CashBalance]) 

VAR cashReceipt=CALCULATE(SUM(‘Receipt'[Amount]),FILTER(All(‘Receipt’),’Receipt'[Date]>maxDay &&’Receipt'[Date]<=currentDay)) 

VAR CashDisbursement=CALCULATE(SUM(‘Disbursement'[Amount]),FILTER(All(‘Disbursement’),’Disbursement'[Date]>maxDay &&’Disbursement'[Date]<=currentDay))

RETURN cashAmount+cashReceipt-cashDisbursement

今回の記事では詳細には解説を行いませんが、一見複雑なこの式の最大のポイントは「預金残高の最大日付:maxDay」を算出することです

冒頭のたとえを使って説明すると、今日が1月3日だとしたらその前に預金残高がテーブル:CashBalanceに貴重された日付の1月1日を算出するということになります

最大日付が算出できれば、おのずと入金金額を算出する期間と出金金額を算出する期間が求まります

ただ実際には、フィルターを意識してAll関数をうまく組み合わせていく必要があります

専用アプリによる預金残高表示

Power BIには様々なアプリ/ビジュアルがあります

今回は資金繰り表に活用できるビジュアルを1つ紹介します

その他のビジュアルの取得をクリックして「Calendar」と検索してください

すると「Beyondsoft Calendar」というビジュアルをダウンロードできます

こちらのビジュアルは日々の預金残高をカレンダー上で表示できるという優れものです

残高の状態によって色を変えたりもできます

エクセルではなくPower BIを使用することで、専用アプリを活用することもできるんです

Power BIとPower Appsの連携

Power AppsをPower BI内で使用したい場合は、こちらをクリックするところからはじめます

すると、Power Appsに使用するフィールドの入力ができるようになります

実は、こちらはなんでも構わないんです。取り合えば何か設定すればPower BI内で表示したいPower Appsのアプリを選択できるようになります

<まとめ>

今回は脱エクセルについて考えると称して、エクセルの資金繰り表をPower AppsとPower BIを連携させて作成する方法の概要を紹介しました

紹介したのはあくまで概要ですが、エクセル作業が改善されていく可能性を感じて頂けたら幸いです

現実に、エクセルは表計算ソフトですので大量のデータを処理するには向いていません

なので、Power PlatFormの仕組みを利用して「データ蓄積」「作業」「計算」「表示」を分解・結合できるようにすることはとても意義があります

そうは言っても、誰でも手軽に使えるエクセルと違ってPower BIのDAXなどが複雑であったりするので専門知識が必要です

それに当然、手間暇も必要です

ですので、最終的にはどの程度の量の業務をどんな風に変えたいかが一番のポイントなのかもしれませんね

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

Power AppsでSharepointリストを扱う時の注意点

【Sharepointリストの参照列、複数行のテキストを扱う時は文法が違ってきます】

Sharepointリストはエクセルのように扱えて手軽で便利なのですが、列の種類に応じて対応の仕方が違ってくるのが少しややこしいです

今回の記事では実際の例を交えて「対応の仕方」を解説していきます

参照列

参照列とは?

こちらはエクセルのワークシートで言えば、VLOOKUP関数で取得した列のような形です

次の画像は今回の解説で使用する参照列です

CustomerName列は他のリストを参照しています

実は参照元のIDを基にして参照が成り立つようになっています

取得

参照列を下の画像のように、そのまま内部名で取得しようとするとエラーになります

この場合は、下の画像のように1つ加えます

CustomerName列「値/Value」を取得する形になります

更新

IDが5の行のCustomerName列を「C001」から「C002」に変更するとします

通常ですとこの場合は、下の画像のようにPatch関数を書きます

これだと実はエラーになりますので、第三引数の{}の中を次のように書き換えます

{}が入れ子になる形になります

ここで「Id」は参照元のIDを指定します

複数行テキスト

複数行テキストとは?

1行テキストの場合は文字数等に255までの制限があります

複数行テキストの場合は「63,999」まで文字が入ります

そして、設定を変えると「HTML」での設定になります

そうすると文字の色なども設定できます

取得/HTML形式にて

複数行テキストを「HTML形式」で設定すると、取得する場合は通常のラベルですと以下のようになってしまいます

ですので、通常のラベルではなくHTMLテキストを指定しています

そうすれば、通常のテキストのように取得できます

この時、プロパティも通常と違いますので注意が必要です

更新

複数行テキストをHTML形式で更新するには、リッチテキストエディターを使用します

リッチテキストエディタ―はPower Apps上にて、通常のエクセルのワークシート上のような操作ができる優れものです

取得はTextプロパティがないので「Default」プロパティで行います

更新は通常の「テキスト入力」と同じ要領で行います

<まとめ>

今回はPower AppsでSharepointリストの参照列、複数行テキストを扱う際の注意点を解説しました

参照列の場合は参照元リストの「ID」の扱い、複数行テキストの場合はHTML形式の扱いがポイントになります

やはりSharepointリストは癖があるので注意が必要ですね

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

ガッツの日記 第8回 Microsoft社のAI Builderを使用してみた

【AI Builderがどれだけ手軽か試してみた!やはりかなり手軽に活用できそうです!!】

ChatGPTが登場してAIも身近にはなってきていますが、AIの魅力を知るのに一番AI Builderが手軽だとおもっています

今回の記事ではPDFの請求書から自動で情報を抽出してみます

そして、

抽出した情報はPower AppsもしくはPower Automateで利用できるようにします

大まかな流れとしては以下の3つの事を順に行います

①請求書情報/学習用データの準備

②請求書情報の学習

③Power AppsもしくはPower Automateで情報を利用できるようにする

①~③の流れの中で「コード」は一切書かかないのが大きな特徴です

②の請求書情報の学習では、読者の多くの方が利用されたことがあると思いますが「タグ付け」も行います

記事の最後にはAIが身近、手軽になったことを実感してもらえたら幸いです

請求書情報/学習用データの準備

PDFの請求書を5つ用意します

全て同じフォーマットで揃えます

別途、学習後のテストデータも準備しておきましょう

請求書情報の学習

AI Builderの該当画面を開く

Power AutomateからAIモデルを開きます

こちらの画面には様々なメニューがあります

こちらの中から「請求書から情報を抽出する」というメニューをクリックします

すると次に開いた画面の左下に「新しいアップロード」というボタンがあります

こちらのボタンから学習用データの1つをAI Builderに取り込めます

取り込んだデータはしばらくこんな感じです

この揺れ方がとてもリアルです

いかにも学習しているという感じです

学習が終わると画面右横に情報が抽出されています

これで請求書から抽出する項目がリストされた形になります

ちなみにこれはまだ1段階目の学習です

また次で学習用データの追加が出てくるので違和感を感じるかもしれませんが、1段階目では単に項目を抽出したにすぎません

学習用データの追加

1つ目のデータの学習が終わったら画面右下から「カスタムモデルを作成する」をクリックします

次に開く画面では、リストアップされた項目に対して項目の追加を行います

ただ、今回は解説をスキップして次に行きます

次に開いた画面ではコレクションに学習用データを全て追加します

ここでいうコレクションとは学習用データ一式という意味合いになります

上記の作業を繰り返して学習用データ一式を揃えると画面は次のような形になります

それでは次に行きます

項目調整

次に開いた画面では、追加したデータに赤い印が表示されていました

これは一旦設定した項目がうまく拾えていないことを意味していますので調整が必要です

うまく拾えている項目は「緑」の線で囲まれ、画面上にエラー表示*がありません

*肌色の部分

うまく抽出できていない項目は、もしも必要がなければ取り消すこともできます

もし、うまく抽出できていない項目がある場合は「タグ付け」処理を行います

*上記処理は1つ前の画像と違う学習データで処理しています

では、表となっている各項目の場合はどうするのか?と思われる方もいらっしゃると思います

この場合は、このマークをクリックします

そして、抜けいている部分をクリックしてからタグ付けを行います

タグ付け等の抜けがなくなり、コレクションの赤いマークが消えたら次に行きます

モデルの公開

次は「モデルの公開」なのですが、その前にモデルをトレーニングします

トレーニングには数分かかる場合もあります

トレーニングが終了すると、モデルの精度が確認できるとともに「公開」処理が行えます

Power AppsもしくはPower Automateで情報を利用できるようにする

モデルを公開すると「モデルの使用」と表示されますので、こちらをクリックします

すると、モデルの使用方法が複数表示されます

Power Apps

次に「・・・アプリを構築する」をクリックします

これだけでPower Appsで「フォームプロセッサ」が設定されたアプリが自動構築されます

このフォームプロセッサには新たな請求書をアップロードできます

但し、これだけだとアップロードしてAIで処理した内容が見れません

ですので、ギャラリーを設置します

ギャラリーのItemsにはフォームプロセッサ名と「Results」を設定します

ラベルには「ThisItem.XXX」に加えて「.Value」を加えると正しく表示されます

これでAIで抽出した項目はギャラリーで表示できるようになりました

ただ、以下のようなテーブルの内容はどうするか?という問題がまだ残ります

画像に alt 属性が指定されていません。ファイル名: image-11-644x126.png

テーブルの内容を表示するには、ギャラリー内に更にギャラリーを追加します

入れ子のギャラリーのItemsには「ThisItem.items」を設定します

ラベルの中身には「ThisItem.~.Value」を前述と同じように使用します

Power Automate

前述の「モデルの使用法を選択してください」にて、「インテリジェントな自動化を構築する」を選択するとPower Automateで作成したAIモデルを活用できます

Power Appsを使用した時には請求書のPDFをアップロードしましたが、今回は指定のアドレスに指定の件名でPDFを添付ファイルとして送ります

そして、AIモデルのから出力された内容が、これまた指定のメールアドレスにメール本文に含まれて返ってくるようにします

前述の「インテリジェントな自動化を構築する」をクリックしたら、次の画面では受け取り方法を選択します

そして上記画像のように「Microsoft365Outlook作業アカウント」を選択してください

すると、途中で「続行」の指定をする画面が出てきますが、そちらで「続行」を指定するともうPower Automateフローができています

デフォルトではトリガーでは件名を次のように指定するようになっていますので、必ずテストする際にはご確認ください

Power Automateフローの最後の「メール送信」アクションは本文を丸ごと修正して、受け取りたい項目を動的コンテンツから指定してください

これで添付ファイルをメールすれば、添付ファイルの請求書の内容を受け取れます

仮に、複数行が出現するテーブルの項目を受け取る項目に指定した場合はメールが複数送信されるようになります

<まとめ>

今回はAI Builderの「請求書から情報を抽出する」メニューで請求書のPDFから情報を取り出しました

学習のプロセスが意外と簡単だと感じられたのではないかと思います!

AIモデルを学習したら、簡単にPower AppsやPower Automateで結果を出力できるのも大きなポイントです

とうとうAIも本当に身近になったと、今回の記事を通じて感じて頂けたら嬉しいです

実際にAI Builderを使用するにはライセンスが必要です

決してべらぼうに高額というわけではありません

もし、一定期間試してみたいという場合には期間限定の「試用版」もありますで、ぜひご利用してみてはいかがでしょうか?

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

ガッツの日記 第7回 「エクセルとSharepointリスト」それぞれのいいとこどりをする方法

【今回の記事はデスクトップのエクセルの新たな活用方法の提案です】

ここ数年でSharePointリストなどのクラウドサービスを利用する人はかなり多くなってきています

クラウドはネットさえつながればどこでも使うことができ、特にSharepointリストは複数人でデータを共有して仕事するのに便利な機能が満載です

履歴の復元が行えたり、登録者や更新日時が確認できます

何より3000万行を蓄積できるのがいいです

エクセルはあくまで表計算ソフトであり、大量のデータを蓄積していくのには本来は向いてません

蓄積できる行数も1シート100万前後です

一方、使い慣れたデスクトップのエクセルをこれからも使用していきたいという方も多いと思います

特にVBAを使って業務を回してきたケースの場合は、そのままデスクトップのエクセルを継続的に使用せざる負えないです

ここからが本題です

実は、

Sharepointリストとデスクトップのエクセルを組み合わせて使用する方法があります

ADOという仕組みを使うと「Sharepointリストからエクセルへのデータ取得」「エクセルからSharepointリストへのデータ追加・更新」が自動で行えます

以前、私はこのADOを使用して「Accessとエクセル」を連携するシステムを開発したことがあります

この方法ならばAccessにデータを一元管理しながら、利用者各自のエクセルファイルにて作業を行うことができます

何が一番素晴らしいかというと、売上管理や商品管理などの各種エクセルにてマスタを共通利用できる点です

エクセルでデータ管理を行っていると、複数ファイルでのマスタの追加や更新などの処理が煩雑になりがちです

ADOを使用した仕組みの場合は、Accessにてマスタ管理を行えば、利用者の各種エクセルファイルにてマスタを自動でダウンロードして使いまわすことができます

今回の記事では、Accessの代わりにSharepointリストを使用するわけですが、クラウドで利用できる点なども含めてかなり効率的な仕組みができると考えています

VBAを使い込んでいた組織では、VBAを引き続き有効利用しながらクラウドを活用できるのも大きいです

以降の記事では、Sharepointリストの特徴なども交えながらデータ取得・追加・更新のサンプルコードを解説していきます

尚、今回の記事はVBA経験者を主な対象としていますのでその点は予めご了承ください

事前準備

ライブラリーの追加

ADOをVBAで使用する場合には、Visual Basicのツールタブ⇒参照設定からMicrosoft ActiveX Data Objectsライブラリを追加する必要があります

ここが抜けてしまうと、コードをいくら書いても動きませんので注意が必要です

サイトURL及びリストID

ADOを使用してSharepointリストをデータソースとして使用する場合は「サイトURL」と「リストID」を使用します

サイトURLについては分かりやすいと思います

上記画像の「sites」の後と「Lists」の前までがサイトURLです

リストIDは普段聞きなれないと思いますが、Sharepointリストの内部名のことです

今回の記事で使用するSharepointリストのリスト名は「Sales_Test」になりますが、別の名前/IDをADO内では使用します

リストIDの取得はこちらから行います

上の画像の「リストの設定」をクリックすると、次に開く画面でリストIDが取得できます

こちらの「%7B」と「%7D」の間がリストIDになります

共通構文

以下、サンプルコードを実際に解説していくわけですが、共通の部分があります

******

Dim objConnect As Object ‘ADODB.Connection
Dim recordSet As Object ‘ADODB.Recordset
Const Sharepointurl As String = ①”https://xxxx5.sharepoint.com/sites/TEST
Const listid As String = ②”b24faf63-1f23-xxxx-858e-278ead83f878

③Set objConnect = CreateObject(“ADODB.Connection”)
④objConnect.Open “Provider=Microsoft.ACE.OLEDB.16.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=” & Sharepointurl & “;LIST=” & listid & “;”
⑤Set recordSet = CreateObject(“ADODB.Recordset”)

******

①②ではサイトURL、リストIDを設定します

③では、ADOとの接続を設定し、④で実際に①②のサイトURLとリストIDを通じて接続を行います

⑤では取得したレコード/行一式を格納する箱を設定します

Sharepointリストの列名を取得

本来であれば、早速データの取得のサンプルコードから紹介したいところなのですが、Sharepointリストの列名の取得から始めたいと思います

何故かと言うと、ここにSharepointリストの特徴の1つがあるからです

******

Sub getFields()
(共通)

With recordSet
①.Open “SELECT * FROM [Sales_TEST];”, objConnect
②.MoveFirst

Dim i As Long

③For i = 0 To recordSet.Fields.Count - 1

    ④Debug.Print recordSet.Fields(i).Name

⑤Next

   .Close

End With

objConnect.Close

End Sub

******

①にてレコードを該当Sharepointリストから取得し、②でレコードの最初に移動します

③~⑤でレコードの列名を繰り返し、イミディエイトウィンドウに表示します

取得した列名を見て頂くと、設定していない列が取得されます

ここがAccessと違うところです

一番上に出ているIDはレコードを追加すると「1,2,3・・・」と自動採番されます

このIDは今回もSharepointリストの更新で使用します

ちなみに、使用した接続やレコード一式は必ず閉じる/Closeするようにします

データの取得

Sharepointリストからデータを取得するには、2つの方法があり、列別に取得する方法とレコードセットを丸ごと取得する方法です。

列別

前述の列名で取得した「列名」をここで使用します

*太字の部分です

******

With recordSet
.Open “SELECT * FROM [Sales_TEST];”, objConnect
.MoveFirst

Dim i As Long

Do Until .EOF
  Debug.Print .Fields.Item("注文NO").Value
  .MoveNext
Loop

.Close

End With

objConnect.Close

******

一括

こちらは一括でSharepointリストからレコード一式をワークシートにコピーします

下のコードではSheet5のA2セルに一括でコピーしています

******

With recordSet
.Open “SELECT * FROM [Sales_TEST];”, objConnect

Worksheets("Sheet5").Range("A2").CopyFromRecordset recordSet

.Close

End With

objConnect.Close

******

もし、データが一行もない場合は「データが無かった旨」を表示できるようにすると便利です

.Open “SELECT * FROM [Sales_TEST];”, objConnect

If recordSet.BOF = True And recordSet.EOF = True Then
   recordSet.Close
   objConnect.Close
   MsgBox "該当するデータはありません。"
   Exit Sub
End If

データの追加

以下の①から⑧までを一式で使います

******

①recordSet.Open “[Sales_Test]”, objConnect, adOpenKeyset, adLockOptimistic

②objConnect.BeginTrans

③recordSet.AddNew
④recordSet.Fields("注文NO").Value = Worksheets("Sheet6").Cells(2, 1).Value
⑤recordSet.Fields("SalesAmount").Value = Worksheets("Sheet6").Cells(2, 2).Value

⑥recordSet.Update

⑦objConnect.CommitTrans

⑧recordSet.Close

******

①のadOpenKeysetadoLockOptimisticは他に指定する方法がありますが、こちらの方法であれば大概は問題ありません

④⑤で追加データをワークシートから読み込んでいます

追加データが複数行ある場合は「For ~ Next」内に③~⑥を入れてコードを書きます

そして最後に⑦を実行します

データの更新

前述のようにSharepointリストのIDを使用し、更新するレコードを指定するのがポイントです

ですので実際には、事前にレコードの取得をしておいてIDを特定できるようにする必要があります

******

objConnect.BeginTrans

①recordSet.Filter = "ID = " & Worksheets("Sheet7").Cells(2, 1).Value
recordSet.Fields("SalesAmount").Value = Worksheets("Sheet7").Cells(2, 2).Value
recordSet.Update

******

データの追加も同様ですが、接続が行えない場合は「ロールバック処理/もとに戻す」を行えるようようにしておくのがよいです

<まとめ>

今回はADOの仕組みにより、Sharepointリストからエクセルにデータ取得したり、エクセルからデータ追加・更新を行う方法を解説しました

今回紹介した内容は基本的な内容であり、実際には条件式を組み合わせるなど様々なパターンが考えられます

実はSharepointリストとADOにて接続する場合、1点厄介なことがあります

ADOで扱うSharepointリストに参照列が存在する場合です

上の画像の一番右、CustomerName列は参照列です

他のSharepointリストから参照しています

参照列をADOで取得すると、次の画像のように参照しているSharepointリストのIDに置き換わってしまいます

ですので、IDを変換する仕組みが必要になります

データ追加の時も、参照列がある場合には同様にIDを変換する仕組みが必要です

とにかくSharepointリストを扱う上では「ID」が非常に重要です

この点は強く意識しておきましょう!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ