Access

【ACCESS VBA】Excelファイルを取り込んで必要な項目のみに整形したExcelファイルを出力する

今回は『ベースとなるExcelファイル(列項目可変)を加工して必要なフィールドだけに整形して新しいExcelファイルを作成する方法』をご紹介します。

なんでもAccessでする必要があるか?と言われそうですが…(;^_^A

背景としてはQlikSenseでエクスポートしたデータファイルを顧客毎に加工して出力するのが面倒だという課題がありました。

顧客によって出力する項目も様々だし、QlikSenseからダウンロードしたデータの項目も可変…。

何もかも可変なので変換ツールを作ってしまおう!というものです。

手順の概要

大まかな処理の手順は以下の通りです。

  1. 元となるExcelファイルをインポート
  2. インポートしたテーブルから各フィールド名をワークテーブルに格納
  3. 利用者にはワークテーブルのフィールド名リストに出力ON/OFFを指定させる
  4. 出力フラグがONのフィールドだけを取得するSQLを作成しレコードセットをEXCEL出力

各手順の解説

元となるExcelファイルをインポート

インポートする元ファイルの項目数や項目名は可変であるという前提なのでインポート先のテーブルは毎回新しく作成する作りにします。

よって、指定のインポート先テーブルが存在した場合は削除するというロジックを入れておきます。

 

▼IsTabel()関数は以下の記事を参考に▼

テーブル存在チェック
VBAでテーブルの存在チェックをする関数一時テーブル作成前、インポート前などあらかじめこれから作成する予定のテーブルが存在するかどうかチェックする際に使える関数です。コピペでどうぞ。...

実はこの時ちょっとした問題が発生しました。

Accessのインポートって、インポート元データの最初の8行位でその項目の型を判断するのです。

このため本来文字列でたまたま先頭8行が数字だった項目などがインポートエラーになってしまいました。

全て文字列で取り込めば問題ないのですが可変なので事前にテーブルを作成しておくわけにもいかず…。

結局元のExcelファイルの2行目(ヘッダー行の下)に全列ダミー文字列を挿入するという姑息なことをしました…。

インポートしたテーブルから各フィールド名をワークテーブルに格納

①フィールド名を格納するテーブルを事前に用意しておきます。

【TMPフィールド名リスト】テーブル

フィールド名 データ型 備考
ID 数値型 出力順がずれないようフィールドの順番をセット
フィールド名 テキスト型 インポートテーブルのフィールド名を格納
サンプルデータ テキスト型 1行目の生データをサンプルとして格納
出力フラグ Yes/No型 Outputファイルに必要な項目を指定するフラグ

②インポートしたテーブルの全フィールドのフィールド名を取得して【TMPフィールド名リスト】に格納。

ついでに、サンプルデータとして1行目のデータをセットする。

 

利用者にはワークテーブルのフィールド名リストに出力ON/OFFを指定させる

格納した項目を表示する画面を用意し、利用者に出力のON/OFFを指定させExcel出力します。

出力フラグがONのフィールドだけを取得するSQLを作成しレコードセットをEXCEL出力

 

このプログラムでは出力したExcelを保存するロジックを含みませんので、出力した利用者が目視確認を自分で保存する形になっています。

「全列文字列で保存されている数値を数値に変換…(A)」の処理は以下の記事を参考に…

EXCEL「数値が文字列として保存されています」をVBAで一気に数値変換する方法Excelの「数値が文字列として保存されています」警告を解消する方法を手動と自動でご紹介しています。コピペOKのサンプルプログラムをExcelとAccessでご用意しています。...

まとめ

使う人がいるかどうか不明ですが、可変フィールドのデータを取得して後処理をするシチュエーションはそこそこあるかと思います。

その際の参考になれば幸いです。

ABOUT ME
アズビーパートナーズ
アズビーパートナーズ
プログラマーと社内SEとしての経験を活かして、 情報システム部門のご相談を承ります。 得意な分野はAccessによる短納期開発、 BIツール(QlikSense/QlikView)の開発です。 現在はCMSを利用したホームページの作成にも力を入れています。
中小企業にこそ必要なセキュリティ対策

日々巧妙化する悪意ある攻撃。狙われるのは大企業だけではありません。

セキュリティ対策が追い付いていない中小企業は犯罪者の恰好の餌食です。

情報セキュリティ部門がない、詳しい人がいない…。

そんな時は全部まとめて外部委託して安心を手に入れる方法がベストです!

\セキュリティ対策をまとめて全部NTT西日本にお任せ!/


資料請求はこちらから