久々になってしまいました（汗）
エクセルとMySQLの連携に関する備忘録です。今回は下記（５）について書きます。
（１）さくらのレンタルサーバのMySQLへ自分のwindowsパソコンからsshで接続する方法
（２）ODBCでMySQLへ接続してデータベースの情報を見る方法
（３）Power QueryでMySQLへ接続してデータベースの情報を見る方法
（４）Excel VBAでMySQLへ接続してデータベースの情報を見る方法
（５）Excel VBAでMySQLへ接続してCRUD操作する方法 ←今回の投稿
今回は、エクセルVBAのユーザフォームを利用して基本的なCRUD処理（Create:挿入、Read：読込、Update：更新、Delete：削除）を実装してみました。ユーザフォームは初めて扱うため変なコードになっている可能性も高いですがご愛嬌ということで(>_<)
あと、前回まででVBAとMySQLの連携のための各種設定について説明は済んでいるため、今回はCRUD処理のためのユーザフォーム設計とコードについて解説していきます。
（１）最終形
今回は簡単なCRUD処理を実現します。
エクセルVBAのユーザフォーム自体はじめて触ったため、ググりながら組んでみました。バリデーションとか全く入ってないのでMySQLにデータを登録する際にデータの型が合わない場合はエラーがそのまま表示されますが、気にしないでください＾＾；
以下が最終形です。各ボタンの役割は次の通り。
データ表示一覧 ⇒ テーブルのデータ（レコードセット）を一覧表示する。
データ追加 ⇒ ボタンをクリックすると追加専用のウィンドウが表示され、商品名と価格を入力できる。
データ削除 ⇒ 「削除するデータID」に半角数字を入力しボタンをクリックすると対象のデータ（レコード）が削除される。
選択データを更新 ⇒ データ表示一覧ボタンをクリックするとテキストボックスにデータが表示される。その中の１つのレコードをクリックすると下のデータ更新の商品名と価格の欄にデータが転記され、編集可能になる。
（データ追加ボタンをクリックすると以下のウィンドウが表示される）
（リストボックスのデータを選択した状態）
では、続けて各ボタンのコードを表示しながら簡単に説明していきますね。
同じような処理を繰り返し記述していて読みにくいですが、とりあえず動くように作っているのでご容赦ください。
（２）データ表示一覧ボタン
まずはコードです。「’」で始まる行はコメントですので参考ください。
Private Sub データ一覧表示_Click()
Dim adoCon As Object ' ADOコネクション
Dim adoRs As Object ' ADOレコードセット
Dim SQL As String ' SQL
Dim i As Long
'更新用textboxを初期化
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
' ADOコネクションを作成
Set adoCon = CreateObject("ADODB.Connection")
On Error GoTo ErrorTrap
' ODBC接続
adoCon.Open _
"DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
" SERVER=localhost;" & _
" DATABASE=データベース名;" & _
" UID=DBユーザ名;" & _
" PWD=DBパスワード;"
'MsgBox "データ一覧を表示します"
Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).ClearContents
' SQL文
SQL = "SELECT goodsID,goodsName,goodsPrice,addDate FROM goods"
' SQLの実行
Set adoRs = adoCon.Execute(SQL)
' レコードセット内の全ての行の読込が
' 終了するまで処理を繰り返す
i = 1
Do Until adoRs.EOF
Cells(i, 1) = adoRs!goodsID
Cells(i, 2) = adoRs!goodsName
Cells(i, 3) = adoRs!goodsPrice
Cells(i, 4) = Format(adoRs!addDate, "yyyy/mm/dd")
i = i + 1
' 次の行に移動する
adoRs.MoveNext
Loop
'一気にリストボックスにデータを追加する
データ一覧表.List = Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Value
' 解放処理
adoRs.Close
adoCon.Close
Set adoRs = Nothing
Set adoCon = Nothing
Exit Sub
ErrorTrap:
Set adoRs = Nothing
Set adoCon = Nothing
MsgBox (Err.Description)
End Sub更新用textboxを初期化する以下のコードは下図のテキストボックスに対応しています。
TextBox2.Text = “”
TextBox3.Text = “”
TextBox4.Text = “”
TextBox5.Text = “”
中央のリストボックスにデータをセットする流れは、
a)SQL文でSELECTしてレコードセット（adoRs）を準備
b)いったんワークシート上にデータを転記（例：Cells(i, 1) = adoRs!goodsID）
c)転記したデータ領域をまとめてリストボックスに転記（データ一覧表.List = Range(Range(“A1”), ActiveCell.SpecialCells(xlLastCell)).Value）
となります。
（３）データ追加ボタン
はじめにデータ追加ボタンのコードです。UserForm2を呼び出すだけですね。
Private Sub データ追加_Click()
UserForm2.Show
End Sub次に、データ追加ボタンクリック後にポップアップされるウィンドウ（UserForm2）のコードです。
Private Sub 新規追加_Click()
Dim adoCon As Object ' ADOコネクション
Dim SQL As String ' SQL
Dim RecordsAffected As Long ' 変更された行数
Dim i As Long
Dim result As Long
' 定数
Const adExecuteNoRecords = &H80
' ADOコネクションを作成
Set adoCon = CreateObject("ADODB.Connection")
On Error GoTo ErrorTrap
' ODBC接続
adoCon.Open _
"DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
" SERVER=localhost;" & _
" DATABASE=データベース名;" & _
" UID=DBユーザ名;" & _
" PWD=DBパスワード;"
result = MsgBox("本当にデータを追加してもいいですか？", vbYesNo + vbExclamation + vbDefaultButton2)
If result = vbYes Then
' SQL文
SQL = "INSERT INTO goods (goodsID,goodsName,goodsPrice,addDate) " & _
" VALUES(null,'" & TextBox1.Text & "'," & TextBox2.Text & ",'" & _
Year(Date) & "/" & Month(Date) & "/" & Day(Date) & " ')"
' SQLの実行
' adExecuteNoRecordsは行を返さないのでパフォーマンスが向上
adoCon.Execute SQL, RecordsAffected, adExecuteNoRecords
' RecordsAffectedには変更された行数が返される
Debug.Print "変更された行数:" & CStr(RecordsAffected) & "行"
Unload Me
Else
Unload Me
End If
' 解放処理
adoCon.Close
Set adoCon = Nothing
Exit Sub
ErrorTrap:
Set adoCon = Nothing
MsgBox (Err.Description)
End Subデータを追加する際に、追加時の日付（yyyy/mm/dd）も併せてDBに登録しています。日付を取得するにはDate関数などもありますが、windowsの設定によって日付の形式が変わってしまう恐れがあるため、ここでは、Year(Date)/Month(Date)/Day(Date)でyyyy/mm/ddの形式になるよう指定しています。
（４）データ削除ボタン
コードです。特筆するところはないかなぁ。
Private Sub データ削除_Click()
Dim adoCon As Object ' ADOコネクション
Dim SQL As String ' SQL
Dim RecordsAffected As Long ' 変更された行数
Dim i As Long
Dim result As String
' 定数
Const adExecuteNoRecords = &H80
' ADOコネクションを作成
Set adoCon = CreateObject("ADODB.Connection")
On Error GoTo ErrorTrap
' ODBC接続
adoCon.Open _
"DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
" SERVER=localhost;" & _
" DATABASE=データベース名;" & _
" UID=DBユーザ名;" & _
" PWD=DBパスワード;"
result = MsgBox("本当にデータを削除してもいいですか？", vbYesNo + vbExclamation + vbDefaultButton2)If result = vbYes Then
' SQL文
SQL = "DELETE FROM goods WHERE goodsID=" & TextBox1.Text
' SQLの実行
' adExecuteNoRecordsは行を返さないのでパフォーマンスが向上
adoCon.Execute SQL, RecordsAffected, adExecuteNoRecords
' RecordsAffectedには変更された行数が返される
Debug.Print "変更された行数:" & CStr(RecordsAffected) & "行"
End If
' 解放処理
adoCon.Close
Set adoCon = Nothing
Exit Sub
ErrorTrap:
Set adoCon = Nothing
MsgBox (Err.Description)
End Sub
（５）データ更新ボタン
まずはじめに、中央のリストボックスで選択したレコード（1行分）の情報を取得し、下方の編集用テキストボックスに各データを転記するコードです。上記（２）も参照ください。
Private Sub データ一覧表_Click()
Dim sIndex
sIndex = データ一覧表.ListIndex
TextBox2.Text = データ一覧表.List(sIndex, 0)
TextBox3.Text = データ一覧表.List(sIndex, 1)
TextBox4.Text = データ一覧表.List(sIndex, 2)
TextBox5.Text = データ一覧表.List(sIndex, 3)
End Sub続いて、データ更新ボタンのコードです。
Private Sub 選択データを更新_Click()
Dim adoCon As Object ‘ ADOコネクション
Dim SQL As String ‘ SQL
Dim RecordsAffected As Long ‘ 変更された行数
Dim i As Long
Dim result As Long
‘ 定数
Const adExecuteNoRecords = &H80
‘ ADOコネクションを作成
Set adoCon = CreateObject(“ADODB.Connection”)
On Error GoTo ErrorTrap
‘ ODBC接続
adoCon.Open _
“DRIVER={MySQL ODBC 5.3 Unicode Driver};” & _
“ SERVER=localhost;” & _
“ DATABASE=データベース名;” & _
“ UID=DBユーザ名;” & _
“ PWD=DBパスワード;”
result = MsgBox(“本当にデータを更新してもいいですか？”, vbYesNo + vbExclamation + vbDefaultButton2)
If result = vbYes Then
‘ SQL文
SQL = “UPDATE goods SET goodsName = ‘” & TextBox3.Text & “’, goodsPrice = “ & TextBox4.Text & “, addDate = ‘” & TextBox5.Text & “’ WHERE goodsID = “ & TextBox2.Text
‘ SQLの実行
‘ adExecuteNoRecordsは行を返さないのでパフォーマンスが向上
adoCon.Execute SQL, RecordsAffected, adExecuteNoRecords
‘ RecordsAffectedには変更された行数が返される
Debug.Print “変更された行数:” & CStr(RecordsAffected) & “行”
End If
‘ 解放処理
adoCon.Close
Set adoCon = Nothing
Exit Sub
ErrorTrap:
Set adoCon = Nothing
MsgBox (Err.Description)
End SubここまでエクセルVBAとMySQLの連携についてシリーズで投稿してきましたが、今回で最後になります。
最後は説明不足感も否めませんが（苦笑）、エクセルVBAとMySQLの連携も意外と簡単にできるんだ、というのが実感です。MySQLはワードプレスをはじめ色々なサービスで利用されているようですので、エクセルVBA（ユーザフォーム）と組み合わせて低コストで色々できそうな感じです。
今回は、ついでにエクセルVBAのファイルをGitでバージョン管理する方法についても調べて試してみました。やっとこさGitデビューです)^o^( 方法については忘れないうちにまとめてみようかと思います。
ほか、Ruby on Railsにも興味があり学習を始めました。今後はRailsについてもちょこちょこアウトプットして行こうかと思います。
