ExcelとSQLデータベースの連携

VBAでデータベースサーバーへ問い合わせる

データベースサーバーが、ネットワーク内で稼働をはじめると、様々なアプリからそれを呼び出して、データを取りだし、業務に役立てる事ができます。

まずは、ExcelVBAからみてみましょう。

EXCEL2007以降の場合は、.xlsmと付くマクロ有効のファイル形式で保存すれば、VBAでデータベースに接続し、データを引き出す事ができます。

例として、Excelのブックを新規作成し、mariadb_test.xlsm と名前を付けて保存します。

開発のタブをクリックし、Visual Basic をクリックします。

これがExcel にプログラムを書き込むための、 Visual Basic の画面です。

データベースを扱うためには、ここに二つの処理をする必要があります。

ツールから、Microsoft ActiveX Data Objects にチェックを入れ、有効化します。
必ず2.8とは限りませんし、使っていない場合は、リストのずっと下の方の事もあります。

VBAProjectを右ボタンでクリックし、挿入から標準モジュールを選びます。

標準モジュールが開かれます。新規作成したときは、当然、真っ白ですが、こうして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(列、行)の番号で指定するので、面倒ですが、ｒ，ｃのように数値変数にしています。
固定の数値でやってしまうと、何等かの理由で改良を求められた時、表示位置を探すのが面倒なためです。

もちろん、SELECT文をもっと複雑にし、行と列の移動を巨大にすれば、売上シートや、在庫シートなどを比較的簡単に制作できます。

さて、このコードですがこのままでは実行しづらいです。

方法の一つとして、ボタンを設置して呼び出す方法があります。

この開発の「挿入」を使用すると、シート上にボタンなどを配置できます。

ボタンが出来たら、右クリックでプロパティを表示します。ボタン名などを判りやすいものに修正しましょう。

今度は通常にボタンをクリックすると、コードが記入できますので、次のコードを書き込みます。

Private Sub CommandButton1_Click()
  Call test01
End Sub

モジュールのtest01を呼び出す（コール）という意味です。

これで、ボタンを押すとモジュールのtest01が呼び出されます。

後は、要望にあわせてシート構成を修正し、実際に操作する人が判りやすいように、ボタンやシートなどを配置すれば、データベースを使用する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

シート上のデータをデータベースに投入する場合は、ループを仕掛けてシート上のセルを一つづつ参照し、データを取り込んでいきます。
これを、ＩＮＳＥＲＴ文に整形し、ADODB.CommandにExecuteすれば、データがデータベースに投入されます。