目次
Excelで変数を利用して、お好みのデータを抽出
こんにちは。伊川(@naonaoke)です。
今回は、ACCESSからExcelへ、お好みのデータを転送する方法を紹介します。
問題提起
ExcelとACCESSは仲良し
ExcelとACCESSは、VBAにおいて連携することができます。
この技を知っておくと、集計は本当に楽になります。
今回の上司の依頼は、MT_aaa MT_xxxというデータを状況によって取り込みたいという命令です。
この場合の解決策は、2通りです。
・データベースの分だけ、Excelを作成する。
今回は、MT_aaa MT_xxxと2つしかありません。
しかし、データベースが10個分あったら、10個のExcelを作成することになります。
現実的ではありません。
・テータベースを抽出するたびに、VBAのエディタを開いてコードを書き換える。
はっきり言って面倒です。
これも現実的ではありません。
変数を使う
結論から言いますと、変数を使うことで解決します。
変数に関しては、下記の記事を参照してください。
今回の取込にての注意点は下記の通りです。
事前設定 その1
VBAエディタの画面を開いて、「ツール」⇒「参照設定」を選択します。
Microsoft ActiveX Data Objects 6.1 Library
これを設定しないと、ExcelとACCESSの連携はできません。
事前設定 その2
ExcelとACCESSが必ず同一フォルダの、同一階層にあることが必要です。
同一フォルダの、同一階層にExcelのファイル、ACCESSのファイルが別々になると、伊川の提供するサンプルファイルは動きません。
事前設定 その3
コンボボックスを事前に設定しておきましょう。
http://keiyu.xyz/2019/09/26/excel%e3%81%ae%e9%80%a3%e5%8b%95%e3%83%97%e3%83%ab%e3%83%80%e3%82%a6%e3%83%b3%e3%81%ae%e4%bd%9c%e6%88%90/
転送のコード
Sub データ取り込み()
Worksheets("取込").Range("A6:C20").Clear
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim conStr As String
Dim a As String
a = Range("A1")
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ActiveWorkbook.Path & "\20191023.accdb"
con.Open ConnectionString:=conStr
rs.Open Source:=a, ActiveConnection:=con, _
CursorType:=adOpenKeyset, LockType:=adLockOptimistic
Worksheets("取込").Range("A6").CopyFromRecordset Data:=rs
Worksheets("取込").Range("A5") = "ID"
Worksheets("取込").Range("B5") = "項目_1"
Worksheets("取込").Range("C5") = "項目_2"
MsgBox "データ取込完了"
End Sub
今回のコードのポイント
この赤枠の中で、変数を設定しています。
この変数aが、MT_aaa MT_xxxに変化するわけです。
そうすると、データベース分のExcelを作成することや、データベースを変更するたびに、
VBAエディタを開いて、コードを修正する必要もありません。
これで、完璧です。
まとめ
これは、動画を観ていただければわかるとおもいますが、もともとは競馬ネタです。
各競馬上のデータを取り込むために考えました。
大げさなことではなく、常に、ループ、変数を使って、VBAのコードを短く、わかりやすくしようと考えた結果です。
ぜひ、みなさんの業務に役立てて欲しいと思います。
いきなり、ACCESSへ転送するコードが出てきましたが、毎日更新しようとおもっています。
阿久 世子男（アク セスオ）の成長をみまもってください。
サンプルファイルNo20はです
