ExcelVBAとAccessの連携 第4回 条件を絞ってデータを読み込む

ExcelからAccessのデータベースをごにょごにょしちゃう記事の続きを書きました。登録したツイートを、指定のアカウントや日付、キーワードで絞り込むなどのオプションをつけた読み込みを実装してみます。

関連記事

• 第1回 Excelからデータベースへの接続
• 第2回 テーブル設計とシート&コードの準備
• 第3回 SQLを使った読み書きの処理
• 第4回 条件を絞ってデータを読み込む ←NOW!
• 第5回 レコードの更新・削除
• 第6回 トランザクション処理
• 番外1 リファクタリングしたコード
• 番外2-1 プレースホルダを使ったSQL実行
• 番外2-2 続・プレースホルダを使ったSQL実行（汎用化）

この連載がもっと実用的なサンプルで書籍になりました！

ユーザーフォームの作成

抽出条件を絞り込むためのフォームを作成します。ExcelのVisualBasicEditorで[挿入]→[ユーザーフォーム]で新しいフォームを作成します。名前はUserFrom1になるはずです。

ツールボックスを使ってこんな感じに配置してみます。コマンドボタン1（OK）を押したらフォームを隠す、2（キャンセル）を押したら終了するというコードをあらかじめ書いておきます。

完成イメージ

まず、前回書いた、条件なしで出力するものがこちら。

Sub DBtweet() 'ツイート読み込み
  Call DBconnect 'DB接続

  'SQL文の作成
  strSQL = _
    "SELECT t_tweet.f_no, t_tweet.f_id, t_main.f_name, t_tweet.f_content, t_tweet.f_day " & _
    "FROM t_tweet LEFT JOIN t_main " & _
    "ON t_tweet.f_id = t_main.f_id " & _
    "ORDER BY t_tweet.f_day DESC"
  adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ
  Range("B10:F26").ClearContents '前のデータクリア
  Range("B10").CopyFromRecordset adoRs 'セルへ出力
  Range("F10:F26").NumberFormatLocal = "yyyy/m/d h:mm;@" '書式設定

  Call DBcut_off 'DB切断
End Sub

SQL文を作って、実行して出力。このSQL文に、さっき作ったユーザーフォームを使った条件を追加する形になります。

イメージとしてはこんなカタチ。

黄色いところが条件1, 水色が条件2, ピンクが条件3といった感じでそれぞれユーザーフォームを使ってSQL文（の一部）をつくり、最終的にSQL文に組み込みます。

解説

With

まず、黄緑色のラインをひっぱっている部分を見てみます。これは別に必須ではないのですが。

このように、With ○○で括ることによって、その中にある○○部分のドットより前を省略できます。

条件1

黄色の部分です。

If .TextBox1 = "" Then 'アカウント欄が空欄の場合
  acnt = ""
Else 'アカウント欄が記入済
  acnt = "WHERE t_tweet.f_id='" & .TextBox1 & "' " 'アカウントを指定
End If

文字列型変数acnt（アカウント、のつもり）へ、ユーザーフォームのテキストボックス1に入力された値を使って条件文を書きます。テキストボックスが空の場合は、空白の文字列を入れておきます。これでテキストボックスに値が入ったときだけ、条件をつけられるというわけです。

条件2

水色の部分です。

If .TextBox2 = "" Or .TextBox3 = "" Then '日付が両方（どちらか）空欄の場合
  dy = ""
Else '日付が記入済
  If acnt = "" Then '初めての条件なら
    dy = "WHERE"
  Else '2番目なら
    dy = "AND"
  End If
  dy = dy & " t_tweet.f_day BETWEEN #" & .TextBox2 & "# AND #" & .TextBox3 & "# " '指定日絞込
End If

文字列型変数dyへ、ユーザーフォームのテキストボックス2と3に入力された日付内という条件文を書きます。テキストボックスが空の場合は、条件1と同じく空白の文字列。

もうひとつIf文が入っているのは、条件1の文字列acntが空白か否かによって、頭の文字列が変わるからです。WHEREは最初の1回だけで、それ以降の条件はANDでつなげるため、dyの頭はWHEREかAND、どちらにもなる可能性があります。

フィールド名 BETWEEN #日付1# AND #日付2#のように書くことで範囲を設定します。日付は「#」で括ります。特にエラー処理を設定していないので、テキストボックスに正しい日付型が入らないとエラーになってしまいますのでご注意ください。

条件3

ピンクの部分です。

If .TextBox4 = "" Then '検索テキストが空欄の場合
  txt = ""
Else '検索テキストが記入済
  If acnt = "" And dy = "" Then '初めての条件なら
    txt = "WHERE"
  Else '2番目以降なら
    txt = "AND"
  End If
  txt = txt & " t_tweet.f_content LIKE '%" & .TextBox4 & "%' " '～を含む
End If

文字列型変数txtへ、ユーザーフォームのテキストボックス4に入力された文字列を含むという条件文を書きます。テキストボックスが空の場合は、今までと同じで空白の文字列。

今度は条件1と2の文字列両方が空白かどうかを見ています。両方空白だったらWHERE、それ以外はANDが頭につきます。

フィールド名 LIKE '%○○%'で「○○」を含むものを抽出できます。「%」はワイルドカードなので、LIKE '○○%'と書けば前方一致、LIKE '%○○'と書けば後方一致になります。

コード

ということで、書き起こしてみると、こうなります。

Sub DBtweet() 'ツイート読み込み
  Dim acnt As String, dy As String, txt As String

  With UserForm1
    .Show 'ユーザーフォーム表示

    If .TextBox1 = "" Then 'アカウント欄が空欄の場合
      acnt = ""
    Else 'アカウント欄が記入済
      acnt = "WHERE t_tweet.f_id='" & .TextBox1 & "' " 'アカウントを指定
    End If

    If .TextBox2 = "" Or .TextBox3 = "" Then '日付が両方（どちらか）空欄の場合
      dy = ""
    Else '日付が記入済
      If acnt = "" Then '初めての条件なら
        dy = "WHERE"
      Else '2番目なら
        dy = "AND"
      End If
      dy = dy & " t_tweet.f_day BETWEEN #" & .TextBox2 & "# AND #" & .TextBox3 & "# " '指定日絞込
    End If

    If .TextBox4 = "" Then '検索テキストが空欄の場合
      txt = ""
    Else '検索テキストが記入済
      If acnt = "" And dy = "" Then '初めての条件なら
        txt = "WHERE"
      Else '2番目以降なら
        txt = "AND"
      End If
      txt = txt & " t_tweet.f_content LIKE '%" & .TextBox4 & "%' " '～を含む
    End If
  End With

  Call DBconnect 'DB接続

  'SQL文の作成
  strSQL = _
    "SELECT t_tweet.f_no, t_tweet.f_id, t_main.f_name, t_tweet.f_content, t_tweet.f_day " & _
    "FROM t_tweet LEFT JOIN t_main " & _
    "ON t_tweet.f_id = t_main.f_id " & _
    acnt & _
    dy & _
    txt & _
    "ORDER BY t_tweet.f_day DESC"

  adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ
  Range("B10:F26").ClearContents '前のデータクリア
  Range("B10").CopyFromRecordset adoRs 'セルへ出力
  Range("F10:F26").NumberFormatLocal = "yyyy/m/d h:mm;@" '書式設定

  Call DBcut_off 'DB切断
End Sub

という感じになりますね。一応コピペで動くと思うのですが…。続きは、

• レコードの削除・更新
• トランザクションの実装

を予定してます。あと2回w

続き

• ExcelVBAとAccessの連携 第5回 レコードの更新・削除