Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 8.1 Pro : Excel 2013)
For i = 1 + 最終行
投稿日時: 18/09/13 17:42:53
投稿者: sakana20180912

アクティブなシートA列に、参照先のファイルを開かずにE列のデータだけを引っ張りたいです。
データの行は変動するので、データが入っている分だけ欲しいのですが。。
以下のコードからどのようにカスタマイズすれば良いか分かりませんSadT-T):
ご教示お願いします。
 
Sub BookNoOpenGet ( )
 Dim i As Long
 For i = 1 To 30
 
        Cells( i , 1 ) = ExecuteExcel4Macro( " 'D:\Users\....\....\ [ ABC.xls ] Sheet1 ' ! R" & i & "C5" )
 
 Next i
End Sub
 
因みに、上記を実行すると綺麗に取れます。

投稿日時: 18/09/13 18:06:32
投稿者: sakana20180912

件名訂正 m(__)m
(誤)For i = 1 + 最終行 => (正)For i = 1 To 最終行

回答
投稿日時: 18/09/13 18:25:57
投稿者: WinArrow
投稿者のウェブサイトに移動

なぜ
>参照先のファイルを開かずに
なんですか?
 
開けば、ループしなくても、簡単に複写することができるし、レスポンスもよいでしょう。
 
開いても、見えなくすることができます。
 

投稿日時: 18/09/14 07:12:27
投稿者: sakana20180912

WinArrowさん
 
おはようございます。
ご回答ありがとうございます。
 
実はですね、データを取得する参照先のファイルが複数ありまして。。
できれば処理の負荷を極力減らしたいがための「参照先を開かずに」なんです。
 
確かに、Openメソッドを使用し、以下の
ActiveWindow.Visible = False や、
Application.ScreenUpdating = False などを使えばいいのですが。。
 
開かないでできるのであればご教示いただきたいと思いました(^^;)
 

回答
投稿日時: 18/09/14 08:43:45
投稿者: WinArrow
投稿者のウェブサイトに移動

最終行となるデータ件数を
ループに入る前にい取得すればよいと思います。
 
参考コードを紹介します。
アレンジしたみて
 
Dim HK As String
HK = "'D:\TEST\[Book1.xls]Sheet1'!"
Debug.Print ExecuteExcel4Macro("CountA(" & HK & "R1C1:R5000C1)")

回答
投稿日時: 18/09/14 10:51:23
投稿者: WinArrow
投稿者のウェブサイトに移動

↑の参考コードは、途中に空白セルが存在しない前提です。
 
Excel4Macroでは、特定のセル位置を関数で指定できないので、
R1C1:R50000C1
のように指定せざるを得ません。
 
関数は、メモリ上にシートが読み込まれている状態で機能しますから、
メモリに展開されていない状態では、関数が使えません。
 
ADOを使用すると、他ブックのシート内の全件(又は、条件指定で)一挙に取得可能です。
 
 

回答
投稿日時: 18/09/14 11:33:53
投稿者: もこな2

便乗質問で、ちょっと分からないんですが、

sakana20180912 さんの引用:
処理の負荷を極力減らしたい
ということで、第一目標は素早い処理をすることと思われますが、わざわざ、ExecuteExcel4Macroを使ったり、ループ処理をするほうが処理の負荷大きくないんでしょうか?
 
実データがないので、テストしてないですが、参照先のファイルに数式がびっしり入っていて再計算にものすごく時間がかかるとかなら別でしょうけど(そうだとしても開く前に再計算を手動に変更で回避できるような・・)ExecuteExcel4Macroを使ってループさせるよりも、ブックを1つ開くほうが負荷は大きい(高い?)のでしょうか。
 
もちろん、片っ端から参照先ブックを開いてそのままであれば、メモリを圧迫しますから↓のように、用が済んだらさっさとブックは閉じるののが前提です。
 
Sub サンプル()
    Dim dstSH As Worksheet: Set dstSH = ActiveSheet

    With Workbook.Open("D:\TEST\Book1.xls").Worksheets("Sheet1")
        .Range("E1", .Cells(.Rows.Count, "E").End(xlUp)).Copy dstSH.Cells(1, "A")        
        .Parent.Close
    End With
End Sub

回答
投稿日時: 18/09/14 23:53:38
投稿者: simple

余談めきますが、ExecuteExcel4Macroを使った場合の速度についてコメントします。
 
"executeexcel4macro 速度"でGoogle検索して
トップに表示されるのが、以下のサイトです。
"ExecuteExcel4Macro を使った処理速度向上 – softwarekenshou"
https://softwarekenshou.wordpress.com/2015/08/08/executeexcel4macro-%E3%82%92%E4%BD%BF%E3%81%A3%E3%81%9F%E5%87%A6%E7%90%86%E9%80%9F%E5%BA%A6%E5%90%91%E4%B8%8A/
 
残念ながら、これはとてもミスリーディングな記事ですので、
注意喚起の意味でコメントしてみます。
 
このかたは、
・5つのブックの特定シートから、横に並んだ26個のセルを
 マクロのあるブックに順次取り出すという設例を使って

(a)5つのブックを順次、通常どおり開いて取得するケース
(b)executeexcel4macro を使うケース
を比較しています。
 
実測したところ、
> (a)計測結果:所要 3分11秒
> (b)計測結果:所要 0分7秒
> なんと! 27倍の処理速度を叩き出す事ができました。

としています。
 
しかし、コードをみると、なんと、
(a)では 一つのセルを取得する毎にブックの開閉を繰り返しています。
合計で 5 × 26 回も開閉しています。そんなアホな。
5回の開閉だけで十分なはずです。
 
executeexcel4macroは1つのセルしか取得できませんが、
普通に開けば、26個のセルをまとめてコピーペイストできるのに、
それを使っていないのです。
その代わりに、26回ブックを開閉させています。何という....。
 
これは、とても公平な比較とは言い難いですね。
 
私が実測したところでは、同じく26個の場合、
(a)は 3秒
(b)は 2秒
でした。
これは、仮に1000個の連続したセルをコピーする場合は、
(a)は 3秒
(b)は 23秒
と逆転します。
--------------------------------
セル範囲が狭く、しかも多数のファイルの時は、executeexcel4macroが
使える場面があるかもしれないが、
一般的な場面では、普通にファイルを開くのがオーソドックスな方法だと思います。
 
なお、上記のサイトのコードは、
>Cells(i, j) = ExecuteExcel4Macro(“‘” & myPath & “\[” & myFile & “]Sheet3’!R3C2:R3C27”)
> ’この一行に置き換え可能

などとしているが、これでは同一の値(R3C2の値)しかコピーしないはずですし、
・変数のタイプミスあり、(starTime)
・Dim myPath, myFile, buf As String などという間違った変数宣言あり、
・インデントもされていない
とミスだらけです。ネット上の記事は玉石混淆ですね。
("石"が検索トップにくるというのは不幸なことです。)
 
--------------------------------
ちなみに、別の解説記事では、
ブックを開かずにセル値を取得(ExecuteExcel4Macro,Excel.Application ...
https://excel-ubara.com/excelvba5/EXCELVBA242.html
こちらは適切な解説かなと思いました。
 
> 結論として、ExecuteExcel4Macroが有用なのは、
> ・ファイル数が極めて多い
> ・シート名が固定されている
> ・取得するセル数が限定的である事

とされています。
 
(なお、普通に Workbooks.Openするよりも、
Dim xls As New Excel.Application
として
Set wb = xls.Workbooks.Open(strDir & strFile)
としたほうが速度アップが図れるというのは、私には新鮮でした。)

回答
投稿日時: 18/09/15 08:23:34
投稿者: WinArrow
投稿者のウェブサイトに移動

simpleさん、
 
貴重な情報ありがとうございます。
 
Excel4Macroでは、1回のアクセスで1つのセルしか取得印かできないため、
どうしてもループ処理になってしまう。
また、Edn(xlUp)も使えないので、データ件数を取得するのに工夫が必要
など、
普通にブックを開いた方が早いのではないか?
と疑問に思いつつも、処理速度が速いというページを見て、
納得したわけではないが、そんなもんか?
テストする時間もないまま、
データ件数を取得する方法(限定的)を提案していました。
 
simpleさんの情報で納得しました。
 
ありがとうございました。

回答
投稿日時: 18/09/15 10:05:22
投稿者: もこな2

simpleさん解説ありがとうございます。
 
ExecuteExcel4Macroをつかったアプローチだと
 ・1セルずつしか取り出せない
 ・シート"名"が特定されている必要がある
などの条件があるけど、たくさんのファイルから数個ずつセルをコピーしたいといったことであれば、ブックを開くより負荷というか、処理速度が早いこともあるってことですね。
 
sakana20180912さん便乗質問失礼しました。

回答
投稿日時: 18/09/15 13:49:58
投稿者: simple

おふたかた どうもありがとうございました。質問者さんどうも失礼。
 
# なお、件のサイト記事について、少々きつく書いてしまったかもしれません。
# 世の中に発信されること自体はOKでしょうから。  割り引いてお読み下さい。

回答
投稿日時: 18/09/15 21:42:03
投稿者: 半平太

>ファイルを開かず
 
時々、見かけるトピックですね。
以前、他所の掲示板で、プロフェッショナルな回答者を中心に検討したことがあります。
 
その時は、ADO ・ 数式の埋め込み ・ それ例外 の順でした。
「ADO」と「数式の埋め込み」は僅差で、それ以外との差は1桁違っていました。
 
今回も当方の環境とプログラムで以下のテストしてみました。
 
ファイルサイズ250Kと6.5Mの2ファイルに対して、100個のセル値を取ってくるとの想定です。
ファイル数は3個のみでやりました。
 

 結果                   普通       New Excel.Applicationを噛ませる
                         ↓     ↓
  Fileサイズ             オープン   オープン   Macro4   数式 
  6.5M         平均         2.67       3.07    26.17   0.26 
               1個目        2.61       3.98    25.98   0.27 
               2個目        2.75       2.62    26.36   0.27 
               3個目        2.66       2.60    26.19   0.26 
                                                            
  250K         平均         0.59       0.94     1.35   0.02 
               1個目        0.55       2.20     1.37   0.03 
               2個目        0.61       0.32     1.34   0.02 
               3個目        0.59       0.30     1.34   0.02 

(1)やはり数式の埋め込みがダントツに早かったです。
 
(2)New Excel.Applicationの改善効果はファイルサイズが小さい時にしか現れなかったです。
  この方式は、どっちにしても、1回目は余分に時間を食います。
 
(3)ファイルサイズが大きいと、Macro4のパフォーマンスは信じられないくらい低いです。
  プログラムを見直しはしましたが、依然としてこの結果には自信がないです。
  いずれにしてもMacro4の値取得は、期待する程の効果はないです。
 
今回の質問では、結果はセルに入力されることになるので、数式の埋め込み方式も有力だと思います。
 
E列の最下行番号も、どこかのセルに以下の数式を埋めれば取得出来ますし・・
 E列(データ列)が数値なら
    どこかのセル.FormulaLocal = "=MATCH(32^8,'D:\Users\....\....\[ABC.xls]Sheet1!'!E:E)"
 
 E列(データ列)が文字なら
    どこかのセル.FormulaLocal = "=MATCH(REPT(""ーー"",20),'D:\Users\....\....\[ABC.xls]Sheet1!'!E:E)"

回答
投稿日時: 18/09/16 00:18:50
投稿者: K.Hiwasa
投稿者のウェブサイトに移動

こんばんは。
 
明確な根拠があるわけではなく、少し実験した上で思うところなんですが、
New Excel.Applicationの効果は、アプリが非表示になっているからではないでしょうか。
通常のOpenでも、直後にウィンドウを非表示にすれば少し速くなると思います。
 
ループ
   Set wb = Workbooks.Open(パス)
   wb.Windows(1).Visible = False
   ---処理---
   wb.Close False

回答
投稿日時: 18/09/17 20:57:35
投稿者: simple

別の実験結果を提供いただき、ありがとうございました。
また、公平な比較への助言ありがとうございました。
 
Excel4Macroを使うときには、
100個のセルというのが、既に多いケースなのかもしれませんね。
 
また、別プロセスのExcelを使用した時の効果は、
確かに自分が大きいときほど、効果はあるようです。
他のExcelを使うことの効果を私もやってみました。
5個のファイル(30KB程度)から1000個のセルをコピーペイスト。
(通常通り開いて処理)
 (画面更新や警告表示はむろん抑止しています)
 

(大きいファイル)                      平均値  指数
  18M   他のExcel利用せず       3.7578  3.7865  (100%)
                                3.8125          
                                3.7891          
        他のExcelを利用         3.4063  3.3750    89%
                                3.3438          
                                3.3750          
(小さいファイル)                               
144KB   他のExcel利用せず       2.9219  2.9453  (100%)
                                2.9219          
                                2.9922          
        他のExcelを利用         2.9219  2.8880    98%
                                2.8750          
                                2.8672          

 
いずれにしましても、情報提供ありがとうございました。私はこれで。

投稿日時: 18/09/18 16:11:36
投稿者: sakana20180912

皆様
 
回答が遅くなり失礼しましたm(__)m
 
解説が大変プロフェッショナルで理解するのに
時間がかかってしまいましたが皆様からの回答を合わせましたら
解決出来ました!
 
有難うございました(^O^)/