Excel VBA質問箱 IV

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

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

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
8527 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【32721】別フォルダのブックをMatch関数で わさび 05/12/21(水) 12:01 質問[未読]
【32722】Re:別フォルダのブックをMatch関数で Jaka 05/12/21(水) 12:12 発言[未読]
【32728】別フォルダで他ブックの値を取得 わさび 05/12/21(水) 13:27 質問[未読]
【32730】Re:別フォルダで他ブックの値を取得 Kein 05/12/21(水) 13:39 発言[未読]
【32736】Re:別フォルダで他ブックの値を取得 わさび 05/12/21(水) 14:18 質問[未読]
【32737】Re:別フォルダで他ブックの値を取得 わさび 05/12/21(水) 14:22 質問[未読]
【32741】Re:別フォルダで他ブックの値を取得 Kein 05/12/21(水) 14:42 回答[未読]
【32744】Re:別フォルダで他ブックの値を取得 わさび 05/12/21(水) 15:15 質問[未読]
【32749】Re:別フォルダで他ブックの値を取得 Kein 05/12/21(水) 16:23 回答[未読]
【32766】Re:別フォルダで他ブックの値を取得 わさび 05/12/21(水) 20:48 お礼[未読]
【32768】Re:別フォルダで他ブックの値を取得 わさび 05/12/21(水) 21:30 質問[未読]
【32769】Re:別フォルダで他ブックの値を取得 Kein 05/12/21(水) 21:48 発言[未読]
【32781】Re:別フォルダで他ブックの値を取得 わさび 05/12/22(木) 8:20 お礼[未読]
【32721】別フォルダのブックをMatch関数で
質問  わさび  - 05/12/21(水) 12:01 -
引用なし
パスワード
   別フォルダにあるブック内で、
「AAA」という文字が入っているセルの行番号を調べようと試みております。
以下のように考えたのですが、最終行で
「実行時エラー'9'　インデックスが有効範囲にありません」と出てしまいます。

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

Dim nowPATH As String
nowPATH = "C:\Documents and Settings\（中略）\○○.xls"

Dim MATCHrow As Byte
MATCHrow = Application.WorksheetFunction.Match(AAA, Workbooks(nowPATH).Worksheets("TOTAL").Range("C12:C32"), 0) + 11

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

パスやブック名、シート名は合っています。
C12:C32の範囲内に「AAA」という文字は必ずあります。

「Workbooks(nowPATH)」の部分、すなわち、
「Workbooks(C:\Documents and Settings\（中略）\○○.xls)」の書き方を、
私はわかっていないんですよね、おそらく。
実際にはどう書けばよいのでしょうか。
どうかご教授ください。宜しくお願いします。
【32722】Re:別フォルダのブックをMatch関数で
発言  Jaka  - 05/12/21(水) 12:12 -
引用なし
パスワード
   VBA上のMatch関数の使い方（エラー処理）はおいておいて、VBA上から他ブックを開かずにMatch関数は使えないと思います。
閉じたままと言うなら、適当なセルに関数を入れて、その返り値を取得したらどうでしょうか？
【32728】別フォルダで他ブックの値を取得
質問  わさび  - 05/12/21(水) 13:27 -
引用なし
パスワード
   ▼Jaka さん：
>VBA上のMatch関数の使い方（エラー処理）はおいておいて、VBA上から他ブックを開かずにMatch関数は使えないと思います。
>閉じたままと言うなら、適当なセルに関数を入れて、その返り値を取得したらどうでしょうか？

がーーーーーーーん…他ブックを開かずにはできないんですね…
それならばVLookup関数で、と思って以下のようにしてみましたが、

Range("A4").Value = Application.WorksheetFunction.VLookup(AAA, Workbooks(nowPATH).Worksheets("TOTAL").Range("C12:C32"), 1, False)

…同様のエラーが出てしまうということは、
やっぱりVLookup関数も他ブックを開かずにはできないんですね。


けっきょく何がしたいのかといえば、
他ブックのC12:C32内に「AAA」があれば、その行のE〜Zまでの値を取得したいのです。
何行目かがわかれば、以下のマクロ（一部省略）でできることは確認できたのですが。

---------------------------------------
Dim nowPATH As String
nowPATH = "C:\Documents and Settings\（中略）\○○.xls"
　　
Dim nowFILE As String
nowFILE = Dir(nowPATH)

Workbooks(nowFILE).Worksheets("TOTAL").Range("E" & i & ":Z" & i).Copy
ThisWorkbook.Worksheets("テスト１").Range("A2").PasteSpecial Paste:=xlValues
---------------------------------------

対象となるファイル数が、多ければ２００以上にもなりかねないので、
ファイルを開かずに処理をしたいと考えてます。
もし何かいい方法をご存知でしたら、ぜひご教授ください。
なにとぞ宜しくお願いします。
【32730】Re:別フォルダで他ブックの値を取得
発言  Kein  - 05/12/21(水) 13:39 -
引用なし
パスワード
   ブックを開かずに値を引っ張る方法は、過去ログにもたくさんあるように
リンクを使うのが一般的です。他には DAO とか Excel4マクロ関数 を使う手も
ありますが、いずれにしても
>対象となるファイル数が、多ければ２００以上
これをループ処理するなら、シート名が全て同じになっていないととても面倒です。
その点はどうでしょーか ? それから、リンク先のセル範囲はどこにするか ?
という点も、たくさんのブックを対象にするのだから重要な問題ですね。
【32736】Re:別フォルダで他ブックの値を取得
質問  わさび  - 05/12/21(水) 14:18 -
引用なし
パスワード
   ▼Kein さん：
>ブックを開かずに値を引っ張る方法は、過去ログにもたくさんあるように
>リンクを使うのが一般的です。他には DAO とか Excel4マクロ関数 を使う手も
>ありますが、いずれにしても
>>対象となるファイル数が、多ければ２００以上
>これをループ処理するなら、シート名が全て同じになっていないととても面倒です。
>その点はどうでしょーか ? それから、リンク先のセル範囲はどこにするか ?
>という点も、たくさんのブックを対象にするのだから重要な問題ですね。

DAOとかExcel4マクロ関数…は、
私の能力の限界を超えてしまいそうなので避けたいです。

シート名はすべて「TOTAL」です。

過去ログにあった応用で、以下のテストをしてみたら値が入ってきました！
----------
Sub TEST1()

　　Dim IDcode As String
　　Dim NOWrow As Byte
　　
　　IDcode = "○○"
　　NOWrow = 12

　　With ThisWorkbook.Worksheets("テスト１").Range("A4")
　　　　.Formula = "='C:\Documents and Settings\（中略）\[" & IDcode & ".xls]TOTAL'!D" & NOWrow
　　　　.Value = .Value
　　End With

End Sub
----------

私のつたない知識では、これをE〜Z列までループさせて、
さらに、ファイル名をループさせて…ということしか思い浮かびません。
もっといい方法がありましたら、ぜひぜひご教授ください。
宜しくお願いします。
【32737】Re:別フォルダで他ブックの値を取得
質問  わさび  - 05/12/21(水) 14:22 -
引用なし
パスワード
   って、あぁぁぁぁ…
そこで問題なのが、対象となるデータが何行目なのかを
どうやって知るのかということでした…

>過去ログにあった応用で、以下のテストをしてみたら値が入ってきました！
>----------
>Sub TEST1()
>
>　　Dim IDcode As String
>　　Dim NOWrow As Byte
>　　
>　　IDcode = "○○"
>　　NOWrow = 12
>
>　　With ThisWorkbook.Worksheets("テスト１").Range("A4")
>　　　　.Formula = "='C:\Documents and Settings\（中略）\[" & IDcode & ".xls]TOTAL'!D" & NOWrow
>　　　　.Value = .Value
>　　End With
>
>End Sub
>----------
>
>私のつたない知識では、これをE〜Z列までループさせて、
>さらに、ファイル名をループさせて…ということしか思い浮かびません。
>もっといい方法がありましたら、ぜひぜひご教授ください。
>宜しくお願いします。
【32741】Re:別フォルダで他ブックの値を取得
回答  Kein  - 05/12/21(水) 14:42 -
引用なし
パスワード
   列方向(右)へとリンクしていくなら、かならず 256個以内 のファイル数でないと
いけません。それは分かりますね ? で、保存先のフォルダーは決まっているのですから
そのパスは定数で宣言しておきます。こんな感じになります。

Sub TEST1()
　　Dim MyF As String
　　Dim Cnum As Integer
　　Const Ph As String = _
　　"'C:\Documents and Settings\（中略）\"
　　
　 MyF = Dir(Ph & "*.xls")
　 Do Until MyF = ""
　　　Cnum = Cnum + 1
　　　If Cnum > 256 Then Exit Do
　　　With Worksheets("テスト１")
　　　　 With .Range(.Cells(4, Cnum), .Cells(24, Cnum))
　　　　　　.Formula = "=' & Ph & "[" & MyF & "]TOTAL'!D12"
　　　　　　.Value = .Value
　　　　 End With
　　　End With
　　　MyF = Dir()
　 Loop
　 With Worksheets("テスト１")
　　　.Range(.Cells(26, 1), .Cells(26, Cnum)).Formula = _
　　　"=IF(ISNA(MATCH(""AAA"",A:A,0)),"""",MATCH(""AAA"",A:A,0))"
　 End With
End Sub
【32744】Re:別フォルダで他ブックの値を取得
質問  わさび  - 05/12/21(水) 15:15 -
引用なし
パスワード
   今、必死で解読しておりますが、質問があります。

Range(.Cells(4, Cnum), .Cells(24, Cnum)) や、
Range(.Cells(26, 1), .Cells(26, Cnum)) のように、
Rangeの括弧の中にCellsがあるのはどういう意味なのでしょうか。


>列方向(右)へとリンクしていくなら、かならず 256個以内 のファイル数でないと
>いけません。それは分かりますね ?

256列までしかない…ということでいいんでしょうか。
【32749】Re:別フォルダで他ブックの値を取得
回答  Kein  - 05/12/21(水) 16:23 -
引用なし
パスワード
   えっと、まずコードを少し改良しておきます。ファイル名が表示されて無いと、
どのブックのデータか分からなくなりますから。

Sub TEST1()
　　Dim MyF As String
　　Dim Cnum As Integer
　　Const Ph As String = _
　　"'C:\Documents and Settings\（中略）\"
　　
　 MyF = Dir(Ph & "*.xls")
　 Do Until MyF = ""
　　　Cnum = Cnum + 1
　　　If Cnum > 256 Then Exit Do
　　　With Worksheets("テスト１")
　　　　 With .Range(.Cells(4, Cnum), .Cells(24, Cnum))
　　　　　　.Formula = "=' & Ph & "[" & MyF & "]TOTAL'!D12"
　　　　　　.Value = .Value
　　　　 End With
　　　　.Cells(26, Cnum).Value = Left$(MyF, Len(MyF) - 4)
　　　End With
　　　MyF = Dir()
　 Loop
　 With Worksheets("テスト１")
　　　.Range(.Cells(27, 1), .Cells(27, Cnum)).Formula = _
　　　"=IF(ISNA(MATCH(""AAA"",A:A,0)),"""",MATCH(""AAA"",A:A,0))"
　 End With
End Sub

>Rangeの括弧の中にCellsがあるのはどういう意味
Rangeオブジェクトをヘルプで調べてみて下さい。その「プロパティ」に Cells が
あるはずです。なので、そーいう使い方も出来るというわけです。
一方、Cells は Worksheetオブジェクトのプロパティでもあるので、
Worksheets("テスト１").Cells(26, Cnum).Value = ・・・という書き方も
出来るのです。
>256列までしかない…ということでいいんでしょうか。
そうです。よって変数 Cnum が 256 を超えたら、ループを抜ける条件をつけて
おきました。
【32766】Re:別フォルダで他ブックの値を取得
お礼  わさび  - 05/12/21(水) 20:48 -
引用なし
パスワード
   >Rangeオブジェクトをヘルプで調べてみて下さい。その「プロパティ」に Cells が
>あるはずです。なので、そーいう使い方も出来るというわけです。

おぉぉぉぉぉ。なるほど納得です。

サンプルプログラム、ものすごく参考になりました。
これを元にがんばります。
本当にありがとうございました。
【32768】Re:別フォルダで他ブックの値を取得
質問  わさび  - 05/12/21(水) 21:30 -
引用なし
パスワード
   先ほどの仕様の個所とはまた別のところでなんですが、
たとえば下のように、

>　　　　 With .Range(.Cells(4, Cnum), .Cells(24, Cnum))
>　　　　　　.Formula = "=' & Ph & "[" & MyF & "]TOTAL'!D12"
>　　　　　　.Value = .Value
>　　　　 End With

他ブックから値を取得してセルに書き込むのではなく、
取得した値を変数に代入するにはどうしたらいいのでしょうか。
【32769】Re:別フォルダで他ブックの値を取得
発言  Kein  - 05/12/21(水) 21:48 -
引用なし
パスワード
   Dim Ary As Variant

と、Variant型の配列用変数を宣言して

With .Range(.Cells(4, Cnum), .Cells(24, Cnum))
　　.Formula = "=' & Ph & "[" & MyF & "]TOTAL'!D12"
　　Ary = .Value
End With

とすれば、 Ary が１次元の配列になります。
注意する点としては、これをループの中で使いまわしするわけですから、
使い終わった時点で直ぐに

Erase Ary

というように初期化する必要がある。ということです。
【32781】Re:別フォルダで他ブックの値を取得
お礼  わさび  - 05/12/22(木) 8:20 -
引用なし
パスワード
   おはようございます！

>Dim Ary As Variant
>
>と、Variant型の配列用変数を宣言して
>
>With .Range(.Cells(4, Cnum), .Cells(24, Cnum))
>　　.Formula = "=' & Ph & "[" & MyF & "]TOTAL'!D12"
>　　Ary = .Value
>End With
>
>とすれば、 Ary が１次元の配列になります。

ということは、一度どこかのセルに書き込まないといけないということなんですね。
そうしたら、それ用のセルをどこか見えないところに設けておく方向で、
仕様変更を検討してみます。
ありがとうございました！
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
8527 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free