ご飯

すっきりしたコードってむつかしい

SQLでワークシートからデータを取り出すもっとも簡単な方法

SQLでワークシートからデータを取り出す、もっとも簡単な方法をご紹介します。

このようなテーブルがあり、300円以上のごはんをメッセージ出力するというマクロです。

 f:id:uhoo:20181123162004g:plain

 

コードは次のようになります。

Module1.bas

Option Explicit

Dim con As New UConnection

Sub Uhoo()

    Dim q As New UQuery

    q.SetCon con
    q.Sql.Add "SELECT * FROM [ごはん$]"
    q.Sql.Add "WHERE 価格 >= ?"
    q.AddParam "価格", 300
    q.OpenRecordset

    Do Until q.hasNext
        MsgBox q.Fields("ごはん") & " ¥" & q.Fields("価格")
        q.MoveNext
    Loop

End Sub
 

解説

  1. 標準モジュールの先頭で UConnection のインスタンスを作成
  2. UQuery のインスタンスを作成
  3. Sql.Add メソッドでSQL文をセット
  4. AddParam メソッドでパラメータを設定
  5. OpenRecordset メソッドでクエリをオープン
    • フィールドの内容を参照するには、 Fields("ごはん") などとします
    • MoveNext メソッドで次のレコードに移動します
    • レコードが1件もなくなれば hasNext メソッドが False を返しループ文を抜けます

 

クラス使って、ADODB のぐじゃぐじゃした記述をすっぽり隠しました。お試しいただければと存じます。

 

使い方

 

  1. 後述する3つのクラスモジュールを追加してください
    • UConnection
    • UQuery
    • UStringList
  2. テーブルを挿入して「ごはん」と命名してください *1
  3. 参照設定に、Microsoft ActiveX Data Objects 6.1 Library を追加してください *2

 

UConnectionクラス

UConnectionは、ADBDB.Connection をフィールドに持ちます。Initializeイベントでコネクションがオープンされ、Terminateイベントでクローズされます。つまりオープンとクローズのコードを省略できます。また、プロバイダのぐじゃぐじゃしたパラメータを隠してくれます。
Option Explicit

Private con As ADODB.Connection

Public Sub OpenConnection()

    Set con = New ADODB.Connection

    With con
        .Provider = "Microsoft.ACE.OLEDB.12.0;"
        .Properties("Extended Properties") = "Excel 12.0 xml;"
        .Open ThisWorkbook.FullName
    End With

End Sub

Public Sub CloseConnection()

    If con.State <> adStateClosed Then
        con.Close
    End If

    Set con = Nothing

End Sub

Public Function GetCon() As ADODB.Connection

    Set GetCon = con

End Function

Private Sub Class_Initialize()

    OpenConnection

End Sub

Private Sub Class_Terminate()

    CloseConnection

End Sub

 

UQueryクラス

UQueryは、ADODB.Recordset ほかをフィールドに持ちます。また、SQL文をリスト形式で格納できる UStringList オブジェクトをフィールドに持ちます。SQL文を組み立てる際、文字列式を & でつなげてぐじゃぐじゃしたコードになるケースがよく見られます。UStringListはこれを回避できます。さらに、SELECT文にほぼ必須となる WHERE句のパラメータを保持することもできます。
Option Explicit

Private con As UConnection, rs As New ADODB.Recordset, cmd As New ADODB.Command
Public Sql As New UStringList

Public Sub SetCon(con_ As UConnection)

    Set con = con_

End Sub

Public Sub AddParam(key$, val$)

    Dim prm As ADODB.Parameter
    Set prm = cmd.CreateParameter(key, adVarChar, adParamInput, 100, val)
    cmd.Parameters.Append prm

End Sub

Public Sub OpenRecordset()

    Set cmd.ActiveConnection = con.GetCon

    cmd.CommandText = Sql.GetStr()

    Set rs = cmd.execute

End Sub

Public Sub MoveNext()

    rs.MoveNext

End Sub

Public Function hasNext() As Boolean

    hasNext = rs.EOF

End Function

Public Function Fields(name$) As Variant

    Fields = rs(name)

End Function

Public Sub CloseRecordset()

    If rs.State <> adStateClosed Then
        rs.Close
    End If

    Set rs = Nothing

End Sub

Public Sub execute()

    OpenRecordset

End Sub

Private Sub Class_Terminate()

    CloseRecordset

End Sub

 

UStringListクラス

UStringListは文字列の配列をフィールドとして持ちます。GetStrメソッドは、文字列の配列を一本の文字列につなげて返します。
Option Explicit

Private str$(), idx%

Public Sub Add(s$)

    idx = idx + 1
    ReDim Preserve str(idx)
    str(idx - 1) = s

End Sub

Public Function GetStr() As String

    Dim i%, s

    For i = 0 To idx - 1
        s = s & IIf(i > 0, " ", "") & str(i)
    Next

    GetStr = s

End Function

Public Sub clear()

    idx = 0

    Erase str()

End Sub

Private Sub Class_Initialize()

    clear

End Sub

 

おしまい

 

*1: f:id:uhoo:20181201204109g:plain

*2:f:id:uhoo:20181123201350g:plain