Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


4008 / 13646 ツリー ←次へ | 前へ→

【58859】再帰取得配列データの処理の方法 ON 08/11/14(金) 15:00 質問[未読]
【58861】Re:再帰取得配列データの処理の方法 kanabun 08/11/14(金) 16:16 発言[未読]
【58863】Re:再帰取得配列データの処理の方法 ON 08/11/14(金) 17:55 質問[未読]
【58864】Re:再帰取得配列データの処理の方法 kanabun 08/11/14(金) 18:13 発言[未読]
【58902】Re:再帰取得配列データの処理の方法 ON 08/11/17(月) 15:09 質問[未読]
【58904】Re:再帰取得配列データの処理の方法 kanabun 08/11/17(月) 15:46 発言[未読]
【58906】Re:再帰取得配列データの処理の方法 ON 08/11/17(月) 18:07 お礼[未読]

【58859】再帰取得配列データの処理の方法
質問  ON  - 08/11/14(金) 15:00 -

引用なし
パスワード
   こんにちは よろしくお願い致します

APIを使用したファイルの高速検索処理
▼【58198】再帰処理でのファイル検索
tp://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=58198;id=excel
でお世話になりました


最後のほうに記載のある
kanabunさんの 【58200】Re:再帰処理でのファイル検索 のコードで

Sub Try_Start()

  If n Then
    MsgBox Join(myList, vbCr), , strFind & " ---> " & n & " Files"
  Else
    MsgBox "該当ファイルなし"
  End If

MsgBox Join(myList, vbCr)

Cells(1, 1) = Join(myList, vbCr)
で、シートに一発展開できるので、対象ファイルが600程度あっても
4秒程度で処理が完了なのですが


上記に加えて
1)フォルダの階層が深いのでフルパスではなく指定フォルダ以下のパスに修正
2)特定ブックの時だけ、指定セルから値取得したいと思っています

で、
Cells(1, 1) = Join(myList, vbCr)
の変わりに

    For CNTR = 1 To UBound(myList)
    
    
      'ブックの入力シートのAX69のセルが空値で無ければ、その値を取得
      If IsError(TG_Val_M4(myList(CNTR), "ST1", "AX", 69)) = True Then

      Else
      
        Cells(ROW_CNT, 1) = Left(RE_PATH(myList(CNTR), "\", 8), 7) & "___ " & TG_Val_M4(myList(CNTR), "入力S", "AX", 69)
        ROW_CNT = ROW_CNT + 1
        
      End If

      'ブックのあるフォルダ以下のパス記載
      Cells(ROW_CNT, 1) = RE_PATH(myList(CNTR), "\", 8)
      
      ROW_CNT = ROW_CNT + 1
    
    Next

なんてすると、一機十倍以上の時間が掛かってしまいます

どのような処理にすればよいでしょうか


補足

'シートの AX69 の値取得
Public Function TG_Val_M4(tg_f_path As String, tg_s As String, cn As String, rc As Integer)

TG_Val_M4 = Application.ExecuteExcel4Macro("'" & Left(tg_f_path, InStrRev(tg_f_path, "\")) & _
        "[" & Right(tg_f_path, Len(tg_f_path) - InStrRev(tg_f_path, "\")) & "]" & _
            tg_s & "'!R" & rc & "C" & Columns("" & cn & "").Column)

End Function
'↑下記を後から作成したので・・そのままです・・・

'フルパスを特定のパス以下に変更する
Function RE_PATH(strWords As String, strDelim As String, ori_cnt As Integer)
  
  Dim astrWords() As String
  Dim cnt   As Long
  
  Dim NEW_WD As String
  
  astrWords = Split(strWords, strDelim)
  
  For cnt = ori_cnt To UBound(astrWords)
    NEW_WD = NEW_WD & "\" & astrWords(cnt)
  Next
  
  'RE_PATH2 = NEW_WD
  '最初の\を取り除く
  RE_PATH = Mid(NEW_WD, 2, Len(NEW_WD))
  
End Function


追記
1)フォルダの階層が深いのでフルパスではなく指定フォルダ以下のパスに修正
は、エクセルでの置換機能でもいいと思っていますが

2)特定ブックの時だけ、指定セルから値取得したいと思っています
の処理が必要です

また、
多分、ブックを開かないので Application.ExecuteExcel4Macro が
最速ではないかと思っているのですが、この辺もアドバイスありましたら
よろしくお願い致します

【58861】Re:再帰取得配列データの処理の方法
発言  kanabun  - 08/11/14(金) 16:16 -

引用なし
パスワード
   ▼ON さん:

こんにちは。
全体が見えないのですが、

>2)特定ブックの時だけ、指定セルから値取得したいと思っています

でしたら、別法で、
1) A列に myListを転記する。

書き出したら、
2) 配列 myList を各要素のBookフルパスを使って
  そのBookの指定シートの AX69 セルを参照する式に変更して、
  B列に再度貼り付ける。
3) B列の式を 値に変更する .Value = .Value

とする手もあるような気がします。

【58863】Re:再帰取得配列データの処理の方法
質問  ON  - 08/11/14(金) 17:55 -

引用なし
パスワード
   ▼kanabun さん ありがとうございます

>全体が見えないのですが、
わかりにくくて申し訳ありません

>でしたら、別法で、
では、
Cells(1, 1) = Join(myList, vbCr)
で、シートに展開後
別subでシート上のフルパスを使用して対象ブックのセル値を取得する
という考えでよろしいでしょうか

セルに
=フルパス+シート名+セル番地
としてみたのですが、値の更新ダイアログが開いてしまいました
この場合、
セル式で、別ブックのセル値を取得するには、対象ブックが開いていないと
駄目なような気がします
ということで、対象ブックが多いので、遅くなりそう気がします
テスト環境の作りこみをしてないので途中でやめてしまいました m(_ _)m
別途後で、検証してみたいと思います


出来ればありがたいなと思っているのは
パブリックな配列変数に
HAIRETU() = Join(myList, vbCr)
みたいに代入しておいて

Try_Start が 完了後
HAIRETU()
を FOR文で処理できれば早そうな気がしているのですが・・・
(再帰の再帰を掛けないで・・Try_Startの再読込みで遅くなっているみたいです)

上記のようなことは可能でしょうか
SUBが完了したら、HAIRETU() も無くなってしまうような気もしています
この辺理解が足りないです
アドバイスありましたらよろしくお願い致します

【58864】Re:再帰取得配列データの処理の方法
発言  kanabun  - 08/11/14(金) 18:13 -

引用なし
パスワード
   ▼ON さん:

> Cells(1, 1) = Join(myList, vbCr)
> で、シートに展開後

[A1]セルに全部入れちゃうのではなく、
A列に UBound(myList) 行分 貼り付けるということだったのですが?

>=フルパス+シート名+セル番地
>としてみたのですが、値の更新ダイアログが開いてしまいました
>この場合、
>セル式で、別ブックのセル値を取得するには、対象ブックが開いていないと
>駄目なような気がします


(今回は まるで試してないのですが)
そんなことないはずです。

▼Bookを開かないでセル値(デ−タ)を取得する
 ht tp://homepage2.nifty.com/kmado/ke_m6.htm#E97M056

>  .Formula = "='" & fol & "[" & bk & "]" & st & "'!" & adr
>  .Value = .Value

【58902】Re:再帰取得配列データの処理の方法
質問  ON  - 08/11/17(月) 15:09 -

引用なし
パスワード
   ▼kanabun さん
ありがとうございます

以前、配列を下記で、セルに貼りつけることが出来たような記憶があって
> Cells(1, 1) = Join(myList, vbCr)
で出来たようなつもりだったのですが
イミディエイトウインドウ上で
Module1.FileSearch_dt
Module2.同上
とかしていて
編集したモジュールと実行したモジュールが違っていたようです
ということで
上記では、kanabunさんの書き込み通り
>[A1]セルに全部入れちゃうのではなく、
の状態になってしまいました

>そんなことないはずです。
も、その通りで再現することが出来ませんでした
ご提示頂いた記述で
>▼Bookを開かないでセル値(デ−タ)を取得する
することが出来ました

実行結果は
\\hoge\001\fuga01.xls    
\\hoge\001\fuga03.xls    指定セルの値取得
\\hoge\002\fuga01.xls    
\\hoge\002\fuga02.xls    
\\hoge\002\fuga03.xls    指定セルの値取得
\\hoge\002\fuga04.xls    
のように
1列目にフルパス
2列目に、1列目がfuga03の時だけ、セル値を取得してみました

取得フルパス数は499個
セル値取得ブック数は128
でした

処理時間は

PC1号機、別エクセルで        
Formula先行、ExecuteExcel4Macro後行時        
    Formula    ExecuteExcel4Macro
初回    37,375ミリ秒    11,375ミリ秒
2回目    10,687ミリ秒    10,781ミリ秒
3回目    11,594ミリ秒    11,047ミリ秒

PC2号機、別エクセルで        
ExecuteExcel4Macro先行、Formula後行時        
    Formula    ExecuteExcel4Macro
初回    10,063ミリ秒    27,375ミリ秒
2回目    10,813ミリ秒    11,234ミリ秒
3回目    10,704ミリ秒    10,84ミリ秒

セル値を取得しない場合は
2回目以降であれば、どちらも1秒以下でした

上記から、
Formula、ExecuteExcel4Macroの
他ブックのセル値取得速度は同等とわかりました
また、
先行インスタンスのエクセルがあれば
後行のインスタンスのエクセルも1回目の処理が早くなる
のがちょっと驚きでした


一方、初回の処理方法 セルに転記しないでの
Try_Start内で配列を見に行くと
>なんてすると、一機十倍以上の時間が掛かってしまいます
は、初回実行でなく、2回目以降の処理で86,781ミリ秒かかりました
で、やはり10倍程遅くなる状態でした


現状、高速なファイル検索結果の配列をセルに展開して
処理する方法は理解できました
ありがとうございました

上記、初回実行の35秒を
4-5秒で、他ブックのセル値取得まで出来れば嬉しいのですが
そんな方法有りましたらよろしくお願い致します


あと
今回は速度的に無理と理解していますが
>出来ればありがたいなと思っているのは
>パブリックな配列変数に
>HAIRETU() = Join(myList, vbCr)
>みたいに代入しておいて
は、やはり不可ですか

そんなことが出来るのであれば
後学のため、ご教授頂けると嬉しいです

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

【58904】Re:再帰取得配列データの処理の方法
発言  kanabun  - 08/11/17(月) 15:46 -

引用なし
パスワード
   ▼ON さん:

こんにちは。

>ご提示頂いた記述で
>>▼Bookを開かないでセル値(デ−タ)を取得する
>することが出来ました

それはよかったですね♪

>上記から、
>Formula、ExecuteExcel4Macroの
>他ブックのセル値取得速度は同等とわかりました

どちらも、シート名が分かってないとダメですけんど、
Formulaでの外部Bookの参照は A1形式が使えるので、読みやすいですね。

>一方、初回の処理方法 セルに転記しないでの
>Try_Start内で配列を見に行くと
>>なんてすると、一機十倍以上の時間が掛かってしまいます

>あと
>今回は速度的に無理と理解していますが
>>出来ればありがたいなと思っているのは
>>パブリックな配列変数に
>>HAIRETU() = Join(myList, vbCr)
>>みたいに代入しておいて
>は、やはり不可ですか
>
>そんなことが出来るのであれば
>後学のため、ご教授頂けると嬉しいです

すみませ〜ん。コードをよく読んでないので、どういうことか分かりません。

myListという配列に パターンに合致したファイルのパス名の
配列が入ってるんですよね?

【58906】Re:再帰取得配列データの処理の方法
お礼  ON  - 08/11/17(月) 18:07 -

引用なし
パスワード
   ありがとうございます

>どちらも、シート名が分かってないとダメですけんど、
>Formulaでの外部Bookの参照は A1形式が使えるので、読みやすいですね
ですね、ExecuteExcel4Macroで考えながら記述するより楽チンです
その上、ご提示頂いた記述のFormula先は、セルに数式直でも使えてしまうんですね・・・
vbaできない人でも使えて、一見便利そうな気がしますが・・・
何でも、リンクされてしまうと訳わからなくなりそうで・・・
公開しないほうがいいような気もしますが、心配しすぎですかね・・・
どんな後遺症が出るか想像出来ない・・・・
教えてあげたい気分50、心配な気分50みたいな・・・です


>すみませ〜ん。コードをよく読んでないので、どういうことか分かりません。

本当に申し訳ありません
自分で読み返しても、理解不能的な記述がいっぱいの中で
ご回答頂いて、さぞかし??だったと思います

まして、
やさしく
>すみませ〜ん。コードをよく読んでないので、どういうことか分かりません。
と、返されては・・・

ちょっと整理してから、再Qしたいと思います

ちょっと遅れるかも知れませんが、そのときには引き続きよろしくお願いします

尚、再Qは、引き続きこちらにあげたいと思います
よろしくお願いいたします

4008 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free