最近仕事でVBAを使い始めました。
ほとんど個人的な備忘録ですが、もしかしたら何かの役に立つかもしれないので一応公開しています。
間違えてたらすみません。
Contents
まず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冊です。
今さら聞けない「基本のき」まで丁寧に説明してくれています。
