システム屋さんの備忘録: 6月 2017

Excel VBAでMySQLからデータを取得して表示する

久しぶりにExcel VBAに触れる機会があったので、サンプルプログラムを載せます。久しぶりすぎていろいろダメなところがあるかと思いますがご愛嬌ということで^^;

環境

本投稿のサンプルプログラムは、以下の環境で作成しております。

Windows10 Pro
Microsoft Office 2013
MySQL 5.7.18

事前条件

MySQLのインストールやらは今回省略しております。よろしければ、以下のブログを参考にしていただければと思います。

Windows10にMySQL5.7をインストール

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」をインストールする必要があります。

https://dev.mysql.com/downloads/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

システム屋さんの備忘録

2017年6月29日木曜日

Excel VBAでMySQLからデータを取得する