はじめに
初めまして。ATです。
私はexcelとAccessでデータ集計をする仕事をしています。
もっと効率よく集計するにはどうしたらいいかな？と考えることは仕事のうちでもあり、とても勉強にもなります。
今回は以下2つのシチュエーションにおいて、効率化する方法を考えたいと思います。
・個別のフォルダに格納された容量の重いcsvファイルをaccessに取り込んで集計したいとき
・複数選択したcsvファイルのデータを一つのファイルにすべて結合させたいとき
この記事の対象者
・VBAを勉強中の人
・集計作業に携わっている人
・業務効率化に興味のある人
開発環境
・microsoft excel,Accessなど、オフィスソフトがインストールされている
・excelマクロ、Aceesマクロが起動できる
個別のフォルダに格納された容量の重いcsvファイルをaccessに取り込んで集計したいとき
これは自分自身が仕事で体験したことなのですが、
・一日に一つ生成されるサイトのログをすべて結合して集計をする業務が定期的に発生する
・一日分だけでも容量が重くデータは圧縮ファイルに保存されていて下記画像のようになっている
・容量が重くexcelでの結合はできないので、accessにリンクテーブルとしてインポートさせたい
accessに一つ一つリンクテーブルでインポートする作業を毎回やるのが面倒だったので、以下のような操作手順の集計用ツールを作成しました。
★リンクテーブルとは、accessファイル外のデータをテーブルとして持つことができる機能です。
ツールの操作手順
<操作手順1>
マクロを登録したexcel図形をクリックすると、自動で対象フォルダの中のフォルダ全てをすべて毎月同じフォルダ名「01,02,03,04… …28,29,30」と連番でリネームする。
★excel図形にマクロを登録してボタンのように使うことができます。
<操作手順2>
同じくマクロを登録したexcel図形をクリックすると、ファイル名をすべて毎月同じファイル名「01,02,03,04… …28,29,30」と連番でリネームする。
下記のようにあらかじめaccessにリンクテーブル名「Day01,Day02,Day03,Day04… …Day28,Day29,Day30」を作成しておきます。
「C:\Users\test\Desktop\勉強用データ\記事作成用\」の直下に、毎月同じフォルダ名、同じファイル名のデータを作成すればデータの中身は最新月で更新できます。
具体的な実装解説
<準備>
excelシートに変更前と変更後のフォルダ名、ファイル名を全て列挙させます。
エクセルのセル番地内の文字列を変数に格納して、フォルダ名、ファイル名をリネームさせるために用意しておきます。
<実装手順1>
フォルダ名リネーム自動化のために以下のようなマクロを作成しました。
今回はワークシート内のフォルダ名を1行づつ変数に代入して、ワークシート最終行までループさせる方法を使いました。
<手順手順3>
次はファイル名リネーム自動化です。以下のようなマクロを作成しました。
赤線の中で
"C:\Users\test\Desktop\勉強用データ\記事作成用\1\FEI_PREF_yyyymmdd.csv" から、
"C:\Users\test\Desktop\勉強用データ\記事作成用\1\Daydd.csv"
にリネームして、これをワークシート最終行までループさせています。
これでフォルダ名、ファイル名ともにリネームできて、accessへのインポート作業がだいぶ楽になりました。
最初だけ「01,02,03,04… …28,29,30」のファイル名をリンクテーブルとしてすべてインポートしないといけないので、手間がかかってしまうところが残念です…。
accessへのリンクテーブルインポートもマクロで一括でやる方法を考えたのですが、複数のcsvファイル(容量重いのでリンクテーブル)を一括でインポートするマクロを作ることがどうしても難しくできなかったです…(汗)
今後の自分の課題として、取り組んでいけたらと思います。
複数のcsvファイルのデータを一つのcsvファイル(容量軽め)にすべて結合させたいとき
容量が軽いデータであれば、集計したいログをすべてワンシートにまとめることができるかもしれません。
ツールの操作手順
今回はマクロを登録したexcel図形を一回クリックするだけです。
具体的な実装解説
このようなコードを作成しました。
ここでは先ほどのようにワークシートに列挙したセルの文字列を変数に代入するのではなく、選択したファイル名そのものをVariant型というどんなデータ型でも格納できる変数型に格納しています。そしてVariant型変数を配列に格納してファイルの中のデータをワークシートに出力する方法を使っています。
先ほどはオブジェクト型に宣言していたファイルもVariant型で宣言可能です。
このVariant型を使うと配列に格納してもエラーにならず楽に操作できました。
個人的には新規でファイル作成したほうが扱いやすいので新規作成して保存しましたが、excelの別シートの出力なども可能かと思います。
今回学んだこと
・VBAではフォルダ、ワークシート、セルはオブジェクト型
VBAではフォルダはオブジェクト型というデータ型です。
オブジェクト型にはワークブック、ワークシート、セルを格納できます。
１．オブジェクト型変数として宣言する
２．オブジェクト型変数として宣言した変数をワークシート最終行までループさせる
この部分が難しくて苦戦しました(-_-;)
正直まだオブジェクト型について詳しく説明できるほどではないので頑張ります…。
・マクロの不便な部分も結構多い。
(1)配列の中に配列を格納できないので、複雑な自動化を実装したいときは苦労しそう
(2)オブジェクト型変数にはsetステートメントを宣言しないとエラーになる
一応VBAの資格を取得しているのですが、基本的なVBAの知識を知らなかった・もしくは忘れていたりして自分の知識不足を痛感しました。
今回作成したマクロもオリジナルではなく、すべてネットで探してきたコードを自分で少しアレンジしたものです。参考にさせていただいたサイトは参考文献に掲載しています。
最後に
ちょっと不便なところもありますが、excel、accessを仕事で使っている者としてはVBAは好きな言語のひとつです。
これからも勉強を続けていきたいと思います。
Pythonで業務効率化する技があることも最近よく聞きます。
VBAだけにこだわらず、他の言語もやってみたいと思いました。
拙い説明になってしまいましたが、ここまで読んでいただきありがとうございました。
参考文献
フォルダ名一括変更
https://fastclassinfo.com/entry/vba_change_folder_and_file_name/
ファイル名一括変更
https://www.helpforest.com/excel/emv_sample/ex100016.htm#google_vignette
オブジェクト型、Variant型について
https://excel-ubara.com/excelvba1/EXCELVBA352.html
for Eachの制御変数について
http://officetanaka.net/excel/vba/file/file02.htm
for eachステートメントの中にArray関数で宣言した配列を入れる方法
https://news.mynavi.jp/techplus/article/businesshack-13/
Array関数
https://www.officepro.jp/excelvba/array/index6.html
名前を付けて保存
https://www.tipsfound.com/vba/11004-vba
excel図形にマクロを登録する方法
https://office-hack.com/excel/macro-button/
access リンクテーブルとは何か
https://www.microsoft-access.net/file-tips/filetips-1.html