↓↓↓動画の内容を文字起こししています↓↓↓
こんにちはExcel開発15年、セルネッツの竹本です。今回は「知らないと損、ブックを開かずデータ件数を取得」というテーマで解説をしていきます。
Excelファイルの各シートのデータの件数を知る場合、普通はVBAからオープン命令をかけて、ファイルを開いて、シートの最終行を求める。というやり方をすることが多いですが、重いファイルを開くときは非常に時間がかかってしまいます。
データの件数を一瞬で知る方法、ADO接続をする方法を紹介します。
サンプルで使うのは「2023年度顧客データ」という50MBのExcelのファイルです。1ファイル50MBは実際にはありえない重いサイズです。
どのくらいの速度で件数を知ることができるのかをお見せします。
これがファイルを開いたときのイメージです。12か月分、12シート50MBです。
デモをしていきます。
右側がVBAでファイルをオープンしてから件数を調べるやり方。左側はADO接続です。
まず遅いほうをやってみます。
まずオープン取得、3月。8秒かかりました。実際に開いて、開いたあとでシートの計算処理を求めています。
ADO接続では0秒です。100件ありました。ここに書いてある件数が実際のデータ件数です。見出しは1行ついているので、実際のレコード数ですが、3月が100件。
続いてADOから2月を調べます。21件。
そして1月を調べてみます。0秒です。圧倒的な速さ、違いがあることがわかると思います。
正式名称はActiveX Data Objectsといいます。データソース、データベースです。いろんなデータベースに接続する技術のことを言っていますが、具体的にはAccessだったりSQLServer、Oracleデータベースでも大丈夫です。
できることはデータを取得する、それからレコードを追記する、削除をする、更新する。一通りできますので、覚えておくとだいぶ幅が広がります。一度でもAccessを勉強されたかたなら触れた機会があると思います。SELECT句、FROM句、WHERE句などです。
SQL文を使いますので、覚えていただけると開発の幅が一気に広がります。頑張って勉強していただきたいと思います。
実際にソースを見ていきます。
まずオープンで取得するやり方です。ワークブックを開いて、開くまでに時間がかかります。開き終わるまでに8秒ぐらいです。
開いた瞬間にオートフィルター、絞り込みがあればいったん解除して最終行を求めるやり方をしています。
通常はこちらで良いのですが、ADOで取る場合は以下の感じになります。
まずインスタンスを作成、お約束の構文ですが、ここがADO接続で対象ブックに接続しているところです。
SELECT句が出てきました。セレクト、カウント、アスタリスクが全部無条件となりますので、シート名を指定してレコード数を取得したら後処理、クローズしてNothingして終わります。
ADO接続で取得したほうが圧倒的に速いので、大量データが入っているファイルが想定される場合は、ADO接続で取得できることを知っておくとよろしいかと思います。
ADO接続を使ってデータベースに接続しにいく準備としてひとつ必要になってくるのが、VBAのツールの参照設定です。
開発タブのここです。ここにチェックを入れていただく必要があります。
最後、まとめになります。
なぜデータ件数を知ることが重要なのか？ということですが、入力チェックの定番なんです。処理を実行する前に存在するデータがなければ処理はすることができません。
処理対象のデータがあるかないかを事前にチェックするのは一瞬が良いので、できるだけ速い速度でチェックをしてください。
そして活用メリット。ADOを使うとき、ブックのサイズが重いときは高速で取得できる方法を使ってください。オートフィルターが絞り込みでも大丈夫ですが、このデータを開いてみると時間がかかってしまいます。
3月はそのままプレーンなデータが絞り込みなしの状態ですが、2月は12行目が見えていて、23行目が見えています。データの行を非表示にしているんです。
この状態であったとしても、ADO接続ではテーブルの有効データ範囲で取得をするので、件数はバッチリ取ることができます。
歯抜けのある状態が1月ですが、実際は23件のレコードがあります。グレーに塗っているところはあえて歯抜けの状態にしています。
この状態でもADO接続でやった場合は有効データ範囲のレコード件数を持ってくるので、正しくデータ件数の取得ができています。
ということで、オートフィルターが絞り込みされている状態でも大丈夫ですし、行データ非表示の場合でもOKです。
総数記述もお見せしたようにそんなに多くはありません。幅が広がるので方法論として覚えておくと便利です。
前提としてはテーブル形式であることが前提になります。テーブル形式とはデータベースの構造です。見出しは1行ですよね。2行目以降が明細データ、レコードと呼ばれる構造になります。この形式になっている必要があります。
取得するのはあくまで見出しを除く明細レコードの件数なので、Excelの最終行で置き換えた場合は＋1をする必要があります。
そしてシート名を特定できることが前提条件になります。
以上の3つを満たしていれば重いファイルはこの方法で取得できます。
プログラムソースに関しては概要欄に記述しておきますので、ぜひ実験していただければと思います。
少しでも参考になったらぜひチャンネル登録・高評価をお願いします。
毎週金曜日の夜9時に投稿しています。ご視聴ありがとうございました。
タケモ塾では、今後も皆さんのVBA学習に役立つコンテンツを作成してまいります。
ブログ記事、Youtubeチャンネルのご質問・ご感想・ご要望などお気軽にお問合せください。
お問合せはこちらから ✉
タケモ塾運営：株式会社セルネッツ
