SQLのSelect文をfor nextしたい

質問

Select from where文で
A1～A〇（〇は可変）セルに記入されているものを検索対象にしたく、
構文を無視して希望したい完成形を大雑把に書くと

aaa = Range("A1").End(xlDown).Row

SQL =
Select *
from TBL
　for i = 1 to aaa
where Fld = cells(i,1)
next i

こうなるのですが、当然動くはずもなく。
ORやINでも予め検索する個数が固定されていないとダメなような気がするのですが
どのように文を書くとよろしいのでしょうか？

回答No.5

piroin654 · ベストアンサー

遅くなりました。
Accessという言葉が出てこないので、
てっきりexcelのデータのみを対象としている
と思っていました。

buf = adoRs.GetRows

補足になどが出てきたので、ひょっとして
http://okwave.jp/qa/q8776430.html
の続きですかね。

そういうことであれば、たとえば、Sheet2のF列に

　　　　12
　　　　45
　　　　36

などの年齢が数値として設定してあるとして、
Accessのテーブルに、

ID　　名前　　住所　　　年齢
1　　　田中　　東京　　　34
2　　　佐藤　　大阪　　　12
3　　　西田　　東京　　　20
4　　　三村　　名古屋　　45
5　　　織田　　福岡　　　50
6　　　石川　　熊本　　　36

のようなデータがあるとします。Sheet2のデータの
年齢と一致するレコードをSheet1に縦横を反転させて、

2　　　4　　　　6
佐藤　三村　　石川
大阪　名古屋　熊本
12　　45　　　　36

のようにデータを抽出したい、ということで
いいのでしょうか。そいうことであればこのように
書いていただくとすべての話がつながるのですが。

変数は、

　　Dim strSQL As String
　　Dim i As Long
　　Dim j As Long
　　Dim k As Long
　　Dim m As Long
　　Dim n As Long

とします。途中を省略します。

　　j = Sheet2.Range("F1").End(xlDown).Row
　　k = rs.Fields.Count
　　n = 1
　　Do Until rs.EOF
　　　　For i = 1 To j
　　　　If Worksheets("Sheet2").Cells(i, 6).Value = rs!年齢 Then
　　　　　　For m = 1 To k
　　　　　　　　Worksheets("Sheet1").Cells(m, n) = rs.Fields(m - 1)
　　　　　　Next m
　　　　　　n = n + 1
　　　　End If
　　　　Next i
　　rs.MoveNext
　　Loop

　　'後始末
　　rs.Close: Set rs = Nothing
　　cn.Close: Set cn = Nothing

のようにすれば、AccessのデータがSheet1に抽出されます。

お礼

回答ありがとうございます、説明不足で本当に申し訳ありません、URLの続きで合っています。
Excelのみで完結することでもADOを使えることを知りませんでした。

教えて頂いたコードでデータは抽出することができました
ありがとうございます。

このままでもそこまで支障はないのですが
selectには検索対象のフィールドも入れないとエラーが発生するので、そのフィールドを入れたのですが
これですと、抽出に検索対象のフィールドもでてきますが、でてこないようにはなりませんか？

上でいうと、年齢で検索し、年齢を除いたID名前住所を抽出するイメージです。

回答No.7

piroin654

ExcelでADOを使ってデータを加工するものと
思っていました。途中で何だかはずしているのかな、
という感じがしていました。失礼しました。

補足に関しては、Accessのデータを取り出す
SQL文が、

　　SELECT * FROM テーブル名

となっていて、「*」を使うとすべてのフィールドを
対象とします。したがって取り出すフィールド名を
「*」のかわりに羅列すればいいのですが、一方で
「年齢」のフィールドレコードの検索対象にしていますから
「年齢」のフィールドを含めてデータを取り出す
必要があります。したがって、コードの中で「年齢」の
フィールドデータをはじきながらSheetに格納ということで、

　　　　If Worksheets("Sheet2").Cells(i, 8).Value = rs!年齢 Then
　　　　　　For m = 1 To k
　　　　　　　　If Not rs.Fields(m - 1).Name = "年齢" Then
　　　　　　　　　　　Worksheets("Sheet1").Cells(m, n) = rs.Fields(m - 1)
　　　　　　　　End If
　　　　　　Next m
　　　　　　n = n + 1
　　　　End If

のように、

　　　　If Not rs.Fields(m - 1).Name = "年齢" Then

　　　　End If

で、
　　　　Worksheets("Sheet1").Cells(m, n) = rs.Fields(m - 1)
を挟み込みます。これで「年齢」というフィールドのデータは
Sheetには格納されません。

なお、新しく立てられた質問、
http://okwave.jp/qa/q8792437.html
に関しては、また違った方法を取るようになります。
たぶん、出来るとは思いますが、確認してみます。
ひとまずは、上記のことを確認してみてください。

回答No.6

piroin654

No5に追加してください。

　　　strSQL = "SELECT * FROM テーブル名"

を、変数宣言の後に設定しておいてください。
当然ながら「テーブル名」とはAccessのテーブル名
です。
このテーブルをExcelのSheetのデータと思っていました。
これが話がかみ合わなかった原因ですかね。失礼しました。

お礼

ありがとうございます、試せる環境がなく
連続投稿できないokwaveの仕様で返事が遅れてしまいました。
コードを書いたところ、自分の思い描いた動きをしてくれました。
辛抱強く付き合ってくださり、本当にありがとうございました。

回答No.4

piroin654

補足内容に、こちらがおいついていない
感じで。

SELECT * FROM [Sheet2$A3:B6]

上記のSQL文はFROM以下の

[Sheet2$A3:B6]

が、対象となるデータが存在するテーブルを
意味します。

SELECT *

は、指定されたテーブル中の全てのフィールドを
選択する、という意味です。

SQL文の解読は後ろから解読されます。

Excelのバージョンと接続文字列については、
http://support.microsoft.com/kb/257819/ja
http://sorceryforce.net/WordPress/?p=154

なお、OSなどの環境によっては参照設定でAD0のバージョンを
下げる必要があることもありえます。

補足中の、

＞また、回答してくださったものにはテーブル名が入っていないのですが、これでよろしいのでしょうか？
＞（ちなみに、SQL文を
＞SQL = _
＞"select * from TBL [シート$F" & i & ":F" & j & "]"

において、「TBL」については何か変数宣言なりオブジェクトの
設定なりをされているのですか？

補足

うーん・・ちょっと話がかみ合ってないですね。

説明不足だったのでもう一度質問内容を詳しく言うと
Accessデータベースから特定のテーブルに特定のフィールドの中にある
値を含む行を抽出したいのです。
そしてその値はマクロによりコンボボックスから複数選択し、
エクセルのA1から下に追加されていきます。
A1 2000
A2 4530
と入っているとすると
そのテーブルの特定のフィールド内に2000と4530がある行のデータを
抽出したいのです。
なので、回答して頂いたものであると自分の期待していたものとはちょっと違うものが抽出されることが予想されますし、何よりそれ以前にエラーがでます。
上の補足にある「TBL」はテーブル名をさします。

回答No.3

piroin654

オートメーションエラー以外に、補足の以下のような
使い方はしません。たぶん、型が違うというエラーが
起こります。

＞i = 45
＞j = 44 + Worksheets("シート名").Range("B47")　'Fの45行目からB47に記入されている数字の行分だけ取得
＞strSQLgenyu = _
＞"select * from [シート名$F" & i & ":F" & j & "]"

No1の回答をよくみてください。
i = 3
j = 6
strSQL = "SELECT * FROM [Sheet2$A" & i & ":B" & j & "]"
や、
i = 1
j = Sheet2.Range("A1").End(xlDown).Row
strSQL = "SELECT * FROM [Sheet2$A" & i & ":B" & j & "]"

のようにiやjには数値がはいります。また、
strSQL = "SELECT * FROM [Sheet2$A" & i & ":B" & j & "]"
に実際の数値を入れてかきなおせば、

i = 3
j = 6
strSQL = "SELECT * FROM [Sheet2$A" & i & ":B" & j & "]"

と、

strSQL = "SELECT * FROM [Sheet2$A3:B6]"

は、同じです。つまり、Sheet2のA3からB6の範囲のデータを
対象とする意味です。

補足

回答ありがとうございます。
[Sheet2$A" & i & ":B" & j & "]"
の意味は理解しているつもりです。

＞j = 44 + Worksheets("シート名").Range("B47")　'Fの45行目からB47に記入されている数字の行分だけ取得
のB47には数値が入っております。
ちなみに j = 48と、数値を入れてみても同様の結果となりました。

（また、補足ではSQLgenyuとstrSQLが混在していますが、これは質問用にリネームしているだけなので実際には同じ名称を使っております。混乱させてしまい申し訳ありません）

回答No.2

piroin654

コード表の参照設定で、ADOにチェックが入っているか、
参照設定が「不可」になっているものがないか、ある場合は
チェックをはずす、あるいは、ADOのバージョンが妥当な
ものか、などをまず確認してみてください。

補足

回答ありがとうございます、ADOを使用して、検索対象を可変ではなく、where ORを使用して
1つや2つ等指定してあげると抽出されるので、問題ないかと思います。
verはMicrosoft.jet.OLEDB.4.0ですが、Ace.OLEDB.12.0でないと動かないものでしょうか？

また、回答してくださったものにはテーブル名が入っていないのですが、これでよろしいのでしょうか？
（ちなみに、SQL文を
SQL = _
"select * from TBL [シート$F" & i & ":F" & j & "]"

adoRs.Open SQL, adoCn, adOpenKeyset
tmpFldCnt = adoRs.Fields.Count '9
tmpRecCnt = adoRs.RecordCount '4

Range("M28:DG31").ClearContents

ReDim buf(tmpFldCnt - 1, tmpRecCnt - 1)
buf = adoRs.GetRows

Range(Cells(28, 13), Cells(28 + tmpFldCnt - 1, 13 + tmpRecCnt - 1)) = buf

とすると、そのテーブルにある全ての情報がM28から書き込まれます。）

回答No.1

piroin654

たとえば、Sheet2にA1を起点に以下のような
データがあるとして、

東京　　関東
名古屋　中部
大阪　　関西
神戸　　関西
岡山　　中国
広島　　中国
福岡　　九州
熊本　　九州

このデータの大阪から広島までのデータを
取得するならば、簡単な方法としては、

Dim i As Long
Dim j As Long

i = 3
j = 6
strSQL = "SELECT * FROM [Sheet2$A" & i & ":B" & j & "]"

のようにします。質問の場合は、たとえば、東京から最後の
熊本までデータを取得するならば、

Dim i As Long
Dim j As Long

i = 1
j = Sheet2.Range("A1").End(xlDown).Row
strSQL = "SELECT * FROM [Sheet2$A" & i & ":B" & j & "]"

のようにします。
取得したデータは、ADOやDAOなどのRecordsetオブジェクトを使って、Sheet1に
貼り付けるなどの処理をします。

補足

回答ありがとうございます、ADOを用いて試してみたのですが

（宣言、DB接続省略）

Dim i As Long
Dim j As Long

i = 45
j = 44 + Worksheets("シート名").Range("B47")　'Fの45行目からB47に記入されている数字の行分だけ取得
strSQLgenyu = _
"select * from [シート名$F" & i & ":F" & j & "]"

adoRs.Open strSQL, adoCn, adOpenKeyset 　'ここでエラー

上記の場所でオートメーションエラーがおきてしまいます。
原因は何でしょうか？