VBAでプルダウン選択したデータを違うＶＢＡの中に反映させたい

質問

ＶＢＡであるプログラムを作成しました。
それは成功しました。
次の段階として成功したＶＢＡを動的にしたいと考えています。

具体的には以下のようになります。
成功したプログラムの一部。
（ＳＱＬサーバでクエリを組んでその結果をエクセルに出力するプログラムです。）

Sub　aaa()
…
strSQL = "SELECT TOP 10 TBL_……'%石原裕次郎%'"
…
End Sub

石原裕次郎の部分が固定ですが、エクセル側でプルダウンなどで、
ほかの人（北島三郎など）に選択できて、その選択した人をＳＱＬの'%石原裕次郎%に反映させたいと考えております。
可能でしょうか。

ＶＢＡははじめたばかりでよくわかっていません。
どなたか宜しくお願い致します。

回答No.1

nicotinism · ベストアンサー

一案ですが
大きな流れとして

>ＳＱＬサーバでクエリを組んでその結果をエクセルに出力するプログラムです
は出来てるようなので
氏名だけのレコードを抜き出したものをシート（シート名を仮に「氏名」とします）
A列にズラッと氏名が並ぶようにしてください。
（データ→外部データの取り込みから出来るかもしれませんが私も良く分からない）

で
メニューから表示→ツールバー→コントロールツールボックスにチェックを入れ
コンボボックスをシート上に配置。
このコンボボックスのプロパティを出して　ListFillRange　の欄に　氏名!A:A とします。
これでシート氏名の一覧がドロップダウンできるはず。

次に
strSQL = "SELECT TOP 10 TBL_……'%石原裕次郎%'"は
strSQL = "SELECT TOP 10 TBL_……'%who%'" とかにしておいて
コンボボックス（仮名CombBox1）の　Change　イベントで
strSql= replace(strSql,"who",CombBox1.Value)　で変換させてあげてやれば良いのでは？

私自身良く分からんのでもっとスマートな方法が有りそうな気がします。。。
特にコンボボックスのデータソースとSQLサーバのデータを直結出来れば・・
とは思うんですけどねぇ。わざわざシート氏名に書き出さなくて済みますし。
もし分かったら教えて　(^^ゞ

お礼

早速の回答ありがとうございます。
早速やってみましたが、つまづいてしまいました。
わかったら教えてください。

＞このコンボボックスのプロパティを出して　ListFillRange　の欄に＞氏名!A:A とします。

ListFillRangeの欄に入力しようとしても入力できません。
入力後、enterキーなどで移動すると入力しても空白になってしまいます。

＞コンボボックス（仮名CombBox1）の　Change　イベントで

chngeイベントとはどこのことでしょうか。

コンボボックスでググりましたが、思うように解決できませんでした。
恐縮ですが、宜しくお願い致します。

補足

＞このコンボボックスのプロパティを出して　ListFillRange　の欄に＞氏名!A:A とします。

＞ListFillRangeの欄に入力しようとしても入力できません。
＞入力後、enterキーなどで移動すると入力しても空白になってしまい＞ます。

すいません。こちらは解決しました。

回答No.4

nicotinism

おそらく、コントロールの名前が違っているためかと
CombBox1.Value　誤
ComboBox1.Value　正
かな？そちらの名前を確認してください。
私の最初のスペルミスが続いてましたね。
勘弁して　(^^ゞ

お礼

ありがとうございます。
問題なく動作致しました。
非常に勉強になりました。
ありがとうございました。

さらに質問させていただいてもよろしいでしょうか。

(1)コンボボックスで選択後、「実行」ボタンを選択すると、実行するように変更できますでしょうか。

(2)コンボボックスを2個作成し、1個目のコンボボックス、かつ、2個目のコンボボックスを選択後、「実行」のように変更はできますでしょうか。

現在の状態でも十分満足しておりますので、
お忙しいようでしたら、流していただいてかまいません。

回答No.3

nicotinism

strSQL= replace(strSQL,"who",CombBox1.Value)
strSQL = "SELECT TOP 10 TBL_……'%who%'"
ではなくて反対

strSQL = "SELECT TOP 10 TBL_……'%who%'"
という雛形を作っといて
strSQL= replace(strSQL,"who",CombBox1.Value)
でReplaceすると
strSQL = "SELECT TOP 10 TBL_……'%石原裕次郎%'"などのように
冒頭のご質問のSQL文になるということです。”夜霧よ今夜もありがとう♪”

Private Sub ComboBox1_Change()
Dim StrSql as string '入れてあるとは思いますが・・・
…
strSQL = "SELECT TOP 10 TBL_……'%who%'"
strSQL= replace(strSQL,"who",CombBox1.Value)
MsgBox "CombBox1.Value= " & CombBox1.Value & " Sql=" & strSql
Debug.Print "CombBox1.Value= " & CombBox1.Value & " Sql=" & strSql
'↑Debug.Print　でイミディエイトウィンドウに出力
…
End Sub

まだエラーが出るようでしたらモジュールの全文とエラーメッセージを載せてください。
またメニューのデバッグから　VBAProjectのコンパイルも忘れずに！
※Excel97以前のバージョンではReplace関数はないので自作する事になります

>subの中にコードを入れればいいということでしょうか。
はい。

お礼

回答ありがとうございます。
修正しましたが、結果は同じでした。

「実行時エラー”４２４”オブジェクトが必要です。」
のメッセージが出ます。

何度も教えていただきまして恐縮ですが、
ご教授宜しくお願い致します。

*******************************
モジュール全文
********************************

Private Sub ComboBox1_Change()

Const connstr = "Provider=sqloledb;" & _
"Data Source=ＳＱＬサーバのホスト名;Initial Catalog=ＤＢ名;User Id=ユーザ名;Password=パスワード; "

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT TOP 10 _……'%who%'"
strSQL = Replace(strSQL, "who", CombBox1.Value)

MsgBox "CombBox1.Value= " & CombBox1.Value & " Sql=" & strSQL
Debug.Print "CombBox1.Value= " & CombBox1.Value & " Sql=" & strSQL

conn.Open connstr
rs.Open strSQL, conn

Range("A2").CopyFromRecordset rs

rs.Close
conn.Close

Set rs = Nothing
Set conn = Nothing

End Sub

補足

ちなみに、

strSQL= replace(strSQL,"who",CombBox1.Value)
↓
strSQL= replace(strSQL,"who","北島三郎")

に変更したら、問題なく置き換えができていました。
宜しくお願い致します。

回答No.2

nicotinism

まず、表示　→　ツールバーの　VisualBasic　はチェックを入れて表示しといてください。
そのバーの中に三角定規のアイコンがありますので、
これでデザインモードの切り替えを行います。

で本題
デザインモードでコンボボックスを右クリックするとコンテキストメニューが展開され
その中に、コードの表示　があるはずですので選択してください。
VBE　の画面に切り替わり

Option Explicit

Private Sub ComboBox1_Change()

End Sub

となるとおもいますので
先の回答のstrSql= replace(strSql,"who",CombBox1.Value)を
ご質問者さんのコードと共に適切な箇所に入れれば宜しいかと？

また蛇足ですが

CombBox1　　　　　▽　　　　　　　　　　　　Change　　　　　　▽
　　　　　　　　　　　　　　　　　　　　　　　　　↑これをクリック
Option Explicit

Private Sub ComboBox1_Change()
'処理コード
End Sub
コンボボックスで使えるイベントがドロップダウンリストに出てきます。

お礼

回答ありがとうございます。
非常に勉強になります。
以下のように変更しましたが、
「実行時エラー'424'　オブジェクトが必要です」
のメッセージが出ました。デバックを押すと、
「strSQL= replace(strSQL,"who",CombBox1.Value)」
の背景が黄色になりました。

Option Explicit
Private Sub ComboBox1_Change()

…
strSQL= replace(strSQL,"who",CombBox1.Value)
strSQL = "SELECT TOP 10 TBL_……'%who%'"
…
End Sub

これはどのようにすればよろしかったでしょうか。
聞いてばかりですいません。

＞コンボボックスで使えるイベントがドロップダウンリストに出てきま
す

こちらですが、ドロップダウンリストが出てきました。
これは、それぞれを選択した場合に、
実行したいプログラムがあれば同じように
subの中にコードを入れればいいということでしょうか。