Excel VBAでテーブル内の情報取得とテーブルへのレコード追加

みなさんこんにちは。胡田です。

皆さんExcel使ってますよね。私も頻繁に使ってます。そして、仕事で「毎月更新するExcel」があります。いや、PowerBIでレポート作っていて必要な情報はそこにあるのでそれを見てもらえばいいのです。自動更新ですし。

でも、Excelファイルを更新する必要…がありまして……。毎月頑張っております。クエリを書いたりうまくフォーマットを改善などしてかなり効率的に処理できるようにしたのですが、次の一手を行うためにどうしてもExcel VBAが必要となり10年ぶりくらいに書きました。

Excelもずいぶん進化してテーブルなども定義できるようになっているのでそのへんも加味して処理しようと思ったところ、結構サンプルを探すのに苦労してしまったので未来の自分(また10年後くらい？)のためにいくつかサンプルコードを書いておこうと思います。

テーブル内の情報を取得

まずテーブル内の情報の取得方法。

サンプルのテーブルはこちらです。シート名、テーブル名、列名を使ってテーブル内の情報を取得します。

サンプルコードはこちら

きちんとすべて名前でアクセスしているのがポイントです。なお、For ~ Nextをつかっていて気持ちが悪いですが、Excelではforeach的なものを利用したときに順番が保証されないらしく(※ブログ情報であり動作未確認)このような方法で妥協しています。

テーブルにレコードを追加する方法について。サンプルコードは以下です。

これもすべて名前でアクセス…にしたかったのですがそれを実現しようとするとても大変そうだったので諦めました…。

そしてこの方法で列を追加するとテーブルの１番最後に列が追加される挙動になります。

サンプルのテーブルの状態から実行すると１行空の行ができてしまうので注意が必要です。予め消しておけばOKです。

今回の件、本当はFlowを使いたかったところです。残念ながら私が対象としたExcelファイルに対してはエラーとなってしまい、Flowを適用できなかったのでExcel VBAに逃げました。

参考になるところがあると幸いです。

(2020/11/02 追記) またしてもExcel VBAをいじることがあり、自分で自分のブログを参考にしたので、貼り付けておきます。テーブル内の該当セルをみつけてほかの列を更新してます。