素人プログラミング備忘録

ExcelのVBAで、SQL内の条件をセル内の日付で指定してシートに貼り付ける方法【VBA備忘録】

プログラミング01

最近仕事でVBAを使い始めました。
ほとんど個人的な備忘録ですが、もしかしたら何かの役に立つかもしれないので一応公開しています。
間違えてたらすみません。

まずExcelをAccessデータベースに接続する。

接続準備:ADODBライブラリの追加

VBEメニューから
「ツール」→「参照設定」を開く。

「Microsoft ActiveX Data Objects x.x Library」(x.xは最新のやつ)にチェックを入れて「OK」

ExcelをAccessに接続する記述。

下の記述は、

  • ExcelからAccessのデータベースに接続
  • Accessからの切断
  • オブジェクトの破棄

を行うものです。

Sub
    Dim 名前変数 As String
    
    名前変数 = "データベースのファイル名"

    Dim オブジェクト変数 As Object
    Set オブジェクト変数 = CreateObject("ADODB.Connection") 'ADODBコネクションオブジェクト作成
    オブジェクト変数.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=データベースファイルのフルパス;"  'Accessファイル接続


 
    オブジェクト変数.Close 'コネクションクローズ 
    Set オブジェクト変数 = Nothing  'オブジェクト破棄

End Sub 

 

ここの間にやりたいことを記述していくということになります。

SQLでテーブルからレコードを取得。

レコードセットオブジェクトを作成。

データベースからSQLを使ってレコードを引っ張ってくるわけですが、そのために一旦「レコードセット」なるものを作ってそこに格納させる必要があります。
そのための記述が以下。

    Dim レコードセット用の変数 As Object 'ADOレコードセットオブジェクト
    Set レコードセット用の変数 = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクト作成
 

 
    レコードセット用の変数.Close 'レコードセットクローズ

SQLでレコードを抽出する記述を間に書き加えていくことになります。

上記2つを併せたものが、

Sub
    Dim 名前変数 As String
    
    名前変数 = "データベースのファイル名"

    Dim オブジェクト変数 As Object
    Set オブジェクト変数 = CreateObject("ADODB.Connection") 'ADODBコネクションオブジェクト作成
    オブジェクト変数.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=データベースファイルのフルパス;"  'Accessファイル接続

    Dim レコードセット用の変数 As Object 'ADOレコードセットオブジェクト
    Set レコードセット用の変数 = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクト作成
 

 
    レコードセット用の変数.Close 'レコードセットクローズ
 
    オブジェクト変数.Close 'コネクションクローズ 
    Set オブジェクト変数 = Nothing  'オブジェクト破棄

End Sub 

これで、

  • ExcelからAccessに接続
  • SQLで引っ張るレコードセットの生成
  • レコードセット終了
  • Accessから切断
  • オブジェクトの破棄

が行われます。

これを実行しても箱を作って壊すだけですので見た目は特に何も起こりません。

SQLでレコードを取得。

SQL用の変数を宣言してその中身を決めていきます。

    Dim SQL用の変数 As String
    
    SQL用の変数 = "SELECT レコード名1," & _
                         "レコード名2," & _
                         "レコード名3 " & _
                    "FROM テーブル名 " & _
  • SELECT」はレコードの指定
  • FROM」はテーブルの指定
  • 右にたくさんある「” & _」は改行のサイン

このままではレコードを全部引っ張ってくるので、このあとに「WHERE」を使って条件を指定します。

日付を抽出条件として指定するには「#」で囲んでやらなければいけません。

    WHERE フィールド名 = #2018/03/01#
    WHERE フィールド名 BETWEEN #2019/3/1# AND #2019/3/20#

Excelのセルを直接WHEREにはめ込むのは無理なので変数を使う。

よくわからないので最初はSQLの中身に直接セルの値を打ち込んでみましたがやっぱりダメでした。
しかしVBAには変数というウルテクがありますのでそれを使わない手はありません。

Dim 変数1 As Date '開始日
Dim 変数2 As Date '終了日

とりあえず何がしかの変数をDate属性で宣言します。
ここでは開始日と終了日を指定しますが特定の日付1つならもちろん変数も1つでOKです。

Dim SQL用の変数 As String
    
SQL用の変数 = "SELECT レコード名1," & _
                    "レコード名2," & _
                    "レコード名3 " & _
               "FROM テーブル名 " & _
              "WHERE テーブル名 BETWEEN " & "#" & Format(変数1, "yyyy/mm/dd") & "#" & _
                                  "AND " & "#" & Format(変数2, "yyyy/mm/dd") & "#" & ""

レコードセット用の変数.Open SQL用の変数, adoCn 'SQL実行、対象をRecordSetへ

 

そのまま変数を入れてもダメだったので「Format関数」なるものを使って形を整えてあげます。
ここでは「年/月/日」にしていますが、SQLでは割と何でもいいみたいです。
データベースの方の形に合わせるのが無難だと思います。

紆余曲折ありましたがとりあえずこれでデータを引っ張ってくるのは行けました。

取得したデータをExcelに吐き出す。

データの取得はできましたが、次は取得したレコードをシートに出力します。

三本松
三本松
もっと詳しくなればわざわざ出力しないで直接数値を読み込めるようになるんでしょうねぇ…。
Worksheets("シート名").Range("セル位置").CopyFromRecordset レコードセット用の変数

これで、指定シートの指定位置を一番左上とした表がズラっと貼り付けられます。

全部書くとこうなります。

  • ExcelからデータベースとなるAccessのファイルに接続
  • SQLで、Excelのセル内で指定した日付条件でレコードを取得
  • ワークシートに結果を吐き出す。
    Dim 日付変数1 As Date '開始日
    Dim 日付変数2 As Date '終了日
    Dim 名前変数 As String

    日付変数1 = Worksheets("シート名").Range("セル位置").Value '開始日を入れたセルの位置
    日付変数2 = Worksheets("シート名").Range("セル位置").Value '終了日を入れたセルの位置
    
    名前変数 = "データベースのファイル名"
 
    Dim オブジェクト変数 As Object
    Set オブジェクト変数 = CreateObject("ADODB.Connection") 'ADODBコネクションオブジェクト作成
    オブジェクト変数.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=データベースファイルのフルパス;"  'Accessファイル接続
 
    Dim レコードセット用変数 As Object 'ADOレコードセットオブジェクト
    Set レコードセット用変数 = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクト作成
 
    Dim SQL用変数 As String
    
    SQL用変数 = "SELECT レコード名1," & _
                       "レコード名2," & _
                       "レコード名3 " & _
             "FROM テーブル名 " & _
             "WHERE テーブル名 BETWEEN " & "#" & Format(変数1, "yyyy/mm/dd") & "#" & _
                                         "AND " & "#" & Format(変数2, "yyyy/mm/dd") & "#" & ""
 
    レコードセット用変数.Open SQL用変数, オブジェクト変数 'SQL実行、対象をRecordSetへ
    Worksheets("シート名").Range("セル位置").CopyFromRecordset レコードセット用変数
 
    レコードセット用変数.Close 'レコードセットクローズ
    オブジェクト変数.Close 'コネクションクローズ
 
    Set レコードセット用変数 = Nothing
    Set オブジェクト変数 = Nothing  'オブジェクト破棄
    

変数は適当なアルファベットを決めて下さい。
日本語のままでは多分動きません。

私が書いた元のVBAはこれで動きましたので多分大丈夫だと思います。
間違っていたらすみません。自信はありません。

VBAもSQLも素人の私が現在読んでいる本がこちらの2冊です。
今さら聞けない「基本のき」まで丁寧に説明してくれています。

ABOUT ME
三本松
三本松
北海道根室市出身、東京都内在住の中年妻子持ちサラリーマンです。 せっかく大学まで出たのに若いうちの時間を浪費してしまいましたが、自分で納得の行く人生を取り戻すべく現在も奮闘中です。 主に過去の失敗から学んだことを記事にして発信しています。