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の読み込み編集が可能.
  英語。  

## 機能紹介  
**接続**
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/6ef2ad2a-5663-d313-c46c-a0b3edecc5e5.gif width="480" height="320">
**読み込み**
 <img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/673a0f23-ee05-2511-0bf0-520a8e10ba31.gif alt="comment" width="480" height="320">

**編集**
 <img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/5efa7211-5e4a-6d70-c80b-eec41774e6e5.gif" alt="comment" width="480" height="320">

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

## 導入方法
> If you already have a MySQL server installed.

<a href="https://www.mysql.com/jp/why-mysql/windows/excel/" target="_blank" >公式ページ</a>
<a href="https://dev.mysql.com/downloads/installer/" target="_blank" >Download Link</a>
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/185f59f6-6452-e773-f909-6ba022b7190b.png width=800px>


1. ツリーを展開して「MySQL for Excel」を選択し、右Windowに移動。  
1. Next を押す  
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/968edc2e-4149-2836-289f-3e09931403e2.png width=700px>

<u>Click Execute</u>
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/e14cb932-4cb4-cd27-3589-fb7702fbc23e.png width=700px>
> Visual Studio 2010 Tools for Office Runtime is not installed  がないと言われているので,
Executeを押して不足しているRequirementをインストール。

Accept してInstall
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/4fe35329-eb0b-adc1-5f17-b2acaac1f535.png width=400px>

Intall Now
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/87907c54-7ad6-3485-27fc-36ce929a2d5c.png width=400px>

Click Finish
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/3e71b473-88c2-891d-ded0-b96844db21b2.png width=600px>

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

<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/0f3a46f6-e6b3-6141-3eca-e2bf70e66395.png width=700px>

Click Execute
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/035ef62c-f3f4-6b78-a4cc-4834bb771caf.png width=700px>

Install完了
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/3cdb03cc-1517-c9ea-430f-4f8d098497b1.png width=700px>

# ODBC
## システム環境 
  ODBCドライバ ⇔ Excel
  ODBCドライバ ⇔  VBA
## 概要 
  Open Data Base Connectivity の略。
  ODBCドライバを使うことで、Excel・VBAとデータの入出力をすることができる。
## 機能紹介 
### ODBC 接続と読み込み(Excel)
#### 接続設定

> DSN: Data Source Name

<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/9d989caa-5e7a-1729-7f4b-e75e56b1f106.png width=500px>
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/bc8a0b8d-ded8-9029-2a29-33fd7bab891d.png width=500px>

Input Database Profile
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/a008e2d9-57a1-24a5-9ea0-52e737a24d94.png>
Execute Test
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/9d1f72c9-1a89-8a45-3267-850a0a2cd0d5.png>

#### テーブルの取り込み

|<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/c8e7648c-0fc2-9965-47a8-fde5be8b6018.png width=400px>|
|:--|

| <img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/bca872af-1d6c-737f-8d58-27a149dfe3a2.png width=600px>|
|:--|

「読み込み」を選択するとアクティブセルにテーブルを挿入。 
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/ad10a875-850c-48ff-962c-70e4161352d5.png width=600px>
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/30c6c0cf-f82a-c1f9-e12e-c807af70582d.png width=600px>

「読み込み先...」を選択すると挿入先を選択可能。
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/c9cdfa63-d988-bd27-2b79-3f8abc5ca0f2.png>

## ODBC DB操作1(VBA)

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

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

```vb

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
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/eab82ec2-3db6-5324-507b-e5f940a4a249.png>

```vb
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
```
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/b1fee188-570d-3587-cd09-0d555be5a15b.png>

### Insert Record
<img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/1797db55-020c-113f-f2b2-85ba7914e15d.png>

```vb
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

```vb
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

```vb

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

    UserForm1.Show

End Sub
```

```vb
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
```

```vb
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のインストール

<a href=https://pasocafe.space/excel-mysql-connect-2/ target=_blank>参考サイト様</a>
<a href=https://dev.mysql.com/downloads/connector/odbc/ target=_blank>Download Link</a>

`mysql-connector-odbc-8.0.21-win32.msi`

<details><summary>
Visual Studio 2019 x86 Redistributableがないとエラー </summary>

| <img src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/bfedc081-b227-357d-d91c-fbfbac0aa00d.png> |
|:--|

インストールする
<a href=https://aka.ms/vs/16/release/vc_redist.x86.exe target=_blank>https://aka.ms/vs/16/release/vc_redist.x86.exe</a><br>

再起動する
![2020-08-02-10-01-56.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/6d6b9e51-06b3-718e-7613-90ea39bc16ef.png)

</details>

![2020-08-02-09-43-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/df7f4b72-922d-5419-c9de-8583bed8f603.png)
![2020-08-02-09-43-22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/e2363fe2-d433-3e63-b02e-0a342868452e.png)

![2020-08-02-09-45-20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/2499edc4-00f9-2a3b-fc9f-aa4617d3f10c.png)

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

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

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

Successful Installation
![2020-08-02-10-11-29.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609440/fe843ed7-f281-e6ba-c0cd-24aeb30025f9.png)

---