MySQLとExcelの連携について

はじめに

会社でのデータ管理は、基本的にすべてExcel。
ファイルの管理や、バージョン管理が大変。

そのためWeb化＆データベース化の推進をしていきたい。
Excel文化が根強いため、ひとまずExcelベースでデータベース化を目指す。
今回は標題のとおり、ExcelとMySQLとの連携を調査。

と、言っている私もプログラミングは少しかじったレベルのため学習しつつチャレンジ。

調査環境

• Server version: 5.7.29 MySQL Community Server (GPL)
• Microsoft(R) Excel for Office 365 MSO (16.0.12527.20612) 32 ビット

MySQL for Excel

システム

Excelアドイン

概要

Excel上のアドイン機能からMySQLの読み込み編集が可能.
英語。

機能紹介

接続

読み込み

編集

レビュー

アドイン単体のインストールのため、導入は初心者でも楽
また、データベースの知識を伝えずに機能を使える。
しかし、VBAとの連携機能はなく(?)、汎用性・自動化は難しいか。

導入方法

If you already have a MySQL server installed.

公式ページ
Download Link

1. ツリーを展開して「MySQL for Excel」を選択し、右Windowに移動。
   
2. Next を押す
   

Click Execute

Visual Studio 2010 Tools for Office Runtime is not installed がないと言われているので,
Executeを押して不足しているRequirementをインストール。

Accept してInstall

Intall Now

Click Finish

Click Next >

Requirements がInstall され準備ができたので、次のStepに進めるように。

Click Execute

Install完了

ODBC

システム環境

ODBCドライバ ⇔ Excel
ODBCドライバ ⇔ VBA

概要

Open Data Base Connectivity の略。
ODBCドライバを使うことで、Excel・VBAとデータの入出力をすることができる。

機能紹介

ODBC 接続と読み込み(Excel)

接続設定

DSN: Data Source Name

Input Database Profile

Execute Test

テーブルの取り込み

「読み込み」を選択するとアクティブセルにテーブルを挿入。

「読み込み先...」を選択すると挿入先を選択可能。

ODBC DB操作1(VBA)

1. SQL_CONN でユーザー情報とDB名を設定。
2. SQL_Order でSQL文を設定。
3. SQL_Orderを実行する。
   
   • CREATE、INSERTはSQL文がそのまま実行される。
     
   • SELECTのときは取得したレコードをRecords(配列変数)に返すので、お好みで調理。
     

• SQL文ごとに SQL_Orderを複製して使用することを想定。

Sub SQL_Order()
' このプロシージャでSQL文を設定して、RunSQL(Function)に渡すと、 SQL文を実行する。'
' また、SQL文がSELECTの場合は、Records(配列)に結果を返す。'
' データベースの設定は、SQL_CONN(Function)で行う。'

    Dim SQL As String
    Dim Records As Variant

' SELECT'
    SQL = "SELECT * FROM usage_history"
    retVal = RunSQL(SQL, Records)

End Sub
'----------------------------------------------------------'
Function RunSQL(ByVal SQL, ByRef Records)
' SQL処理のコントローラ。'
' Conn(DB接続) → Exec(SQL実行)'
'   → GetDataArray(データを配列に取得) → Close(後処理)'

    On Error GoTo ErrorTrap
    Dim adoCon As Object ' ADOコネクション'
    Dim adoRs As Object  ' ADOレコードセット'
    Dim i As Long
' Conn'
    retVal = SQL_CONN(adoCon, adoRs)
' Exec'
    retVal = SQL_EXEC(adoCon, adoRs, SQL)
' Get Data Array'
    If LCase(Left(SQL, 6)) = "select" Then ' SELECTのときだけ配列生成'
        ReDim Records(adoRs.RecordCount, adoRs.Fields.Count - 1)
        retVal = SQL_RECORD(adoRs, Records)
' Close'
        retVal = SQL_CLOSE(adoCon, adoRs)
    Else
        adoCon.Close:  Set adoCon = Nothing
    End If
Exit Function

ErrorTrap:
    MsgBox (Err.Description)
End Function
'----------------------------------------------------------'
Function SQL_CONN(ByRef adoCon, ByRef adoRs)

    On Error GoTo ErrorTrap

' ADOコネクションを作成'
    Set adoCon = CreateObject("ADODB.Connection")

' ODBC接続'
    adoCon.Open _
        "DRIVER={MySQL ODBC 8.0 Unicode Driver};" & _
        " SERVER=localhost;" & _
        " PORT=3306;" & _
        " DATABASE=admin_pc_usage;" & _
        " STMT=SET NAMES sjis;" & _
        " UID=root;" & _
        " PWD=;"
Exit Function
ErrorTrap:
    Set adoRs = Nothing
    Set adoCon = Nothing
    MsgBox (Err.Description)
End Function
'----------------------------------------------------------'
Function SQL_EXEC(ByRef adoCon, ByRef adoRs, ByVal SQL)
'adoRs.RecordCountを取得するため、カーソルの設定をクライアント&静的に変更。'

' SQLの実行'
    Set adoRs = CreateObject("ADODB.Recordset")
    adoRs.CursorLocation = adUseClient
    adoRs.Open _
            Source:=SQL, _
            ActiveConnection:=adoCon, _
            CursorType:=adOpenStatic
End Function
'----------------------------------------------------------'
Function SQL_RECORD(ByVal adoRs, ByRef Records)
' 配列を生成する。1行目に列タイトル、2行目～にレコード。'
    i = 0
    Do Until adoRs.EOF
        For j = 0 To adoRs.Fields.Count - 1
            If i = 0 Then: Records(i, j) = adoRs.Fields(j).Name
            Records(i + 1, j) = adoRs(j).Value
        Next j
        adoRs.MoveNext
        i = i + 1
    Loop
End Function
'----------------------------------------------------------'
Function SQL_CLOSE(ByVal adoCon, ByVal adoRs)
On Error GoTo ErrorTrap3

' 解放処理'
    adoRs.Close
    adoCon.Close
    Set adoRs = Nothing
    Set adoCon = Nothing
ErrorTrap3:
    Set adoRs = Nothing
    Set adoCon = Nothing
    MsgBox (Err.Description)
End Function
'----------------------------------------------------------'

ODBC DB操作2(VBA)

Create Table

Sub Order_CreateTable()
' このプロシージャでSQL文を設定して、RunSQL(Function)に渡すと、 SQL文を実行する。'
' また、SQL文がSELECTの場合は、Records(配列)に結果を返す。'
' データベースの設定は、SQL_CONN(Function)で行う。'

    Dim SQL As String
    Dim Records As Variant

' CREATE'
    DataCnt = Range("C6").End(xlDown).Row - 6

    SQL = "Create Table " & Range("D4") & "(" '& vbCrLf'

    For r = 0 To DataCnt - 1
    ' Col_Name'
        buf = "`" & Range("C" & r + 7) & "`" & " "
        SQL_BODY = SQL_BODY + buf
    ' TYPE'
        buf = Range("D" & r + 7) & " "
        SQL_BODY = SQL_BODY + buf
    ' NULL'
        If Range("E" & r + 7) = 1 Then: _
        SQL_BODY = SQL_BODY + "NOT NULL "
    ' Auto_Increment'
        If Range("F" & r + 7) = 1 Then
            SQL_BODY = SQL_BODY + "AUTO_INCREMENT "
            mem_AI = Range("C" & r + 7)
        End If
    ' Comment'
        If Range("H" & r + 7) <> "" Then
            SQL_BODY = SQL_BODY + "COMMENT '" & Range("H" & r + 7) & "'"
        End If
    ' Next'
        SQL_BODY = SQL_BODY & "," '& vbCrLf'
        SQL_BODY = Replace(SQL_BODY, " ,", ",")
    Next r

    SQL = SQL & SQL_BODY
    If mem_AI <> "" Then: _
    SQL = SQL & "PRIMARY KEY (`" & mem_AI & "` ))"

    retVal = RunSQL(SQL, Records)

End Sub

Insert Record

Sub Order_InsertRecord()

    Dim SQL As String
    Dim Records As Variant

' INSERT'
    'INSERT INTO admin_pc_usage.年休申請'
    '(申請者, 申請日, 種別, 備考, 登録日)'
    'VALUES('', '', '', '', '');'
    SQL = "insert into " & Range("D6") & "(申請者, 申請日, 種別, 備考, 登録日) " & " values "

    buf = "('" & Range("D8") & "','" & Range("D9") & "','" & Range("D10") & "','" _
    & Range("D11") & "','" & Now() & "');"

    SQL = SQL & buf

    retVal = RunSQL(SQL, Records)

End Sub

Select

Sub Order_SELECT()

    Dim SQL As String
    Dim Records As Variant

' SELECT'
    SQL = "SELECT * FROM " & Range("D4")

    retVal = RunSQL(SQL, Records)
    WriteRecords (Records)

End Sub

UPDATE

Sub Order_getRecord()
' UPDATE するレコードを選択して取得'

    UserForm1.Show

End Sub

Private Sub CommandButton2_Click()
    Dim lRow As Long, i As Long
    Dim ListNo As Long
        ListNo = ListBox1.ListIndex

        If ListNo <= 0 Then
            MsgBox "いずれかの行を選択してください"
            Exit Sub
        End If

        With Worksheets("UPDATE")
            .Range("D7") = ListBox1.List(ListNo, 0)
            .Range("D8") = ListBox1.List(ListNo, 1)
            .Range("D9") = ListBox1.List(ListNo, 2)
            .Range("D10") = ListBox1.List(ListNo, 3)
            .Range("D11") = ListBox1.List(ListNo, 4)
        End With

        Unload UserForm1
End Sub

Private Sub CommandButton3_Click()
    Unload UserForm1
End Sub

Private Sub UserForm_Initialize()
    Dim SQL As String
    Dim Records As Variant
' SELECT'
    SQL = "SELECT * FROM 年休申請"
    retVal = RunSQL(SQL, Records)

    Header = WorksheetFunction.Index(Records, 1)

    With ListBox1
        .ColumnCount = 6
        .ColumnWidths = "40;40;60;40;80;70"
        .List = Records
    End With

    ListBox1.List = Records

End Sub

Sub Order_UPDATE()

    Dim SQL As String
    Dim Records As Variant

' UPDATE'
    'Update 年休申請'
    'SET 申請者='', 申請日='', 種別='', 備考='', 登録日='''
    'WHERE `申請ID`=0;'
    SQL = "Update " & Range("D4") & _
                " SET 申請者='" & Range("D8") & "', 申請日='" & Range("D9") & "', 種別='" & Range("D10") & _
                    "', 備考='" & Range("D11") & "', 登録日='" & Now() & "'" & _
                " WHERE `申請ID`=" & Range("D7") & ";"
    retVal = RunSQL(SQL, Records)

End Sub

レビュー

• 導入はインストーラを統一すれば簡単。
  
  • Excel上で使用する場合は、ユーザーDSNの設定が必要だが、手順書でカバー可能なレベル。
    
• 開発は前者(MySQL for Excel) より難易度高いが、割と簡単な部類。
  
  • 要VBA、SQL文基礎。ネット検索で方法がすぐ出てくるレベル。
• 機能性は、VBAでCRUD全てできるので充分。
  
  • 運用面もVBAで処理できるため期待大。
    

ODBCのインストール

参考サイト様
Download Link

mysql-connector-odbc-8.0.21-win32.msi

Visual Studio 2019 x86 Redistributableがないとエラー

インストールする
https://aka.ms/vs/16/release/vc_redist.x86.exe

再起動する

ODBCドライバのインストール確認(windowsが64bitの場合)

1. C:\Windows\SysWOW64\odbcad32.exe のodbcad32.exeをダブルクリックで実行。
2. ドライバータブをクリックし「MySQL ODBC 8.0 Unicode Driver」があればインストールは成功。

「8.0」はバージョン番号のため環境により違いあり。

Successful Installation

---