ちょっと困っていますのでどなたか助けていただけないでしょうか。
(何がしたいか)
ローカル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関数ではどうしても重くなってしまいます。
なんとか、なんとか効率を上げたいので、どうぞお助けください!
A 回答 (2件)
- 最新から表示
- 回答順に表示
No.2
- 回答日時:
>B.xlsのファイルをOpenせずに
とのことですので、ODBCを用いてマクロで検索することにします。また同様のことはEXCELの「データ」→「外部データの取り込み」でもできると思いますが、こちらは試していません。
まず以下のマクロをALT+F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて下さい。
\\Net\DB\B.xls は1行目がタイトル行であることが必要です。このマクロでは仮に「企業コード」「売上」というタイトルの列があることにしていますが、実際のタイトルが違っていたら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
- 回答日時:
試せる環境に無いので、出来るか解りませんけど、、、
VLOOKUP関数でデータが引っ張れるならば、マクロで該当セルに関数を入れて戻り値を値に直すとか、、、
papayukaさん
ご回答ありがとうございます!
VLOOKUPのマクロ組み込みをさっそく試してみます。
実は、
私がイメージしてたのは、find methodで同じ文字列を
完全一致で検索し、見つかったセルのAddressを取得し、
そのセルの一つ右のValueをOffsetで取得する、という流れでした。
このときにそもそも、
1)こんな処理をファイルをOpenせずに行えるのかどうか?
行えるならどのような記述になるのか?
2)ネットワーク上ファイルの特定はどのように記述するのか?
この2点が?????でした。
継続、アドバイスいただけると助かります!!
※ もちろんVLOOKUPで試してみてまた、報告させていただきます!
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) このVBAでExcelアプリケーションを作成は必要ですか? 3 2023/07/19 21:13
- Excel(エクセル) Excel VBAどこが間違ってますか? 4 2023/07/17 10:04
- Access(アクセス) Access2016のExcelインポートの機能のことで教えてください 1 2022/09/11 14:58
- Visual Basic(VBA) 複数のcsvファイルをExcelに一括変換したい 2 2023/03/03 12:44
- Visual Basic(VBA) VBAのユーザーフォームのテキストボックスに入力制限をしたい 6 2022/11/15 08:28
- その他(データベース) Excel VBA 転記について 1 2022/04/20 16:55
- その他(Microsoft Office) office2010とoffice365の共存でoffice365を優先で起草させたい 3 2023/01/24 10:47
- その他(プログラミング・Web制作) pythonでクラスで複数のメソッドを利用する方法 2 2022/04/15 04:17
- Excel(エクセル) excelで検索した商品の画像(ネットワーク上の)を表示させたい。 3 2023/06/28 00:32
- Visual Basic(VBA) エクセルVBA Workbook変数に変数を使ったファイル名を格納したい 5 2023/06/13 14:46
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
条件に応じて特定の行を非表示...
-
Excelでセル内の数式は残し値だ...
-
Excelで連続印刷をするマクロ
-
「マクロ」の足し算の式を教え...
-
エクセル VBA 小数点を含む数字...
-
Excel2007で、太字にした行のみ...
-
Excel2007 セルを右方向に削除...
-
マクロの組み方とチェックボッ...
-
Excel にて非表示行を探すワー...
-
エクセルで連番をマクロで
-
値貼り付けをしても書式も貼り...
-
エクセルで全ての数字間にカン...
-
並べ替えのマクロで対象行の範...
-
100行のセルの高さを一括で調整...
-
エクセルVBAマクロで条件付き合...
-
wordのvbaでハイパーリンク設定...
-
Excelで同じシートのコピーを一...
-
エクセルの複数シートの保護を...
-
別シート参照のセルをシート毎...
-
エクセルで前シートを参照して...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
条件に応じて特定の行を非表示...
-
Excelでセル内の数式は残し値だ...
-
Excel2007で、太字にした行のみ...
-
Excelで連続印刷をするマクロ
-
「マクロ」の足し算の式を教え...
-
エクセル VBA 小数点を含む数字...
-
エクセルで全ての数字間にカン...
-
(Excel2003)すぐ左の最後の行...
-
Excel(VBA)データ入力に応じて...
-
Excel にて非表示行を探すワー...
-
エクセルで、行ごとの並び替え...
-
エクセル2003でマクロをおこな...
-
excel ある部分だけをコピペし...
-
行の入れ替えってどうするの?
-
Excelで周期的に列を削除する方法
-
整数行を残し小数点の行を削除...
-
EXCEL マクロで「キーワード入...
-
VBA コピーを有効行までループ...
-
Excel2007 セルを右方向に削除...
-
値貼り付けをしても書式も貼り...
おすすめ情報