ブックを開かずデータを取得したい

Question

頻出質問で恐縮ですが、教えてください。
過去の質問や他のサイトでも調べたのですが、原因がわかりません。

ブックを開かずデータを取得したいと思い、以下のようなコードを作ってみました。

Sub tenki()
Dim i As Long, j As Long
For i = 3 To 1000
For j = 2 To 7
Cells(i, j) = _
ExecuteExcel4Macro("'C:[ファイル名.xlsm]転記'!R" & i & "C" & j)
Next j
Next i
End Sub

「ファイル名」というブック（転記元）の中にある「転記」という名前のシートのB3からG1000を、
転記先のシートB3からG1000に転記したいです。

他にも転記したいシートが複数あります。
転記元はB3からG1000で統一されていますが、
転記先はB3:G1000,H3:M1000,N3:S1000・・・とずれていきます。
転記元ファイルAのB1と転記先B1が一致、
転記元ファイルBのB1と転記先H1が一致、という感じです。

転記元
転記先

上記コードを実行してみたところ、毎回全く動かなくなります。
タスクマネージャーでエクセルを終了しなければなりません。
エラ〜メッセージは出ません。

どうかよろしくお願いいたします。

Comments

[Yasumichi]

デジカメか何かで撮った写真でしょうか？
パソコン内でスクリーンショットを撮影した方が余計な光が入らなくて見やすいと思うのですが。
Windows アクセサリーの Snipping Tool を使えば、切り取りたい部分も制御できます。

[Yasumichi]

あと、呼び出し先のマクロは開示できますか？

[o-k]

すみません。
スマホで撮影してアップしました。
次回からスクリーンショットをします。

[hatena19]

質問は編集できますので、次回からといわずに、スクリーンショットの画像に入れ替えてください。

あと、どのようにうまくいかないかの具体的な説明を追記してください。
エラーがでるならエラーメッセージを提示してください。

[o-k]

失礼しました。
画像を変更しました。

エラーにつきましては、特に表示はありません。
ただ、コードを実行すると実行中から動かなくなってしまいます。

よろしくお願いいたします。

[Yasumichi]

【事実誤認のため削除】

Answer 1

上記コードを実行してみたところ、毎回全く動かなくなります。
タスクマネージャーでエクセルを終了しなければなりません。
エラ〜メッセージは出ません。

ExecuteExcel4Macro は重い処理ですので、たぶん時間がかかりすぎて動いてないように見えるのだと思います。データ数6000件くらいになりますよね。

ブックを開かずにというのが高速化が目的なら、処理件数が多いときは逆に遅くなります。
普通にWorkbooks.Openメソッドでブックを開いてコピーしたほうがはるかに高速です。

どうしても開かずにというなら、外部参照を使う方法があります。

Sub tenki()
   Dim f As String
   f = "'c:\test[ファイル名.xlsm]転記'!B3"
   With Range("B3").Resize(1000 - 2, 7 - 1)
        .Formula = "=" & f
        .Value = .Value
   End With
End Sub

【EXCEL】他のブックへのリンク（外部参照）を作成する ｜ B-side Journal

他にADOを使う方法もあります。（私の経験上では件数か多いときはこれが一番高速）

ADOについて - Qiita

Microsoft Excel VBA - ADOを使ってExcelシートを読み出す方法

Comments

[o-k]

hatena19さん、ありがとうございました。
おかげさまで思い通りに動きました。

ちなみに、転記するファイルを増やすときは

Sub tenki()
Dim f,g,h,i As String
f = "'c:\test[ファイル名1.xlsm]転記'!B3"
With Range("B3").Resize(1000 - 2, 7 - 1)
.Formula = "=" & f
.Value = .Value
g = "'c:\test[ファイル名2.xlsm]転記'!B3"
With Range("B3").Resize(1000 - 2, 7 - 1)
.Formula = "=" & g
.Value = .Value
h= "'c:\test[ファイル名3.xlsm]転記'!B3"
With Range("B3").Resize(1000 - 2, 7 - 1)
.Formula = "=" & h
.Value = .Value
i= "'c:\test[ファイル名4.xlsm]転記'!B3"
With Range("B3").Resize(1000 - 2, 7 - 1)
.Formula = "=" & i
.Value = .Value
End With
End Sub

のような感じでよろしいでしょうか？
将来的に増えたり減ったりする可能性があるので、
増やし方も知っておきたいです。

[hatena19]

そのような感じで、[ファイル名1.xlsm] の部分のファイル名を変更すればOKです。
ただ、それだと、Range("B3")からの範囲に上書きしてますので、この部分も、Range("H3") 、Range("N3") というように変更するといいでしょう。

[o-k]

追加のアドバイスもありがとうございます。
無事動作いたしました。
この度はありがとうございました。

Answer 2

＞ブックを開かずデータを取得したい

「ファイル」を開かずにデータを取得することは不可能です。
提示されたプログラムは「ウィンドウ」は開かずに
「ファイル」を5988回開いて閉じてます。
時間がかかって当然です。
素直に普通に開きましょう。
「ウィンドウ」が開くのを画面表示しないようにするには、
Application.ScreenUpdating = False を使います。
http://officetanaka.net/excel/vba/speed/s1.htm

Comments

[o-k]

iruyasさん、ありがとうございます。
見えないところで大変な作業を指示してしまっていたのですね。
教えていただいたところを参考に勉強させていただきます。

[hatena19]

厳密には、ExecuteExcel4Macro は「ファイル」を開いて読みに行ってますが、ブックウィンドウは開いていないので、数件のデータの読み込みならブックウィンドウを開くより高速です。ただ、数件のデータならどちらにしても体感できる差はでないので、ブックを開かないでということに拘る必要性はほとんどないですね。

ブックを開かずにセル値を取得（ExecuteExcel4Macro,Excel.Application）｜VBAサンプル集
https://excel-ubara.com/excelvba5/EXCELVBA242.html

[o-k]

シートが複数あったので開く手間を省きたかったのですが、読み込むデータの数が多いと遅くなってしまうのですね。
今後気をつけます。