Home Excel2003：豆知識menu Excel2010：豆知識menu ピボットテーブルmenu サイト内FAQ

データの取り込みと集計　（手作業での操作例と、それをVBAで。）

■ Excel豆知識53 





53-4 そのまま取り込めるデータを毎月1回取り込み作業を行う（VBA）
データのパターン 

「②のデータでAのタイミング」のパターンでの手作業の手順をひと通りやってみましたので、
次はそれをVBAにしてみます。

①、②、③について、AとBの両方のタイミングでの取込みをVBAで考えてみることにします。



下図のように、「yyyymmData.CSV」という名前の月ごとのデータのファイル（データ形式は毎回同じ）
が出力されて、それが取込用のマクロブック（赤枠）と同じ場所にある「CSV保存用」フォルダ（緑枠）
内に保存されている、という設定です。
つまりピンク枠の「mame53_1a」というフォルダの中に、取込用のマクロブックとCSV保存用フォルダ
が置いてある、ということになります。（取り込みたいファイル以外のCSVファイルは「CSV保存用」
フォルダには置いていない、という前提です。）

VBAでファイルを開くときにフルパスが必要になりますが、こういう配置にしておくと
「ThisWorkbook.Path & "\CSV保存用\" &  "yyyymmData.csv"」のように、マクロコードのある
ブックのPathを基準に利用できますので便利です。
マクロブックとデータフォルダの配置
マクロブックとデータフォルダの配置 

手作業の時は自分でこのCSVファイルを開いてデータをコピーしましたが、VBAでやるなら、ボタンクリ
ックで年月の数字を入れれば後は全部自動的に行いたいと思います。自動的にやるためには、「開こう
と思ったファイルがなくてエラーでマクロが中断する」などということを防ぐ工夫も必要です。

53-1に「ピボットテーブルは、最初の１か月分のデータを取りこんだ時に作成して、あとは取りこみ時に
更新だけをするようにしたいと思います。VBAでピボットテーブルを作成しても良いのですが、結構大変
なので、既に作ってあるピボットを更新する方法にします。」と書きましたので、データの取り込みが初
回の場合だけは、そのあとピボットテーブルを作成してくださいというメッセージも出したいと思います。


ということで、手作業で行ったのと同じ組み合わせの
「②の何らかの抽出を行って取り込むデータ」を、「Aの毎月1回取り込み作業を行う」場合のVBAの
手順を文字にすれば、
1.  取込が初回かどうかを判断する。	
2.  インプットボックスで、対象年月西暦を6桁の数字で入力してもらう。
3.  2の数字を利用してCSVファイル名を変数に取得。
4.  そのファイルの存在を確認。
       存在しない場合にはメッセージを出してマクロを終了。
5.  そのファイルのデータが既に取り込まれていないかどうかを確認。
　 　　取り込み済なら、メッセージを出してマクロを終了。
6. 「取込ファイル名」シートにファイル名を書き込む。
7.　該当するCSVファイルを開き、フィルタオプションで欲しいデータを「取込用」シートに抽出。
8.　CSVファイルを閉じる。
9.  データを貯め込んでいる「Data」シートにデータを追加し、データ範囲に名前を設定。
10.「取込用」シートは項目行だけを残してデータをDelete。	
11. 1で初回と判断した場合には、ピボットテーブルを作成してねとメッセージを表示。
　  　 初回でなければ、ピボットテーブルを更新。
12. 上書き保存
13. ピボットシートを選択してマクロを終了。
こんな風に、結構いろいろやっておいたほうが良いことが沢山あります。
だから、コードも長くなります。^^;

それに対して、「①のそのまま取り込めるデータ」を「Bの複数のファイルをまとめて取り込む」場合の
VBAの手順を文字にすれば、
1. 「CSV保存用」フォルダにあるCSVファイルを、そこにあるだけ以下の操作を繰り返す。
2.  CSVファイルを開く。
3.  データを貯め込んでいる「Data」シートにデータを追加する。
4. 「取込ファイル名」シートに取り込んだファイルの名前を追加する。
5.  CSVファイルを閉じる。
6. 「CSV保存用」フォルダにある全部のCSVファイルに対する処理が終わったらマクロを終了。
というふうに、いろいろな確認作業がない分、また、抽出作業も必要がない分、上のケースよりだいぶ簡
単です。
また、複数のファイルをまとめて取り込む場合には、ピボットテーブルは1回作ればいいだけなので、手作
業で行います。なのでピボットテーブルを更新するコードも入れません。

コードの長さで並べれば、
②-A　>　①-A　>　②-B　>　①-B ということになります。
コードが短い順にやろうかとも思いましたが、とりあえず①-Aから始めたいと思います。
①-A、②-A、①-B、②-B、　の組み合わせの順でやってみます。その後で③についても。
		
なお、カレンダー表作成の豆知識で、「この表の作り方について説明をしながら、エクセルのいろいろな
機能についても書いてみたいと思います。」ということでいろいろ回り道をしたのと同じように、今回も
あちこち寄り道をしながら、のんびりやってみたいと思います。
あくまでも、「私がやるとしたら」という初歩的なレベルのものです。
そして、「プロの方に作ってもらうのではなく、職場で自分たちでExcelVBAを利用する場合」というのを
想定していますので、エラー処理も、ある程度のものだけにしてあります。
（私の場合は、「エラーが出たら呼んでね～。」とマクロを登録したボタンのあたりに書いておきます。^^;　）


では、①-Aの組み合わせから始めます。
でも、その前にちょっと一言。^^;	
				
上でVBAでの手順を文字にして書きましたが、コードを書きはじめる前にこれが出来ているわけではあり
ません。出来上がったコードを見て書いたものです。
実際には、			
7.  該当するCSVファイルを開き、フィルタオプションで欲しいデータを「取込用」シートに抽出。
9.  データを貯め込んでいる「Data」シートにデータを追加し、データ範囲に名前を設定。
このあたりの、肝心な部分から書いていって、その後でいろいろテストしてみて、これも追加したほうが
いいな、あっ、これも欲しいな、というような感じで付け加わっていって、出来上がります。出来上がっ
た後も、また、あっ、これも・・とかいうのは日常茶飯事です。^^;	

自分だけで使う時には肝心な部分だけをVBAにして後は手作業で、っていうのはよくありますが、他の人
に使ってもらう時には、ある程度親切なコードにしようかな、とは思っています。

では、操作の中心になる部分から始めます。
				
①手を加えずにそのまま取りこめるデータ
項目行を除いたデータ範囲をコピーして、最終行の次の行に貼り付け 

CSVデータの項目行を除いたデータ範囲をコピーして、取りこみたいブックの「Data」シートに貼り付
けます。上図は初めてデータを取りこんだ場合の図ですが、次月からはこの貼り付けたデータのすぐ下
の行に続けて取りこみます。

続けて取りこみますので、元データの項目行を除いた範囲をコピーしてきたいです。
ということで、まず最初はデータ範囲の取得の仕方です。

データ中のセルを一つ選択した状態で、Ctrlキーを押しながらテンキーの*（アスタリスク）を押すと、
下図のようにA1セルがアクティブセルになってデータ範囲全体が選択された状態になります。
Ctrlキーを押しながらテンキーの*（アスタリスク）を押してデータ範囲全体を選択 

「A1セルを選択して、Ctrlキーを押しながらテンキーの*を押す」操作を「マクロの記録」した結果が
下図のコードです。
「A1セルを選択して、Ctrlキーを押しながらテンキーの*を押す」操作をマクロの記録 

Range("A1").Select
Selection.CurrentRegion.Select
これは、
SelectとSelection を省略して 

SelectとSelectionを省略して			
Range("A1").CurrentRegion.Select			
という１行にすることができます。
SelectとSelection を省略して 

マクロの記録をすると、Select とか Selection　が記録されることが多いです。手作業で行うものを記
録するのですから、セルを選択して、その選択したセル範囲等に対する処理、という記録のされ方をしま
す。手作業では、何かをしようと思ったら、対象になるものを選択することが必要ですが、VBAではそれ
が不要なことが多いです。
私が初めてVBAの質問掲示板に質問をした時に、
記録したマクロをカスタマイズする際には、「特に必要のない限り、Selectする必要はない」ことを意識すると
いいと思います。（あまりSelectしない方がよいと思います。）
また、省略可能なオプションは初期値を変更する場合以外は省略しても構いません。
と教えていただきました。記録されたコードにはSelect以外にも省略できる余分なものが結構あります。最初はどれを
省略したらよいのか見当もつきませんでしたが、数をこなしていくうちに段々理解できるようになりました。記録され
たコードの、省略が出来そうなものを省略してみて、実行してみて大丈夫かどうかを確認する、ということはだいぶや
りました。^^; 

このRange("A1").CurrentRegionでA1セルから繋がったセル範囲を取得できます。
Range.CurrentRegion プロパティ 

Sub test()
    Range("A1").CurrentRegion.Select
End Sub

このコードを実行すると、下図のように、アクティブになっているブックのアクティブになっているシー
トのA1セルから繋がったセル範囲が選択されます。
（ここからは、説明用にデータの少ないサンプルを用います。）
A1セルから繋がったセル範囲が選択 

ここから１行目の項目行を除くためにはOffsetを使います。
Offsetのヘルプには下図のようにあります。（分かりにくいので、こちらに少し説明を。）
Range.Offset プロパティ 

列は動かさずに、行を１行だけ下方向へずらした範囲を取得したいので
Range("A1").CurrentRegion.Offset(1)
とします。

Sub test()
    Range("A1").CurrentRegion.Offset(1).Select
End Sub

これを実行すると、下図のように、アクティブになっているブックのアクティブになっているシートの
Range("A1").CurrentRegionから１行下がった範囲が選択されます。
データがある範囲だけを選択するようにしてもいいですが、前月データの下に追加するだけですので、
下図のように選択したものをコピー貼り付けしても大丈夫です。
今回はこのまま使おうと思います。


なお、先ほどから「アクティブになっているブックのアクティブになっているシートの」というまだ
るっこしい書き方をしていますが、コードが「Range("A1")」　から始まっていて、どのブックのど
のシートのA1セルなのかを指定していません。ブックやシートの指定を省略した場合には、「アクテ
ィブになっているブックのアクティブになっているシート」に対してということが省略されていると
いう解釈になります。複数のブックを開いている場合や、複数のシートがある場合に、こういう
「ブックやシートの指定を省略した書き方をしたコード」を実行する際には注意が必要です。
これ以降は、このまだるっこしい書き方は省略します。

上図の範囲をコピーするわけですが、別にセル範囲をSelectする必要はありません。

Sub test()
    Range("A1").CurrentRegion.Offset(1).Copy
End Sub

このコードを実行すると、下図のようにRange("A1").CurrentRegion.Offset(1)の範囲をコピーできます。
（アクティブセルはF13にありますから、選択していなくても大丈夫なのがわかると思います。）


今度はそれをデータを貯め込むシートに貼り付けようと思います。


この状態から、「貼付先Sample.xlsm」を選択して、マクロの記録を開始して、
DataシートのA2セルを選択して、貼り付けし、Escキーを押してコピーモードを解除すると、
下図のコードが記録されます。

Range("A2").Select
ActiveSheet.Paste
ここまでで下図の状態に、

Application.CutCopyMode = False
これでコピーモードが解除されて、下図の状態になります。


上では貼り付け先を選択して貼り付けましたが、VBAなら選択しなくても大丈夫です。
コピーしたものを、どこを先頭に貼り付けるかを指定してやります。


ただ、「どのブックのどのシートのどのセルに対する処理なの？」というのをはっきりさせてやる必要
があります。

今回は、取りこみ先のブックに取込用のVBAコードを置きたいと思いますので、
「貼付先Sample.xlsm」がThisworkbookになります。


Sub test()
    Workbooks("Sample.csv").Worksheets(1).Range("A1").CurrentRegion.Offset(1).Copy _
                        Destination:=ThisWorkbook.Worksheets("Data").Range("A2")
End Sub

これで「Sample.csv」の１番目のシートのA1セルから繋がった範囲を１行下へ下げた範囲をコピーして
マクロコードのあるブックの「Data」シートのA2セルを先頭に貼り付けてね。ということになります。
なお、上の色のついたコード２行は、１行目の終わりに「半角スペースとアンダーバーを入力して改行」することで、
長い１行を２行に分けて表示したものです。

下図赤丸のように、SubとEnd Sub　の間にカーソルを置いて、「Sub/ユーザーフォームの実行」ボタンを
クリックします。（または、F5キーを押します。）


それを実行すると下図のようになります。メッセージボックスは、このタイミングの図だよ、ということを示したくて
入れたもので、実際の作業のコードではここにこれは入れません。


今度は次の月のデータを貼り付けてみます。
貼付先のデータの最終行の次の行（赤丸位置）に貼り付けるために、最終行が何行目かを求めます。


（最終行の求め方はこちら。）

Option Explicit

Sub 取込test()
    Dim LastRow As Long

    With ThisWorkbook.Worksheets("Data")
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Workbooks("Sample2.csv").Worksheets(1).Range("A1").CurrentRegion.Offset(1).Copy _
                Destination:=.Cells(LastRow + 1, 1)
    End With
End Sub


何だか急にごちゃごちゃしてきたような感じですね。^^;

ということで、この辺でちょっと一呼吸置いて、変数とか繰り返し処理とか基本的なことについて、
ちょこっとしたサンプルを使ってやってみようと思います。

VBAは奥が深いので、私が説明できることはほんの少しですが、一応豆知識53でやってみるコードに
出てくるものに関しては、ある程度書いてみたいなと思っています。
ページTOPへ
Home Excel2010：豆知識menu ピボットテーブルmenu BACK NEXT