エクセルでクエリー

質問

最近エクセルのVBAでDAOを使うと、アクセスのテーブルを読み込んだり出来ることを知りました。

この機能を使えば、エクセル上でアクセスでのクエリーと同じことが出来るのではないかと思って色々試してみましたが、いまひとつうまくいきません。

例えば、
シート１にあるデータ（１行目から全てデータ）を
Ａ列でグルーピングしてＢ列の合計をだし
Ｂ列の合計が０を超えるレコードを
シート２に貼り付けるといったことは可能でしょうか？

サンプルのコードか参考となるホームページ、本などありましたら教えてください

回答No.2

TAGOSAKU7 · ベストアンサー

要：DAO参照設定

シート1[A1～[A5]に、環境を設定

Sub　Main()
　　　　Dim　strDB　　　　　　　As　String
　　　　Dim　strTbl　　　　　　As　String
　　　　Dim　strFldGrp　　　As　String
　　　　Dim　strFldSum　　　As　String
　　　　Dim　lngMin　　　　　　As　Long

　　　　Dim　daoDB　　　　　　　As　DAO.Database
　　　　Dim　daoRs　　　　　　　As　DAO.Recordset
　　　　Dim　strSQL　　　　　　As　String
　　　　Dim　strSQLSum　　　As　String
　　　　Dim　lngRow　　　　　　As　Long
　　　　Dim　lngCol　　　　　　As　Long

　　　　'環境値リード
　　　　With　ThisWorkbook.Sheets(1)
　　　　　　　　strDB　=　.Cells(1,　1)　　　　　'DBパス
　　　　　　　　strTbl　=　.Cells(2,　1)　　　　'テーブル名
　　　　　　　　strFldGrp　=　.Cells(3,　1)　'グルーピングするフィールド
　　　　　　　　strFldSum　=　.Cells(4,　1)　'集計するフィールド
　　　　　　　　lngMin　=　.Cells(5,　1)　　　　'最小値
　　　　End　With

　　　　Set　daoDB　=　DBEngine.Workspaces(0).OpenDatabase(strDB)

　　　　'集計部分
　　　　strSQLSum　=　"SUM("　&　strFldSum　&　")"

　　　　strSQL　=　""
　　　　strSQL　=　strSQL　&　"SELECT"　&　vbCrLf
　　　　strSQL　=　strSQL　&　"　　　　　"　&　strFldGrp　&　vbCrLf
　　　　strSQL　=　strSQL　&　"　　　　,"　&　strSQLSum　&　"　AS　集計値"　&　vbCrLf
　　　　strSQL　=　strSQL　&　"FROM"　&　vbCrLf
　　　　strSQL　=　strSQL　&　"　　　　　"　&　strTbl　&　vbCrLf
　　　　strSQL　=　strSQL　&　"GROUP　BY"　&　vbCrLf
　　　　strSQL　=　strSQL　&　"　　　　　"　&　strFldGrp　&　vbCrLf
　　　　strSQL　=　strSQL　&　"HAVING"　&　vbCrLf
　　　　strSQL　=　strSQL　&　"　　　　　"　&　strSQLSum　&　"　>　"　&　lngMin　&　vbCrLf

　　　　Debug.Print
　　　　Debug.Print　strSQL
　　　　Set　daoRs　=　daoDB.OpenRecordset(strSQL,　dbOpenDynaset)

　　　　With　ThisWorkbook.Sheets(2)

　　　　　　　　For　lngCol　=　1　To　daoRs.Fields.Count
　　　　　　　　　　　　.Cells(1,　lngCol)　=　daoRs.Fields(lngCol　-　1).Name
　　　　　　　　Next　lngCol

　　　　　　　　lngRow　=　1
　　　　　　　　Do　Until　daoRs.EOF
　　　　　　　　　　　　lngRow　=　lngRow　+　1
　　　　　　　　　　　　For　lngCol　=　1　To　daoRs.Fields.Count
　　　　　　　　　　　　　　　　.Cells(lngRow,　lngCol)　=　daoRs.Fields(lngCol　-　1).Value
　　　　　　　　　　　　Next　lngCol

　　　　　　　　　　　　daoRs.MoveNext
　　　　　　　　Loop

　　　　　　　　.Activate
　　　　End　With

　　　　daoRs.Close
　　　　daoDB.Close
　　　　Set　daoRs　=　Nothing
　　　　Set　daoDB　=　Nothing
End　Sub

補足

遅れましたが、エクセルのシートをデータベースとして扱うことになんとか成功しました。

初心者の作ったものなので見せるほどではないですが、参考までにこんなコードになりました。
C:\DB.xlsは曜日のフィールドとデータのフィールドがあり、曜日毎のデータの合計値を計算します。

アクセスとの違いは＄をつけないとテーブルにならないことや、OpenDatabaseのオプションに"Excel 8.0をいれることぐらいでしょうか。

Sub Main()
Dim strDB As String
Dim strTbl As String
Dim daoDB As DAO.Database
Dim daoRs As DAO.Recordset
Dim strSQL As String

strDB = "c:\DB.xls" 'DBパス
strTbl = "DATA$" 'テーブル名
'エクセルファイルをデータベースとして読み込み
Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strDB, False, False, "Excel 8.0;HDR=Yes;")

strSQL = "SELECT [DATA$].曜日, Sum([DATA$].D1) AS D1の合計 FROM [DATA$] GROUP BY [DATA$].曜日;"
Set daoRs = daoDB.OpenRecordset(strSQL)

'フィールド名を取得
I = 0
For Each FLD In daoRs.Fields
Sheets(2).Cells(1, 1).Offset(0, I) = daoRs.Fields(I).Name
I = I + 1
Next

'取得したレコードセットをシートに貼り付け
Sheets(2).Range("a2").CopyFromRecordset daoRs

daoRs.Close
daoDB.Close

Set daoRs = Nothing
Set daoDB = Nothing
End Sub

回答No.1

BLUEPIXY

SQL.REQUESTワークシート関数が質問文のような目的のために使えます。
ただし、１行目には、フィールド名となる項目名が必要です。
あと、テーブルとなる範囲が範囲名として定義されている必要があります。
結果は配列になるので、レコード数の分だけのセルに配列式として入力する必要があります。

参考URL：

http://okweb.jp/kotaeru.php3?qid=1153725