ExcelとSQLデータベースの連携
目次
VBAでデータベースサーバーへ問い合わせる
データベースサーバーが、ネットワーク内で稼働をはじめると、様々なアプリからそれを呼び出して、データを取りだし、業務に役立てる事ができます。
まずは、ExcelVBAからみてみましょう。
EXCEL2007以降の場合は、.xlsmと付くマクロ有効のファイル形式で保存すれば、VBAでデータベースに接続し、データを引き出す事ができます。
例として、Excelのブックを新規作成し、mariadb_test.xlsm と名前を付けて保存します。
![](https://yoshisyou.com/wordpress/wp-content/uploads/2019/02/20190206104202.png)
開発のタブをクリックし、Visual Basic をクリックします。
![](https://yoshisyou.com/wordpress/wp-content/uploads/2019/02/20190206104622.png)
これがExcel にプログラムを書き込むための、 Visual Basic の画面です。
データベースを扱うためには、ここに二つの処理をする必要があります。
![](https://yoshisyou.com/wordpress/wp-content/uploads/2019/02/20190206104952.png)
ツールから、Microsoft ActiveX Data Objects にチェックを入れ、有効化します。
必ず2.8とは限りませんし、使っていない場合は、リストのずっと下の方の事もあります。
![](https://yoshisyou.com/wordpress/wp-content/uploads/2019/02/20190206105441.png)
VBAProjectを右ボタンでクリックし、挿入から標準モジュールを選びます。
![](https://yoshisyou.com/wordpress/wp-content/uploads/2019/02/20190206112330.png)
標準モジュールが開かれます。新規作成したときは、当然、真っ白ですが、こうしてVisual Basicのコードを書いていくと、希望の処理を実行させられます。
Sub test01()
Dim wkb01 As Workbook
Dim wsh01 As Worksheet
Dim Rag01 As Range
Dim r As Integer
Dim c As Integer
Set wkb01 = ThisWorkbook
Set wsh01 = wkb01.Worksheets(1)
Dim myCmd As New ADODB.Command
Dim myConn As New ADODB.Connection
Dim myRS As New ADODB.Recordset
Dim connectionString As String
Dim sql1 As String
connectionString = "Driver={MySQL ODBC 3.51 DRIVER};" _
& " SERVER=192.168.1.14;" _
& " STMT=SET NAMES cp932;" _
& " PORT=3306;" _
& " DATABASE=vegetables;" _
& " USER=user01;" _
& " PASSWORD=upassword;" _
& " OPTION=2;"
myConn.Open connectionString
With myCmd
.ActiveConnection = myConn
r = 2
c = 2
sql1 = "SELECT `serial`,`仕入先` FROM `wholesale`;"
myRS.Open Source:=sql1, ActiveConnection:=myConn
Do Until myRS.EOF = True
Set Rag01 = wsh01.Cells(r, c)
Rag01.Value = myRS![serial]
c = 3
Set Rag01 = wsh01.Cells(r, c)
Rag01.Value = myRS![仕入先]
c = 2
r = r + 1
myRS.MoveNext
Loop
End With
myConn.Close: Set myConn = Nothing
End Sub
非常に簡単なサンプルですが、このモジュルを実行すると、192.168.1.14のIPを持つデータベースサーバーに問い合わせにいき、vegetablesというテーブルを開きます。
SQL文でデータの問い合わせをしているので、その結果がmyRSオブジェクトに格納されます。
これを1行ずつ、時分のシートの指定のセルに書き込んでいきます。
VBAでのセル指定は、cells(列、行)の番号で指定するので、面倒ですが、r,cのように数値変数にしています。
固定の数値でやってしまうと、何等かの理由で改良を求められた時、表示位置を探すのが面倒なためです。
![](https://yoshisyou.com/wordpress/wp-content/uploads/2019/02/20190206113015.png)
もちろん、SELECT文をもっと複雑にし、行と列の移動を巨大にすれば、売上シートや、在庫シートなどを比較的簡単に制作できます。
さて、このコードですがこのままでは実行しづらいです。
方法の一つとして、ボタンを設置して呼び出す方法があります。
![](https://yoshisyou.com/wordpress/wp-content/uploads/2019/02/20190207150445.png)
この開発の「挿入」を使用すると、シート上にボタンなどを配置できます。
![](https://yoshisyou.com/wordpress/wp-content/uploads/2019/02/20190207151828.png)
ボタンが出来たら、右クリックでプロパティを表示します。ボタン名などを判りやすいものに修正しましょう。
今度は通常にボタンをクリックすると、コードが記入できますので、次のコードを書き込みます。
Private Sub CommandButton1_Click()
Call test01
End Sub
モジュールのtest01を呼び出す(コール)という意味です。
これで、ボタンを押すとモジュールのtest01が呼び出されます。
![](https://yoshisyou.com/wordpress/wp-content/uploads/2019/02/20190207152956.png)
後は、要望にあわせてシート構成を修正し、実際に操作する人が判りやすいように、ボタンやシートなどを配置すれば、データベースを使用するExcelシートになります。
VBAからデータベースに書き込み
データベースへ問い合わせが出来るという事は、新規登録や修正も可能です。
SELECT文以外だと、データは返って来ないので、オブジェクトを用意する必要はありません。
Sub test02()
Dim wkb01 As Workbook
Dim wsh01 As Worksheet
Dim Rag01 As Range
Dim r As Integer
Dim c As Integer
Dim i As Long
Set wkb01 = ThisWorkbook
Set wsh01 = wkb01.Worksheets(2)
Dim myCmd As New ADODB.Command
Dim myConn As New ADODB.Connection
Dim myRS As New ADODB.Recordset
Dim connectionString As String
Dim sql1 As String
connectionString = "Driver={MySQL ODBC 3.51 DRIVER};" _
& " SERVER=192.168.1.14;" _
& " STMT=SET NAMES cp932;" _
& " PORT=3306;" _
& " DATABASE=vegetables;" _
& " USER=user01;" _
& " PASSWORD=upassword;" _
& " OPTION=2;"
myConn.Open connectionString
With myCmd
.ActiveConnection = myConn
r = 2
c = 2
sql1 = "INSERT INTO `wholesale` (`仕入先`) VALUES "
Do
Set Rag01 = wsh01.Cells(r, c)
If Rag01.Value = "" Then
Exit Do
Else
sql1 = sql1 & "('"
sql1 = sql1 & Rag01.Value
sql1 = sql1 & "'),"
End If
r = r + 1
Loop
i = Len(sql1)
sql1 = Left(sql1, i - 1)
sql1 = sql1 & ";"
.CommandText = sql1
.Execute
End With
myConn.Close: Set myConn = Nothing
End Sub
シート上のデータをデータベースに投入する場合は、ループを仕掛けてシート上のセルを一つづつ参照し、データを取り込んでいきます。
これを、INSERT文に整形し、ADODB.CommandにExecuteすれば、データがデータベースに投入されます。
![](https://yoshisyou.com/wordpress/wp-content/uploads/2019/02/20190207162157.png)
先ほどと同様、ボタンにtest02をコールするコードを入れておけば、このシートのB列に記載した名前をそのままデータベースに投入できます。
ただしセルが空の場合はそこで終了を意味するので、行は開けてはいけません。
もう一度、データを呼び出すコマンドボタンを実行すると、こうなります。
![](https://yoshisyou.com/wordpress/wp-content/uploads/2019/02/20190207162420-2.png)
これで、データベースそのものの事は知らない、例えば事務の方でも、指定のExcelファイルの指定の位置にデータを入力すれば、データベースへの投入操作ができるようになりました。
このように、データベースを扱うスキルは、必ず御社のお役にたてると思います。