Excel+MySQL連携：４ Excel VBAでMySQLへ接続してデータベースの情報を見る方法

Published in

pasocafe

9 min readDec 26, 2015

エクセルとMySQLの連携に関する備忘録です。今回は下記（４）について書きます。今回からExcel VBAが登場します＾＾

（１）さくらのレンタルサーバのMySQLへ自分のwindowsパソコンからsshで接続する方法
（２）ODBCでMySQLへ接続してデータベースの情報を見る方法
（３）Power QueryでMySQLへ接続してデータベースの情報を見る方法
（４）Excel VBAでMySQLへ接続してデータベースの情報を見る方法 ←今回の投稿
（５）Excel VBAでMySQLへ接続してCRUD操作する方法

ステップ１：Excel VBAとは？

Excel VBA（Visual Basic for Applications）って知っていましたか？
私も恥ずかしながら数カ月前までは、存在は知っていましたが、どんなものか全く知りませんでした。とりあえずプログラミング的なモノという捉え方でした。
しかしながら、スクーの授業で田中さんの授業を見た時に、おぉ！これはいい！と実感しました。何が良いかと言いますと、

・会社などでエクセルのセルをコピペして同じ内容の作業を繰り返す時に、VBAで繰り返し作業をプログラムすることであっという間に作業が完了できてしまうこと。
・普段エクセルで使っている関数（正式にはワークシート関数）をVBAで呼び出して使えること。
・ワークシートの構成がMySQL等のデータベース（DB）の概念を理解するのに非常に役立つこと。
・VBAはプログラミング言語なので、これを習得すると自然と論理的思考が身につき、どんな仕事においてもスタートとゴールまでの過程を細分化し、論理的に組み立てることができるようになること。
・エクセルというマイクロソフトオフィスさえインストールされていれば、誰でも無料で使えること。

といったことが非常にいいですね。最近、小学校などの授業でプログラミングが入ったり、キッズ向けのプログラミング教室などが増えてきているようですが、エクセルもその入口の１つにしてもいいかもしれませんね。

ステップ２：事前準備を行おう

Excel VBAでMySQLに接続するには次の２つの過去記事を参考に、さくらのレンタルサーバにSSHで接続できるようにして、ODBCドライバをインストールしておく必要があります。

（１）さくらのレンタルサーバのMySQLへ自分のwindowsパソコンからsshで接続する方法
（２）ODBCでMySQLへ接続してデータベースの情報を見る方法

以下は、上記２つが済んだ状態を前提に書いていきます。

ステップ３：PortForwarderを起動しよう

ステップ２の（１）で導入したPortForwarderを起動します。
起動することで、手元のパソコンからさくらのレンタルサーバにSSHで接続し、localhostのポート：３３０６にアクセスされたとき、さくらのレンタルサーバのデータベースサーバのポート：３３０６に自動で転送されます。
タスクトレイにPortForwarderのアイコンがあればOKです。

ステップ４：テスト用テーブルを作成する

テスト用にテーブルを準備します。私はさくらのレンタルサーバで標準で使えるphpMyAdminを使って簡単なテーブルを作成しました。
さくらのレンタルサーバのコントロールパネルの「データベースの設定」で、以下で示したところをクリックするとphpMyAdminが開きます。

今回は適当にこんなテーブルを作成しました。
※テーブルの作成方法はgoogleで検索してみてください。

ステップ５：VBAでマクロを作りましょう

マクロとはエクセルの機能の１つで、VBAで記述したプログラムを実行するためのものです。ですので、マクロ＝VBAではないのでご注意を！

(a)マクロを実行するためのボタンを作る

まず、まっさらなワークシートを準備します。
次に、上部のタブに「開発」タブがあるか確認してください。ない場合はこちらを参照して表示させてください。
開発タブを表示できたら、開発＞挿入＞ボタンの順にクリックします。

そうするとカーソルが十字になりますので、適当なところでドラッグしながら矩形を描きます。描いた後にドラッグを放すと、以下の様なウィンドウが表示されますのでOKをクリックします。

ここで、マクロ名が私の表示と違うと思いますが、問題はありませんので気にしなくてもOKです。とりあえずここまで終わると、シート上にいま作成したボタンが表示されていると思いますので確認ください。

(b)Visual Basic Editor（VBE）でVBAを記述しましょう

今作成したボタンの上で右クリックをして「マクロの登録」をクリックします。続けて新規作成をクリックします。

すると、自動でVBEが立ち上がり以下のウィンドウが表示されます。
（ボタン２がボタン３に変わっていますが私の都合です。気にしないで下さい）

VBEはVBAを記述するために使うツールです。VBEを起動するためには、ワークシート上で以下のショートカットキーを打ってください。
[ Alt + F11 ]
これでいつでもVBEを起動することができます。

次に、今開いたVBEの右側の「Sub ボタン3_Click()とEnd Sub」の間に、以下のVBAの記述（コードと呼んだりします）をコピペします。

Dim adoCon As Object ' ADOコネクション
  Dim adoRs As Object  ' ADOレコードセット
  Dim SQL As String    ' SQL
  Dim i As Long
  
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")
 
 On Error GoTo ErrorTrap
 
  ' ODBC接続
  adoCon.Open _
      "DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
      " SERVER=localhost;" & _
      " DATABASE=ご自身のデータベース名;" & _
      " UID=ご自身のデータベースのユーザ名;" & _
      " PWD=ご自身のデータベースのパスワード;"
 
  MsgBox "DB接続成功"' SQL文
  SQL = "SELECT goodsID,goodsName,goodsPrice,addDate FROM goods"
 
  ' SQLの実行
  Set adoRs = adoCon.Execute(SQL)
  
  ' レコードセット内の全ての行の読込が
  ' 終了するまで処理を繰り返す
  i = 2
  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
 
  ' 解放処理
  adoRs.Close
  adoCon.Close
  Set adoRs = Nothing
  Set adoCon = Nothing
Exit Sub
 
ErrorTrap:
    Set adoRs = Nothing
    Set adoCon = Nothing
    MsgBox (Err.Description)