Excel VBAでMySQLからデータを取得して表示する
久しぶりにExcel VBAに触れる機会があったので、サンプルプログラムを載せます。久しぶりすぎていろいろダメなところがあるかと思いますがご愛嬌ということで^^;
環境
本投稿のサンプルプログラムは、以下の環境で作成しております。
- Windows10 Pro
- Microsoft Office 2013
- MySQL 5.7.18
事前条件
MySQLのインストールやらは今回省略しております。よろしければ、以下のブログを参考にしていただければと思います。
MySQL側に以下のテーブルを準備する
エリアマスタ（m_area）
- エリアID（id）
- エリア名（name）
都道府県マスタ（m_pref）
- 都道府県ID（id）
- エリアID（area_id） ※エリアマスタとのリレーション
- 都道府県名（name）
ODBCデータソースアドミニストレータのユーザDSNに登録する
以下は参考例です。環境に応じて読み替えてください。ドライバは「MySQL ODBC 5.3 ANSI Driver」です。DSNを設定する際には「Connector/ODBC」をインストールする必要があります。
- Data Source Name = "任意の名称" ※例：MySQL Database
- Descripiton = "未入力"
- TCP/IP Server = "localhost"
- Port = "3306"
- User = "root"
- Password = "未入力"
参照設定（Microsoft ActivX Data Objects 6.1 Library）
参照設定をする際に、2.xや6.xなどのバージョンがありますが、特に古い環境で使用する予定がなければ最新のものを参照設定してあげればよいと思います。
クラスモジュール作成
データベースからの取得結果を受け取るクラスモジュールを作成します。この辺りの実装方法は好みなので、別に無理に作らなくてもよいです。
init()はJavaで言うとコンストラクタっぽいことをやるために実装しています。VBAでもコンストラクタはあるらしいのですが、引数を設定できないようなので、以下のようにしています。
データベースから取得したレコードをこちらのオブジェクトに設定して、Collection配列に格納しています。
オブジェクト名：AreaModel
Option Explicit
Private mAreaId As Integer
Private mAreaName As String
Private mPrefId As Integer
Private mPrefName As String
Public Function init(areaId As Integer, areaName As String, prefId As Integer, prefName As String)
mAreaId = areaId
mAreaName = areaName
mPrefId = prefId
mPrefName = prefName
End Function
Public Property Get getAreaId() As Integer
getAreaId = mAreaId
End Property
Public Property Get getAreaName() As String
getAreaName = mAreaName
End Property
Public Property Get getPrefId() As Integer
getPrefId = mPrefId
End Property
Public Property Get getPrefName() As String
getPrefName = mPrefName
End Property
標準モジュール作成
MySQLへの接続、SQLの実行、セルに値を設定して表示する、といった一連の処理を作成します。connectionStringやSQL文、セルに値を設定する部分はプログラムの見た目が悪くなるので、別functionに委譲しています。
オブジェクト名：M_DB
Option Explicit
Sub selectArea()
Dim conn As New ADODB.connection
conn.CursorLocation = adUseClient
conn.connectionString = createConnectionString
conn.Open
Dim rs As ADODB.recordSet
Set rs = conn.Execute(areaSelectSql)
' DBからの取得結果を配列に格納
Dim areaList As New Collection
Dim area As AreaModel
Do While rs.EOF = False
Set area = New AreaModel
area.init rs.Fields(0), rs.Fields(1), rs.Fields(2), rs.Fields(3)
areaList.Add area
rs.MoveNext
Loop
' DBから取得したデータをセルに配置
showCells areaList
' 後始末
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub
Private Function createConnectionString()
Dim connectionString As String
connectionString = "Driver={MySQL ODBC 5.3 ANSI Driver};" _
& " Server=localhost;" _
& " Database=test201706;" _
& " Uid=root;" _
& " Pwd=;"
createConnectionString = connectionString
End Function
Private Function areaSelectSql() As String
Dim sql As String
sql = "SELECT " _
& "a.id as area_id " _
& ",a.name as area_name " _
& ",p.id as pref_id " _
& ",p.name as pref_name " _
& "FROM " _
& "test201706.m_area as a " _
& "inner join test201706.m_pref as p on a.id = p.area_id"
areaSelectSql = sql
End Function
Private Function showCells(areaList As Collection)
' 先頭行
Worksheets("Sheet1").Cells(1, 1).Value = "エリアID"
Worksheets("Sheet1").Cells(1, 2).Value = "エリア名"
Worksheets("Sheet1").Cells(1, 3).Value = "都道府県ID"
Worksheets("Sheet1").Cells(1, 4).Value = "都道府県名"
' データ部
Dim rowIdx As Integer
rowIdx = 2
Dim area As Variant
For Each area In areaList
Worksheets("Sheet1").Cells(rowIdx, 1).Value = area.getAreaId
Worksheets("Sheet1").Cells(rowIdx, 2).Value = area.getAreaName
Worksheets("Sheet1").Cells(rowIdx, 3).Value = area.getPrefId
Worksheets("Sheet1").Cells(rowIdx, 4).Value = area.getPrefName
rowIdx = rowIdx + 1
Next
End Function
