マクロ：別ブックのデータの値を転記

Question

ExcelでVlook関数を使ってデータを検索していたのですが、マスタの件数(15,000件)と数式が多くなってしまいブックの容量が大きくなってしまって動きづらくなってしまったので、マスタと検索のブックに分け、マクロを使おうと思ってます。
簡単にいうと、

【マスタブック】
　　A列　　B列
1　　1　　　あ
2　　2　　　い

【検索ブック】
　　A列　　B列
1　　2　　　い
2

検索ブックA列1行目に、「2」を入力してマクロを実行すると「い」が表示されるようにしたいのです。　　
開いておくのは検索ブックはのみです。

Sub 転記()

Dim マスタ As Workbook
Dim 検索 As Workbook
Dim 行, 数字 As Long
Dim Bname As String

Bname = ActiveWorkbook.Name
Workbooks.Open Filename:="C:\Documents and Settings\mi200274\デスクトップ\マスタ.xls"
Workbooks("マスタ.xls").Activate
Set マスタ = Workbooks("マスタxls")
Set 検索 = ThisWorkbook
Set ws1 = マスタ.Worksheets("Sheet1")
Set ws2 = 検索.Worksheets("Sheet1")
 
On Error Resume Next
 
 行 = 1
 
 Do Until ws2.Range("A" & 行).Value = ""
  
  数字 = ws2.Range("A" & 行).Value  
  
  対象 = ws1.Range("A:A").Find(数字, lookat:=xlWhole).Row

   ws2.Range("B" & 行).Value = ws1.Range("B" & 対象).Value
 
   行 = 行 + 1
 
 Loop
 
 ActiveWorkbook.Close
 Workbooks(Bname).Activate
 
End Sub

以上のマクロを知人に教わりながら作ってみたのですが、マスタを一度開かないと検索はできないのでしょうか？
重たいデータなのでできれば開かずに検索して値を転記したいので、そのようなコマンド等ご存知の方教えてください。
ちなみにマクロは「新しいマクロの記録」から作る程度の初心者です。よろしくお願いします。

n-jun · Accepted Answer

ＶＢＥのツール－参照設定で
「Microsoft Active Data Object 2.x Library」
にチェックを入れる。⇒2.xは私の所だと2.8であった。

マスターシートの１行目はＡ１に「番号」Ｂ１に「記号」としておく。

Sub　TEST()
　Dim　dbcon　As　New　ADODB.Connection
　Dim　dbres　As　ADODB.Recordset
　Dim　r　As　Range

　dbcon.Open　"Provider=Microsoft.Jet.OLEDB.4.0;"　&　_
　　　　　　　　　　"Extended　Properties=Excel　8.0;"　&　_
　　　　　　　　　　"Data　Source=C:\Documents　and　Settings\mi200274\デスクトップ\マスタ.xls;"

　Set　dbres　=　New　ADODB.Recordset
　dbres.Open　"[Sheet1$A:B]",　dbcon,　adOpenForwardOnly,　adLockReadOnly

　For　Each　r　In　Range("A1",　Cells(Rows.Count,　1).End(xlUp))
　　　　　dbres.Filter　=　"番号　=　"　&　r.Value

　　　　　If　dbres.EOF　Then
　　　　　　　　r.Offset(,　1).Value　=　"×"
　　　　　Else
　　　　　　　　r.Offset(,　1).Value　=　dbres!記号
　　　　　　　　'dbres.MoveFirst
　　　　　End　If
　Next
　dbres.Close
　dbcon.Close
End　Sub

マスターの方は閉じていても出来るはずです。
ただＡＤＯは２回目なので的はずれでしたらごめんなさい。
⇒ネットワークでの確認も環境がないので出来てません。
　速度についてはどうかな・・・？

n-jun · Answer

n-junです。

こちらに回答する際に半角スペースを全角に置換しましたが、余計な部分まで行なっていたようです。

　dbcon.Open　"Provider=Microsoft.Jet.OLEDB.4.0;"　&　_
　　　　　　　　　　"Extended Properties=Excel 8.0;"　&　_
　　　　　　　　　　"Data Source=C:\Documents and Settings\mi200274\デスクトップ\マスタ.xls;"

上記を全角スペースから半角に修正したものと入替えて下さい。

n-jun · Answer

n-junです。

＞「選択内容があいまいです。～
は
⇒この部分でデバッグになり、F1を押して確認しましたら
の動作によって出てきたメッセージですよね？
欲しいのはこの動作をやる前に出ていたエラーメッセージなんですけど。


こちらはＷｉｎ：ＸＰ　Excel：２００２　で検証しました。
以降のバージョンだと検証できないかも知れません。（原因が不明）

nag0720 · Answer

セルの参照だったらファイルを開かなくてもできるようですが、Find関数のようなものは無理でしょう。

私だったら、マスターをテキストファイルに吐き出しておいて、検索はそのテキストファイルをマクロで読み込みながら処理するという方法を取ります。

374649 · Answer

質問の答えにはなっていませんが、疑問に思ったので。

マクロ使うということを前提に。

マスターでは沢山のSheetを使っているのでしょうか、もしそうでなければマスターの中でデータシートから検索シートを追加して必要データを移し処理したほうが簡単な気がします。

やり方は、マスターのB列の"い"からそのアドレスを求めそれを起点に必要な範囲を決めそれを検索シートへコピー計算式をセット必要な結果を得る。

こうすれば新たなブックを作る必要はないと思います、データのフォーマットや処理の内容が分からないので勝手な思い込みかもしれませんが！！

マクロ：別ブックのデータの値を転記

ＶＢＥのツール－参照設定で

この回答への補足

n-junです。

n-junです。

この回答への補足

セルの参照だったらファイルを開かなくてもできるようですが、Find関数のようなものは無理でしょう。

質問の答えにはなっていませんが、疑問に思ったので。

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング