こんにちは。
田中です。

※あくまで考え方の一例なので、これが正解不正解というものではありません。
※もっといいプログラムの組み方があるかもしれませんので、参考程度に。

事象

基本的には元データ1つにつき、集計結果ファイルは1つ。
けれども時には、Excelにはシートがあり、シートごとに結果を記載する場合もある。

例を挙げると、1つの商品に対して、年齢層ごとにデータをまとめるとして。
・結果ファイルは「商品A_年齢層.xlsx」
・1つの地域につき1シート
みたいな感じする場合というところ。

考え方

○基本的な1つのデータにつき、結果ファイルが１つの場合

Sub Sample1 For 元データ resultFile = 元データをもとに結果ファイル名を作成 resultFileを作成 resultFileを開く 　　resultFileに元データの内容を記載 　　resultFileを閉じる Next End Sub

こういうロジックになるでしょうか？
これをもとに複数バージョンにしたのが下。

○結果ファイルを全ファイル開いて、元データを一件ずつ見ては該当するシートに記載していくパターン

Sub Sample2
For 元データ
　resultFile = 元データをもとに結果ファイル名を作成

　If 結果ファイルがない　then
　　resultFileを作成
　　resultFileを開く
　End If
　resultFileをアクティブ
　該当するシートをアクティブ
　resultFileに元データの内容を記載
Next

開いているresultFileをすべて閉じる

End Sub

結果ファイルがあればそれをアクティブにして、なければを作っては開いたままにして該当したファイルごとに記載していく。

欠点：メモリをとにかく喰う!!
resultFileを開きっぱなしにするわけだから、当たり前といえば当たり前。
開いているファイルの数とか、開くファイルが大きかったりすると致命的。
「メモリが足りません」エラーが出て、元データのファイルが開かない、ファイルが保存できないなど障害が起きる。
例でいうところの地域が都道府県別だったりしたら48シート分…

ということで、次っ!!

今度は、元データごとに該当するresultFileを開いては閉じてというする方法。
これでメモリの問題は解決する。
もちろん、完璧なプログラムなど存在しない。
欠点：処理時間がかかる
ただ、数が少なければ無難な方法。

Sub Sample4
For 元データ
　resultFileObject作成
　Key:resultFile　value:該当する元データ
　※元データが複数の場合、私は"*"とかで区切って一つの文字列にしてしまったりする
Next

For resultFileObjectのKey
　resultFileを作成
　※ここで"*"で区切ったものをばらす

　For 元データ(resultFilesObjectのValue)
　　該当するシートをアクティブ
　　元データの内容を記載
　Next

　resultFileを閉じる
Next
End Sub

resultFileを作る前に、一度該当するファイルをまとめて見ました！
これなら、結果ファイルを開くは一度だけ。

結果的にこのやり方が処理時間が短く、なんとかメモリも範囲内に収まった模様。

ちなみに、Sample4で処理が止まったときの原因の一因について記載する。
・エラーもなく無言で処理が終わる
→ワークブックの数十回以上の開閉
→クリップボードのデータ量が多い

結果的に処理速度、メモリのことを考えると、ひとつひとつファイルを閉じて開いてを繰り返したほうがよさそう。

The following two tabs change content below.

• この記事を書いた人
• 最新の記事

tanaka_k1968

最新記事 by tanaka_k1968 (全て見る)

• 保育園をどうするか。 - 2024年8月29日
• [Kintone]Javascriptでカスタマイズすると日時がズレる問題 - 2024年7月31日
• [VBA]複数のブックの中にある各シートにデータを記載する - 2024年6月25日