別ファイルの値を、「そのファイルを開かずに」コピー

質問

いつもありがとうございます。

エクセルVBAにて、「同一フォルダ内にある別ファイルの値を、『そのファイルを開かずに』
コピーする」やり方をいろいろ調べているのですが、なかなか思うようにいきません。

現状、こんな感じです。

-----------------------------------------------------------------------

　　For i = 4 To 2000

Sheets(Lm).Cells(i, 14).FormulaR1C1 = "='[" & Base & ".xls]" & St & "'!RC[24]"

Next i

※変数"Base"には参照したいファイル名、"St"及び"Lm"にはシート名が入っています

-----------------------------------------------------------------------

　これで行いたい処理はできているのですが、2000行近くあるため、職場の低CPUな
PCでは処理に時間がかかってしまいます。

　これを、（たとえばcopyメソッドなどを使って？）もっと速く処理できる方法はないでしょうか？

　以上、ご教示のほど、よろしくお願いいたします。

回答No.2

keithin · ベストアンサー

sub macro1()
worksheets(Lm).range("N4:N2000").formulaR1C1 = "='c:\フォルダ\[" & base & ".xls] & st & "'!RC[24]"
end sub

などのようにしてみます。
少なくともセルを一個一個嘗め回してその都度外部参照させるようなマクロにしない工夫をしてみると，改善があるかもしれません。
高速化が目的なら，きちんと開いてシンプルにコピーしてきた方がずっと早い事も十分考えられます。実際にマクロを組んで，実験してみて事実を確認してみてください。

またセルを一度に2000個も本当に参照する必要があるのか？といった部分はどうなんでしょうか。

＃ご質問に掲示された今のマクロは「閉じたブックを参照する」になっていませんが，てきとーに削って掲示したせいかもしれません？
＃閉じたCSVに対して外部参照式で値を取ってこさせる方法はありません。

お礼

ご回答ありがとうございました。
上手くいきました。

あとは、「きちんと開いてシンプルにコピー」とどちらが速いかなど
いろいろ実験してみます。

あるがとうございました。

回答No.3

hallo-2007

No2の方もおっしゃるように
バックグラウンドでファイル内のデータをひっぱて来ることは可能ですが
この方法はメモリが少ないPCでは逆に遅くなることもあります。

ファイルを開かずに値を持ってくることは不可能です。
2000ものファイルをどの頻度で操作するのでしょうか？
データが多くのファイルに分散してしまう運用を見直したほうが良いです。
一般的な発想ですが
それぞれのファイルでデータを操作したら、その内容を
データベースに蓄積される
集計の際は、データベースから必要な部分を取り出して処理する。
といったことを考えます。

お礼

ご回答、ありがとうございました。

回答No.1

vaidurya

VBAとかEXCELの詳しい使い方は知りませんが…
コンピューター処理の常識で考えると
一つはシートの保存場所を、HDDではなく
RAMディスクやSSDにおくことが効果的じゃないかと思います。

それが予算的にできないのであれば
シートをCSV形式に変換して保存するといった方法で
まったく違う手段でアクセスするほうが高速かもしれません。

一度、その処理中にタスクマネージャーでモニタリングして
CPU負荷とHDDアクセスとメモリー消費などの推移から
速度低下の原因を特定すべきかもしれません。

たとえば最初のアプローチはHDD待ちを解消するもので
後のアプローチは、メモリー消費とCPU負荷に効果があるかもしれません。

ただ、VBAからEXCELのシートをアクセスするより
CSVファイルへのアクセスのほうが、難しいかもしれません。
また必ず高速化できるとも言えません。
(VBAのEXCELシート参照機能が充分に最適化されている場合など)

お礼

まったく違う視点からのご回答、ありがとうございました。