Excel+MySQL連携:5 Excel VBAでMySQLへ接続してCRUD操作する方法

userform

久々になってしまいました(汗)
エクセルとMySQLの連携に関する備忘録です。今回は下記(5)について書きます。

(1)さくらのレンタルサーバのMySQLへ自分のwindowsパソコンからsshで接続する方法
(2)ODBCでMySQLへ接続してデータベースの情報を見る方法
(3)Power QueryでMySQLへ接続してデータベースの情報を見る方法
(4)Excel VBAでMySQLへ接続してデータベースの情報を見る方法
(5)Excel VBAでMySQLへ接続してCRUD操作する方法 ←今回の投稿

今回は、エクセルVBAのユーザフォームを利用して基本的なCRUD処理(Create:挿入、Read:読込、Update:更新、Delete:削除)を実装してみました。ユーザフォームは初めて扱うため変なコードになっている可能性も高いですがご愛嬌ということで(>_<) あと、前回まででVBAとMySQLの連携のための各種設定について説明は済んでいるため、今回はCRUD処理のためのユーザフォーム設計とコードについて解説していきます。

(1)最終形

今回は簡単なCRUD処理を実現します。
エクセルVBAのユーザフォーム自体はじめて触ったため、ググりながら組んでみました。バリデーションとか全く入ってないのでMySQLにデータを登録する際にデータの型が合わない場合はエラーがそのまま表示されますが、気にしないでください^^;

以下が最終形です。各ボタンの役割は次の通り。

データ表示一覧 ⇒ テーブルのデータ(レコードセット)を一覧表示する。
データ追加 ⇒ ボタンをクリックすると追加専用のウィンドウが表示され、商品名と価格を入力できる。
データ削除 ⇒ 「削除するデータID」に半角数字を入力しボタンをクリックすると対象のデータ(レコード)が削除される。
選択データを更新 ⇒ データ表示一覧ボタンをクリックするとテキストボックスにデータが表示される。その中の1つのレコードをクリックすると下のデータ更新の商品名と価格の欄にデータが転記され、編集可能になる。
userform
(データ追加ボタンをクリックすると以下のウィンドウが表示される)
userform
(リストボックスのデータを選択した状態)
userform

では、続けて各ボタンのコードを表示しながら簡単に説明していきますね。
同じような処理を繰り返し記述していて読みにくいですが、とりあえず動くように作っているのでご容赦ください。

(2)データ表示一覧ボタン

まずはコードです。「’」で始まる行はコメントですので参考ください。

更新用textboxを初期化する以下のコードは下図のテキストボックスに対応しています。
TextBox2.Text = “”
TextBox3.Text = “”
TextBox4.Text = “”
TextBox5.Text = “”
userform

中央のリストボックスにデータをセットする流れは、

a)SQL文でSELECTしてレコードセット(adoRs)を準備
b)いったんワークシート上にデータを転記(例:Cells(i, 1) = adoRs!goodsID)
c)転記したデータ領域をまとめてリストボックスに転記(データ一覧表.List = Range(Range(“A1”), ActiveCell.SpecialCells(xlLastCell)).Value)

となります。

(3)データ追加ボタン

はじめにデータ追加ボタンのコードです。UserForm2を呼び出すだけですね。

次に、データ追加ボタンクリック後にポップアップされるウィンドウ(UserForm2)のコードです。

データを追加する際に、追加時の日付(yyyy/mm/dd)も併せてDBに登録しています。日付を取得するにはDate関数などもありますが、windowsの設定によって日付の形式が変わってしまう恐れがあるため、ここでは、Year(Date)/Month(Date)/Day(Date)でyyyy/mm/ddの形式になるよう指定しています。

(4)データ削除ボタン

コードです。特筆するところはないかなぁ。

(5)データ更新ボタン

まずはじめに、中央のリストボックスで選択したレコード(1行分)の情報を取得し、下方の編集用テキストボックスに各データを転記するコードです。上記(2)も参照ください。

続いて、データ更新ボタンのコードです。

ここまでエクセルVBAとMySQLの連携についてシリーズで投稿してきましたが、今回で最後になります。
最後は説明不足感も否めませんが(苦笑)、エクセルVBAとMySQLの連携も意外と簡単にできるんだ、というのが実感です。MySQLはワードプレスをはじめ色々なサービスで利用されているようですので、エクセルVBA(ユーザフォーム)と組み合わせて低コストで色々できそうな感じです。

今回は、ついでにエクセルVBAのファイルをGitでバージョン管理する方法についても調べて試してみました。やっとこさGitデビューです)^o^( 方法については忘れないうちにまとめてみようかと思います。

ほか、Ruby on Railsにも興味があり学習を始めました。今後はRailsについてもちょこちょこアウトプットして行こうかと思います。

userform

いつも読んで頂きありがとうございます!

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です