ネットワーク上EXCELファイルとローカルEXCEL

質問

ちょっと困っていますのでどなたか助けていただけないでしょうか。

（何がしたいか）
ローカルPCのデスクトップにExcelファイル（A.xls）があります。
ネットワーク上にはEXCELファイル（\\Net\DB\B.xls）があります。
A.xlsのSheet1のA1のセルに12345（企業コード）を入力し検索ボタンを
押すと、B.xlsのファイルをOpenせずにb.xlsの中のA列に保存されている
企業コードデータを検索し、見つかったセルの一つとなりのセルにセット
されている売上データ（10000）をA.xlsのB1のセルへ戻す。

こんな処理は可能でしょうか。

（背景）
ネットワーク上のb.xlsはDB的に使用します。データ量が多いため
EXCEL関数ではどうしても重くなってしまいます。
なんとか、なんとか効率を上げたいので、どうぞお助けください！

回答No.2

zap35

＞B.xlsのファイルをOpenせずに
とのことですので、ODBCを用いてマクロで検索することにします。また同様のことはEXCELの「データ」→「外部データの取り込み」でもできると思いますが、こちらは試していません。

まず以下のマクロをALT＋F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて下さい。

\\Net\DB\B.xls　は１行目がタイトル行であることが必要です。このマクロでは仮に「企業コード」「売上」というタイトルの列があることにしていますが、実際のタイトルが違っていたらSQL文の変更が必要です。またシート名などの変更も必要でしょう。マクロ中にコメントを入れましたので、一度ご覧ください

また質問文では検索したい企業コードを「A1セルに入力」となっていますが、このマクロではA2に変更しています。動かしてみれば分かりますがB1にはタイトルが自動的に入るので、売上データはB2セルから下に格納されるためです。

準備ができたら、マクロの実行はALT＋F8でマクロ一覧を開き、マクロ名を選択して「実行」ボタンです。

Sub Macro1()
Dim myCnc1 As String
Dim myCnc2 As String
Dim myCnc3 As String
Dim myCmd As String
Dim myQryTbl As QueryTable
Dim DbFile As String
　DbFile = "\\Net\DB\B.xls"　'検索するBOOKをフルパスで指定

　myCnc1 = "ODBC;"
　myCnc2 = "DBQ=" & DbFile & ";"
　myCnc3 = "Driver={Microsoft Excel Driver (*.xls)};"
　myCmd = "SELECT 売上 FROM [Sheet1$] WHERE 企業コード =" & Range("A2")
'　　　A2に検索する企業コードがある。
'　　　また[Sheet1＄]は実際のシート名にする。末尾の「$」は消さない
'　　　SQL文の「企業コード」「売上」はB.xlsの実際のタイトルにする
　ActiveSheet.Columns("B:B").Delete '以前の検索結果を消す
　With ActiveSheet.QueryTables.Add( _
　　　Connection:=myCnc1 & myCnc2 & myCnc3, _
　　　Destination:=Range("B1"))
　　　.CommandText = myCmd
　　　.Refresh
　End With
End Sub

XP＋Office2003でネットワークサーバ上のデータが取得できたのを確認しています。

お礼

zap35さん

お返事が遅くなってすみません。
懇切丁寧なご回答をいただき本当に有難うございます。
いただいたScriptをぜひ利用させていただきます。
多分、継続質問させていただくかも知れませんが
どうか、宜しくお願いします。

アドバイス以上のご回答、本当に有難うございます。

回答No.1

papayuka

試せる環境に無いので、出来るか解りませんけど、、、
VLOOKUP関数でデータが引っ張れるならば、マクロで該当セルに関数を入れて戻り値を値に直すとか、、、

お礼

papayukaさん

ご回答ありがとうございます！
VLOOKUPのマクロ組み込みをさっそく試してみます。

実は、
私がイメージしてたのは、find methodで同じ文字列を
完全一致で検索し、見つかったセルのAddressを取得し、
そのセルの一つ右のValueをOffsetで取得する、という流れでした。
このときにそもそも、
１）こんな処理をファイルをOpenせずに行えるのかどうか？
　　行えるならどのような記述になるのか？
２）ネットワーク上ファイルの特定はどのように記述するのか？
この2点が？？？？？でした。

継続、アドバイスいただけると助かります！！

※　もちろんVLOOKUPで試してみてまた、報告させていただきます！