この記事では、ExcelのマクロからMySQLのテーブルデータを削除する方法についてご説明します。
【動画】ExcelのマクロからMySQLのテーブルデータを削除する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
Connectionインスタンスの生成してMySQLのデータベースへの接続情報を用意したらConnectionインスタンスのConnectionStringプロパティに代入し、Openメソッドを実行することで、マクロがMySQLのデータベースに接続しています。
マクロがMySQLのデータベースに接続したらSQLのDELETE文を実行し、Excelのセルに入力された値を条件に対象のMySQLのテーブルデータを削除しています。
マクロ作成の流れ
実行すると、ExcelのマクロがMySQLのデータベースに接続されます。
正常に処理が実行されると、削除対象のMySQLのテーブルデータが削除されます。
ExcelファイルとMySQLのテーブルデータの例
今回は次のExcelファイルを用意しました。
また、MySQLのテーブルデータは次の通りです。
テーブルにデータが10件存在しています。
今回はこのテーブルデータを、Excelのセルに入力された値を条件にマクロが対象データを削除します。
削除された結果は下の画面の通りです。
Excelには項番が2と入力されていたので、id(項番)が2のデータが削除されました。
コードの例
Option Explicit
Private Sub btn_exec_Click()
Dim cn As ADODB.Connection 'Connection用変数
Dim sqlStr As String 'SQL用変数
Dim ws As Worksheet
'シートを取得する
Set ws = Worksheets("work")
'Connectionインスタンスの生成
Set cn = New ADODB.Connection
'MySQLへの接続情報の取得
cn.ConnectionString = "Driver={MySQL ODBC 8.1 ANSI Driver};" & _
"Server=localhost;" & _
"Port=3306;" & _
"Database=testdb;" & _
"User=root;" & _
"Password=testPass@123456"
'コネクションを開く
cn.Open
'DELETE文を用意する(Excelのシートに入力された値を条件に、合致するデータを削除する)
sqlStr = "DELETE FROM syain"
sqlStr = sqlStr & " WHERE ID = " & CLng(ws.Range("valID").Value)
'DELETE文を実行する
cn.Execute sqlStr
'後処理
cn.Close
End Sub
注目すべきコード①
最初に見て頂きたいのは13行目です。
'Connectionインスタンスの生成
Set cn = New ADODB.Connection
コードの説明
以上のコードは、マクロがMySQLのデータベースに接続するための接続情報を取得して接続するコードです。
このインスタンスがないとマクロがMySQLのデータベースに接続することができないので必ず生成しておきます。
注目すべきコード②
次に見て頂きたいのは16行目から21行目です。
'MySQLへの接続情報の取得
cn.ConnectionString = "Driver={MySQL ODBC 8.1 ANSI Driver};" & _
"Server=localhost;" & _
"Port=3306;" & _
"Database=testdb;" & _
"User=root;" & _
"Password=testPass@123456"
コードの説明
以上のコードは、マクロがMySQLのデータベースに接続するための接続情報を用意するコードです。
MySQLのデータベースに接続するための接続情報はいくつかの引数が必要になります。
Driver
Driverには、マクロがMySQLのデータベースに接続するのに必要なODBCドライバーの情報を指定します。
Server
Serverには、接続したいMySQLのサーバの名称を指定します。
今回はlocalhostに接続します。
【参考：MySQL Workbenchの画面】
Port
Portには、ポートの値を指定します。
【参考：MySQL Workbenchの画面】
Database
Databaseには、接続したいデータベース名を指定します。
【参考：MySQL Workbenchの画面】
User
Userには、MySQLのデータベースに接続するためのユーザ名を指定します。
【参考：MySQL Workbenchの画面】
Password
Passwordには、MySQLのデータベースに接続するためのパスワードを指定します。
注目すべきコード③
次に見て頂きたいのは24行目です。
'コネクションを開く
cn.Open
コードの説明
以上のコードは、ConnectionインスタンスのOpenメソッドを実行して、マクロがMySQLのデータベースに接続する処理のコードです。
Connectionインスタンスには「注目すべきコード②」で説明した通り、マクロがMySQLのデータベースに接続するための接続情報をConnectionStringプロパティに代入しているので、ここではOpenメソッドを実行することでマクロがMySQLのデータベースに接続されます。
注目すべきコード④
次に見て頂きたいのは27行目から28行目です。
'DELETE文を用意する(Excelのシートに入力された値を条件に、合致するデータを削除する)
sqlStr = "DELETE FROM syain"
sqlStr = sqlStr & " WHERE ID = " & CLng(ws.Range("valID").Value)
コードの説明
以上のコードは、Excelのセルに入力された値を条件に、該当するMySQLのテーブルデータを削除するDELETE文を組み立てているコードです。
Excelのセルの値がsyainテーブルのIDの値と合致するレコードを削除します。
以上のコードだけではDELETE文が分かりにくいと思うので、実際に実行するDELETE文をサンプルで以下にお見せします。
DELETE
FROM
syain
WHERE
ID = 2
注目すべきコード⑤
次に見て頂きたいのは31行目です。
'DELETE文を実行する
cn.Execute sqlStr
コードの説明
以上のコードは、DELETE文をマクロがMySQL側で実行するよう要求し実行する処理のコードです。
ConnectionインスタンスのExecuteメソッドにDELETE文を引数に指定し実行することでMySQL側でDELETE文が実行されます。
動作確認
「ExcelファイルとMySQLのテーブルデータの例」をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの5行目の「ADODB.Connection」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim cn As ADODB.Connection 'Connection用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、ExcelのマクロからMySQLのテーブルデータを削除する方法についてご説明しました。
ExcelのマクロからMySQLのテーブルデータを削除したいときは本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
