「このセルの値を複数のExcelファイルの同じセルに入れたい」
「複数のExcelファイルの同じセルの情報だけを一つのExcelにまとめたい」
など、よく依頼されます。
特に1つセルの情報を複数のExcelファイルに入れたいといった場合、
どういうことなのかと思って聞いてみると、
定型の書式を作って、それぞれに入力作業を行った後になって、
「この部分にこれを入れるのを忘れていた」
「作業後に、この部分に追記して欲しいと言われた」
などの事情でお願いしますとのこと。
しかも、修正する数が5000ファイル以上ということで、
こちらに依頼する前にも同じようなのがあったらしいのですが
その時は、手作業で１０日くらいかけて修正したそうで、
はっきり言って時間の無駄だと思いました。
一つのExcelファイルで処理を行う場合は、
Excel自体の関数を使ったほうが早い場合も多々ありますが、
さすがに大量のファイルを開いて、同じ処理を繰り返すとなると
VBAで書いたほうがはるかに簡単だし短時間で処理を
することもできます。
では今回の作業について詳細に内容を確認してみると、同じ書式のセルの値をとってくるだけではありませんでした。
前提条件として
・書式自体は10種類あり、それぞれシート名も違う。
・書式によって取得するセルが違う。
例えば
シートAの場合は、A1とB10とD20
シートBの場合は、A1とB12とC15
この値を集計するシートの2、3、4列目にそれぞれ入れる。
・集計するシートの1列目には、取得元のファイル名を入れる。
実際の作業
Excelファイルの保存場所とファイル名を取得
これについては、VBAで作業してもできますが、面倒なので
コマンドプロンプトから、取得します。
Excelファイルの保存先が「C:\test」だった場合、以下のコマンドを実行すると
C:\test>dir /s /b >ファイル名.txt
ファイル名.txtが作成され、その中にファイル名一覧が入っています。
C:\test\001 C:\test\002 C:\test\003 C:\test\001\Book1.xlsx C:\test\001\Book2.xlsx C:\test\001\Book3.xlsx C:\test\002\Book3.xlsx C:\test\002\Book5.xlsx C:\test\002\Book6.xlsx C:\test\003\Book7.xlsx C:\test\003\Book8.xlsx C:\test\003\Book9.xlsx ・・・
ここで取得したファイル名を、集計するエクセルのシートに貼り付けます。
VBAの準備
それでは、実際にVBAで処理を書いていこうと思いますが
その前に準備が必要です。
まずExcelファイルの上部にあるメニューから「ファイル」をクリックします。
左の一番下の「オプション」をクリックします。
「リボンのユーザ設定」から「開発」にチェックを入れて「OK」をクリックします。
これで、VBAを使用する準備ができました。
次回は、実際に作業を行っていきます。
