別のExcelファイルからデータを取得するマクロ

※領収書たちのフォルダ　by Panasonic GH5+25mmF1.7

Excelで事務仕事をしていると、別のExcelファイルからデータを取ってくることはありませんか？

1つのファイルからデータを取ってくるならストレスを感じることはないと思いますが、従業員分のExelファイルから取ってこないといけない場合、大変ですよね。

そういったときは、マクロを使って自動化してしまいましょう。

別ファイルからデータを取得するマクロ

縦に従業員、横に日付、データは勤務時間というふうにまとめたいとします。

このとき、別ファイルからデータを取得するマクロはこちらです。

1Sub GetDataFromWorkbook()
2    For x = 2 To 3
3        ' パス
4        filePath = ThisWorkbook.Path
5        
6        ' ブック名
7        BookName = "time-" & ThisWorkbook.Worksheets("Sheet1").Cells(x, 1) & ".xlsx"
8
9        ' シート名
10        SheetName = "Sheet1"
11
12        ' セル
13        data = Cells(4, 5).Address
14        
15        '数式で別ブックを参照
16        ThisWorkbook.Worksheets("Sheet1").Cells(x, 2) = "='" & filePath & "/[" & BookName & "]" & SheetName & " '!" & data
17        
18        ' 数式を値へ変換
19        ThisWorkbook.Worksheets("Sheet1").Cells(x, 2).Value = ThisWorkbook.Worksheets("Sheet1").Cells(x, 2).Value
20            
21        ' 時間表示の書式を指定してください
22        ThisWorkbook.Worksheets("Sheet1").Cells(x, 2).NumberFormat = "hh:mm:ss"
23    Next
24End Sub

今回のマクロは、従業員それぞれが自分の勤務時間をExcelにまとめていて、従業員全員の勤務時間を1つのExcelファイルにまとめる用のマクロです。

1日分しかデータを取ってきていないので、1ヶ月分まとめて取りたい場合はFor文などを使って修正する必要があります。

それではそれぞれ解説します。

For文で従業員全員のExcelファイルを参照

For x = 2 To 3

ここのForは、xを2から3まで繰り返す処理です。

7行目の「.Cells(x, 1)」でxを使っています。

xは2から3まで動くので、

• .Cells(2, 1)
• .Cells(3, 1)

を参照しているのがわかります。

ここのセルは、従業員のExcelファイル名に相当するので、従業員の数に応じて増やしてください。

従業員のExcelファイル名取得

1' ブック名
2BookName = "time-" & ThisWorkbook.Worksheets("Sheet1").Cells(x, 1) & ".xlsx"

従業員のまとめているファイルを仮に「time-123.xlsx」とします。

「123」は従業員番号や名前でOK

Excelファイル名を取得して、BookNameに格納します。

シート名の取得

1' シート名
2SheetName = "Sheet1"

シート名を取得します。

Excelファイルを新たに作った場合、「Sheet1」となり、そのまま使用している方が多いのでSheet1にしています。

もし、別のシート名にしているのであれば変更してください。

データがどこにあるか指定する

1' セル
2data = Cells(4, 5).Address

取得したいデータがどこにあるか指定します。

4がx座標で縦方向、5がy座標で横方向です。

ここを可変にすると、データ取得の自由度があがります。

データを取得する方法

1'数式で別ブックを参照
2ThisWorkbook.Worksheets("Sheet1").Cells(x, 2) = "='" & filePath & "/[" & BookName & "]" & SheetName & " '!" & data

データを取ってくるため、、セル内に他Excelファイルを参照する式を使っています。

他の方法もありますが、式を使ったほうが速いので、こういうやり方になりました。

式を値に変換

1' 数式を値へ変換
2ThisWorkbook.Worksheets("Sheet1").Cells(x, 2).Value = ThisWorkbook.Worksheets("Sheet1").Cells(x, 2).Value

式のままではExcelファイルが削除されたり、修正されたりすると変わってしまうので、値に変換して変わらないようにしています。

時間表示になるよう書式設定

1' 時間表示の書式を指定してください
2ThisWorkbook.Worksheets("Sheet1").Cells(x, 2).NumberFormat = "hh:mm:ss"

取ってきたデータをわかりやすいよう、時間表示に変えています。

RangeではなくCellsを使う理由

.Range("A1") = "佐藤"
.Cells(1, 1) = "佐藤"

Cells()を初めて見た方も多いかも知れません。

上記のRangeとCellsは、まったく同じことをしています。

同じことをしていても、Rangeでは変数を使えないため、セルを動かすことができません。

しかし、Cellsの場合は変数を使えるので、縦方向・横方向を自由自在に動かすことができます。

これが、RangeではなくCellsを使う理由です。

大きなマクロを組むとき、必ずといっていいほど、ForやWhileといった動的なプログラミングをします。

大量のExcelファイルを読み込んだり、書き出したりするので、セルも合わせて動いてくれないと困るのでRangeを使いません。

今回の場合は、少ないのでRangeでも問題ありませんが、いつでもCellsを使う癖をつけておくと後々楽になりますよ。

できる大事典Ｅｘｃｅｌ　ＶＢＡ　２０１９／２０１６／２０１３＆Ｍｉｃｒｏｓｏｆｔ/インプレス/国本温子

posted with カエレバ

楽天市場

Amazon

Yahooショッピング

多くの別ファイルからデータを取得するならマクロを使おう

多くのファイルを操作しなきゃいけない場合、マクロを使って効率かしていきましょう。

• ファイル開く
• データコピー
• 画面切り替え
• 貼り付け
• 画面切り替え
• ファイル閉じる

この動作をファイル数分やらなきゃいけないのは、苦痛じゃないですか。

マクロでプログラミングさえしてしまえば、1クリックで終わってしまいます。

プログラミングの時間はかかりますが、その後ミスは無くなりますし、時間が空くので他のことに使えます。

同じことの繰り返しは、機械が得意とすることなのでまかせちゃって、考えることに時間を使いたいものです。

Sponsor link

Excel VBAを勉強するなら「できる大辞典」がおすすめです。

辞書なのでピンポイントで学ぶことができます。

大学時代、2016年版を購入して勉強していましたが相当使いました。

ネットだけでも勉強はできますが、本を読みながらVBAを勉強したい方は購入してください。

amazonで見てみる

ねこる

管理者

Mac / プログラミング / Excel / マクロ（VBA） / カメラ

調べたり、効率的なことが好きです。