Excel.sys

データ取得の次は、データ更新を行ってみる。

「StiLL」のボタンテンプレートから左上のボタンを貼り付け、そのボタンをクリックして「Excelデータ更新」を選択すると、設定ダイアログが表示された。

ヘルプによると、更新対象位置の項目名と入力レコード位置で指定したキー列の項目名（最大４つまでＯＫ）で同じレコードがあれば更新、なければ追加の処理となる。

ここで簡単な伝票番号のインクリメント処理を作ってみたい。
下図のように、シート上にＩＤ、伝票Ｎｏ、枝番、updateの４項目のデータを準備した。
ＩＤは１固定とし、元データと更新用データをそれぞれ用意。
更新用データで元データを更新する。
  
図に表示されていないが、セルＪ８には関数（TODAY）が入力されている。
更新用データにチョット数式と関数を使って、同日更新F（updateの変化）をみて、日付が変わったときは伝票Ｎｏをインクリメントし、同じ日付の場合は枝番をインクリメントする動きにしてみた。なお伝票Ｎｏをインクリメントするときは枝番を初期化する。（上図説明参照）

肝心のボタンの設定は次の通り。極めて簡素だ。


「Excelデータ更新」ボタンをクリックして実行すると、最初は日付が違うので、元データの伝票Ｎｏがインクリメント（＋１）された。
 
またupdateも置き換えられ、同日更新Fの値は１になった。（更新用データの伝票Ｎｏは変わらず）

もう一度「Excelデータ更新」ボタンをクリックすると。
今度は枝番がインクリメントされた。

以降、クリックする度、枝番が＋１されていく。
たぶん、明日になれば伝票Ｎｏが＋１され、２となるだろう。

今回はExcelの要素が多くみえたが、このような更新処理はExcelのみではできない。
もちろんＶＢＡでプログラムを作りボタンに関連付けすることはできるが、こんなに簡素にはいかない。簡素な分、逆にExcelでの設定部分が目立ってしまったのかもしれない。


続く。

きのうの続き。
出力項目の選択で、並べかえを入力した状態です。
ここでは単純に支店名と伝票NOを入れ換えてみた。

なおデータソートの順番ではないので注意のこと。
またそのときのボタンのダイアログも示します。
  

次に、条件式を使ってみる。
（条件式の作成）をクリックすると下図（イメージ）のように左辺にデータの項目名を指定し、比較演算子に続けて条件値を入力する仕組みだ。
 

ここで式の右端には選択されている項目名の型名が表示される。
また比較演算子のLikeとNot Likeは項目の型名が文字の場合のみ表示され選択可能。
ANDかORを選択した場合は次行の入力欄が表示され最大５つの条件式までを設定できる。

更に中央の（→）をクリックすると選択した項目の実データが集約され、選択肢として（→）横のプルダウンリストにセットされるので便利。

下図は支店名が集約されたプルダウンリストのイメージ


支店名にA支店を選択してＯＫボタンで設定し、ボタンを実行するとＡ支店が並べかえた順番で表示された。


続く。

本日は夏休み明けの台風襲来だ。
早めに家を出たら湘南新宿ラインはほゞ定刻通りに運行されていたので遅れることなく早く会社に着いた。

今日は、「DBデータ取得」のボタンを使ってみる。
これまで同様「StiLL」のボタンテンプレートから左上のボタンを貼り付け、そのボタンをクリックして「DBデータ取得」を選択すると、最初にデータ ソースの選択ダイアログが表示された。

このボタン処理では、ＯＤＢＣ定義（登録）されたデータベース（ソース）を利用するので、先にＯＤＢＣの設定を行うが・・・、
その前に、実はデータベースをまだ用意してなかったので、Accessにテーブルを作成する。

作成するテーブルの内容は、これまで利用してきた「月別集計表1.xlsx」の（data）シートをそのままアクセスのテーブルとして取り込む。
（この処理についてはAccessの機能である外部データ取り込みを使って作成）
データベース名はTestDatabase.accdb、テーブル名はTestdataとした。

（取り込んだテーブルのイメージ）
 
これをMicrosoft Access Driverを用いDＳNを設定する。DＳNの名前はK_TestdataDBとした。
この部分の手順については、「DBデータ取得」ボタンダイアログの（？）マークをクリックするとヘルプが表示され、その中の（注意）にAccessでのＯＤＢＣの設定方法が詳しく示されているので省略。

以上の準備を行い、データソースの選択画面は以下の通り。
 

ＯＫボタンで「DBデータ取得」ボタンの設定画面が表示されたので、下図のように入力した。


出力範囲名（DATA1）を入力すると、データが表示された領域（ここではセルD9基準）に名前が定義されるので、その後の処理でこのデータを加工する場合などにセルで範囲指定をせずとも可変領域として名前が扱えるので大変便利だ。

完成した「DBデータ取得」ボタンを実行すると、指定したセルD9からAccessのテーブル内容が表示された。

因みに出力項目は、初期状態のすべてとしたが項目選択で出力する並び順をかえることも可能だ。


続く。

今度は検索式（範囲）の設定。
文字通り出力するデータの条件を入力（指定）するところ。
ここは、先に項目式が作成されていないと（検索式作成）ボタンをクリックすることができない。

範囲入力より先に（検索式作成）ボタンを開き、項目式作成と同じ要領で条件付けを行う項目名を選択する。

（支店名）を選択して右矢印で< 検索式 >に移送すると、ダイアログ下段に選択した項目名と、その下に条件を入力する枠が表示される。
下図参考。
 

条件式には等号・不等号などの演算子やワイルドカード文字（＊）を使用することができる。
ここでは下図のように支店名に「Ｅ支店」と入力してＯＫボタンをクリックする。
 

すると、次の「条件の出力位置（検索式範囲）」の入力画面が表示された。
 

ここに条件付けした項目数（今回はひとつ）を確認してシート上の適切なセル位置を選択し設定すると、指定したセル位置に条件式が展開・表示された。

これで設定を終了し、先の表示されているデータをクリアしてボタンを実行すると、「Ｅ支店」のデータのみが表示された。

夏休み明けに続く。

前回のダイアログ設定状況は下図の通り。
 
＊出力セルの「Test」は現在のシート名。

上記の設定でボタンを実行し取得・表示されたデータは、
元データの型名（数値、文字など）が保持されている。
     

今度は省略した項目式範囲を設定して見る。
プルダウンリストに定義されている名前が表示されるが、いまは（セル参照）ボタンで任意のセル位置を指定する。

これまでと同様、項目式の範囲指定は、開始位置（左上）との思いから、一つのセルだけを指定しＯＫをクリック。

このまま特に（項目式作成）を行わず、ＯＫで設定を終了して実行すると、上図の出力データ１列目（年月日の項目名とデータ）が全てクリアされた・・・。
次に、表示されているデータを全てクリアしてから再度実行するも何も表示されない・・・。


この現象から、データが取得されない、いや正しくは取得されてはいるが表示されないことが判った。
つまり、項目式範囲を入力したときは必ず（項目式作成）を行わなければならないのだ。
知ってしまえば当然のことだが。

項目式範囲を入力後、（項目式作成）ボタンを開くと下図のように左に<元データ>の項目名が表示され、右の<項目式>に出力したいに項目名をセットすることがわかる。


ここで右のカーソル位置に注目すると、何故か１つ目の項目のマスが空欄だ。
そうか！　項目式範囲で指定したセルのマスだ。
指定したセルのマスはできたが、（項目式作成）を省略したので項目名が空欄のままとなり、結果として出力対象が無く何も表示されなかったのだ。
しかもこの空欄のマスは“空白”を出力するという動作を行うので、１列目がクリアされたのだ。

事情が分かったところで、右のカーソルを一番上に位置づけ、左の項目名を下にドラッグして全項目を選択し、右矢印で移送してＯＫボタンをクリックすると、項目式の範囲指定で入力したシートのセル位置を基準（左上）にデータ項目名と型名が展開・表示された。
（因みに右のカーソルが初期状態で項目名を右に移すと空欄のマスの下から移送される）

そして最初のダイアログ「BtMergeの作成」に戻ったので、ＯＫボタンで設定を終了して実行すると前回同様にデータが取得され表示されたのだった。


ここでまた遡って、項目式範囲を指定せず、先に（項目式作成）ボタンを開くとどうなるかを見てみた。
項目式作成ダイアログ右のカーソルは一番上にあり、左の項目を下にドラッグして選択し、右矢印で移送すると一番上から綺麗に設定され、ダイアログ上にもデータの型名が自動的に設定・表示された。
この状態でＯＫボタンをクリックすると、今度は項目式の出力位置を指定する画面が表示されたので、設定した項目数を確認して適切なセルの場所を選択できた。

こちらの流れの方が好きかも。

続く。