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

質問

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

以上のマクロを知人に教わりながら作ってみたのですが、マスタを一度開かないと検索はできないのでしょうか？
重たいデータなのでできれば開かずに検索して値を転記したいので、そのようなコマンド等ご存知の方教えてください。
ちなみにマクロは「新しいマクロの記録」から作る程度の初心者です。よろしくお願いします。

回答No.3

n-jun · ベストアンサー

ＶＢＥのツール－参照設定で
「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

マスターの方は閉じていても出来るはずです。
ただＡＤＯは２回目なので的はずれでしたらごめんなさい。
⇒ネットワークでの確認も環境がないので出来てません。
　速度についてはどうかな・・・？

補足

回答ありがとうございました。
さっそくマクロを実行してみましたが、

　dbcon.Open　"Provider=Microsoft.Jet.OLEDB.4.0;"　&　_
　　　　　　　　　　"Extended　Properties=Excel　8.0;"　&　_
　　　　　　　　　　"Data　Source=C:\Documents　and　Settings\mi200274\デスクトップ\\マスタ.xls;"

この部分でデバッグになり、F1を押して確認しましたら
「選択内容があいまいです。
キーワードが選択されていないか、あるいは統合開発環境 (IDE) のコンポーネントについてのヘルプ トピックを表示しようとしました。キーワードを選択していた場合には、1 つのキーワードのみを指定するように、選択範囲を変更し直してください。IDE についての情報は、以下のトピックを参照してください。
コード ウィンドウまたはモジュール ウィンドウ
イミディエイト ウィンドウ
ローカル ウィンドウ
オブジェクト ブラウザ
ウォッチ ウィンドウ」　と表示されました。
どうすればいいのでしょうか(T_T)　教えてください。

また大変申し訳ないのですが、初心者なので分からないことがあるので教えてください。
ＶＢＥのツール－参照設定で「Microsoft Active Data Object 2.x Library」にチェックを入れましたが、他にもたくさん項目がありました。
自分の使用したい、作りたいマクロで標準設定されていない機能を使うようにできる項目が参照設定にある項目なのですか？

お忙しいとは思いますが回答お願いします。

回答No.5

n-jun

n-junです。

こちらに回答する際に半角スペースを全角に置換しましたが、余計な部分まで行なっていたようです。

　dbcon.Open　"Provider=Microsoft.Jet.OLEDB.4.0;"　&　_
　　　　　　　　　　"Extended Properties=Excel 8.0;"　&　_
　　　　　　　　　　"Data Source=C:\Documents and Settings\mi200274\デスクトップ\\マスタ.xls;"

上記を全角スペースから半角に修正したものと入替えて下さい。

お礼

上記のものに入れ替えましたら無事できました！！
何度も答えていただいてありがとうございました。
早速これでやってみます。

回答No.4

n-jun

n-junです。

＞「選択内容があいまいです。～
は
⇒この部分でデバッグになり、F1を押して確認しましたら
の動作によって出てきたメッセージですよね？
欲しいのはこの動作をやる前に出ていたエラーメッセージなんですけど。

こちらはＷｉｎ：ＸＰ　Excel：２００２　で検証しました。
以降のバージョンだと検証できないかも知れません。（原因が不明）

補足

ご回答ありがとうございました。

このメッセージでよろしいでしょうか？

「実行時エラー'-2147467259(80004005)':
インストール可能なISAMドライバが見つかりませんでした」

こちらはWin：XP(professional2003SP2)　Excel：2003です。
検証は難しいでしょうか？

お時間があるときで結構なので回答お願いします。

回答No.2

nag0720

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

私だったら、マスターをテキストファイルに吐き出しておいて、検索はそのテキストファイルをマクロで読み込みながら処理するという方法を取ります。

お礼

そういった方法もあるんですね。試してみます。
回答ありがとうございました。

回答No.1

374649

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

マクロ使うということを前提に。

マスターでは沢山のSheetを使っているのでしょうか、もしそうでなければマスターの中でデータシートから検索シートを追加して必要データを移し処理したほうが簡単な気がします。

やり方は、マスターのB列の"い"からそのアドレスを求めそれを起点に必要な範囲を決めそれを検索シートへコピー計算式をセット必要な結果を得る。

こうすれば新たなブックを作る必要はないと思います、データのフォーマットや処理の内容が分からないので勝手な思い込みかもしれませんが！！

お礼

回答ありがとうございました。

補足

回答ありがとうございました。
説明不足ですみません。
マスタシートをサーバーにおき、複数のPCからそれぞれの検索ブックで見にいこうと思っています。
具体的にいうと、受注台帳が7つのPCにあり、それぞれの受注台帳でマスタコードを入力すると顧客名・電話番号がマスタから転記される仕組みにしたいです。