☆Yuus Memo☆
非エンジニアの方でも業務を効率化できるプログラムを紹介します!
VBA

【VBA】データベースを扱うチュートリアル 準備編②

Excel VBA

皆さんこんにちは!!

前回から「EXCELのVBAでデータベースを扱うチュートリアル」を始めています。

前回の記事をまだ読まれていない方は、こちらからお願いします。
※データベースのテーブルを定義するところまで、進めています。

Excel VBA
【VBA】データベースを扱うチュートリアル 準備編① 皆さんこんにちは! 今回から何回かの記事に分けて、EXCELのVBAでデータベースを扱うチュートリアルを書いていきたいと思います...

今回の内容は、VBAからMariaDBへ接続するための「ドライバー」をインストールし、実際にExcelから接続するところまでを書いていきます。

今回で準備編は終わりで、次回からExcelVBAとデータベースを連携したアプリケーションを開発していきます。

MariaDB以外のデータベースでも、同じことが出来るので、チュートリアルでやる簡単なアプリケーションを応用することで、本格的な業務管理システムが開発出来る様になりますので、皆さん頑張ってください!!

ODBCドライバーのインストール

AccessやSQLServerでこのチュートリアルに取り組まれている方は、飛ばしていただいて構いません。

Visual C++ 再頒布可能パッケージのインストール

Connector/ODBCは、Visual Studio 2015を使用してビルドされるC++アプリケーションを実行するためのランタイムコンポーネントを使用しています。

インストーラーが起動時にチェックしているので、先にインストールしておきましょう。
Microsoftダウンロードセンターから入手できます(無料)。

インストーラーには、x64(64ビット版)とx86(32ビット版)があります。

オペレーティングシステムのアーキテクチャではなく、ODBCを使用するアプリケーションのアーキテクチャと同じものをインストールします。

ExcelやAccessでODBCを使用する場合は、インストールされているMS-Officeのアーキテクチャと同じものをインストールします。例えば、Windows10が64ビット版でも、インストールされているMS-Officeが32ビット版の場合は、x86(32ビット版)の方をインストールします。

MS-Officeのアーキテクチャの調べ方は、

Excelの場合

左上の[ファイル] 、サイドバーの [アカウント]とクリックして、製品情報から[Excelのバージョン情報]をクリックします。

表示されたダイアログの一番上の行に、バージョンが出ています。

インストールされているMS-Officeが何ビット版か確認する方法MS-Officeには、アーキテクチャの違いから32ビット版と64ビット版があり、ExcelやAccessで使用するMySQL Connector/ODBCは、同じアーキテクチャのものをインストールする必要があります。

MySQL Connector/ODBC 8.0のインストール

Windowsには標準で、いくつかの ODBC ドライバがインストールされているようですが、MySQL 用のドライバはインストールされていないようです。入手するには、MySQLのサイトからダウンロードします。

インストーラーは、Windowsのバージョンではなく、Officeのバージョンに合わせてください。
※VC++を入れる時に確認しているはずです。

Office 32bit版の方なら、下記をインストール
Windows (x86, 32-bit), MSI Installer

Office 64bit版の方なら、下記をインストール
・Windows (x86, 64-bit), MSI Installer

リンク先のインストーラーを使用してインストールしてください。
もし、ここまでの操作でつまずかれた方は、お気軽にコメント下さい。

Excel VBAからデータベースへ接続

それでは、まず「XAMPP」を立ち上げて「MySQL」と「Apache」を起動してください。

ダミーデータ作成

起動が確認できたら、phpMyAdminへアクセスし、「employee」の各テーブルに適当なデータを入れてください。

データの作成方法は、

  1. 左メニューからテーブル名をクリック
  2. 上部メニューの挿入をクリック

データ登録フォームが開くので、それぞれのテーブルに適当なデータを2個から3個ほど作ってください。

データを挿入する際に「id」は、AI(自動採番)を採用しているので、入力フォームでは空欄にして下さい。

「users」テーブルの「sex」は、1か2を入力してください。

「users」テーブルの「department_id」は、整数を入力してください。

Excelから接続

準備が整ったので、VBAでデータベースへ接続したいと思います。

まず空白のBookを作成してください。

開発タブより、VBEを開き、標準モジュールを1つ追加して以下のコードを貼り付けてください。

Option Explicit

Sub test()
  Dim adoCon As Object ' ADOコネクション
  Dim adoRs As Object  ' ADOレコードセット
  Dim SQL As String    ' SQL
  
  
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")
 
  ' ODBC接続 ※ODBCドライバーのバージョンは、ご自身のインストールしたものを記載してください。
  ' データベースのIDとパスワードを初期値から変えている方は、「UID」「PWD」をそれぞれ設定したものに変更してください。
  adoCon.Open _
      "DRIVER={MySQL ODBC 8.0 Unicode Driver};" & _
      " SERVER=localhost;" & _
      " DATABASE=employee;" & _
      " UID=root;" & _
      " PWD=;"
 
  'MsgBox "データ一覧を表示します"
  
  Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).ClearContents
  
   ' SQL文
  SQL = "SELECT * FROM users"
 
  ' SQLの実行
  Set adoRs = adoCon.Execute(SQL)
  
  ' 一括で出力
  Range("A1").CopyFromRecordset adoRs

  ' 解放処理
  adoRs.Close
  adoCon.Close
  Set adoRs = Nothing
  Set adoCon = Nothing

End Sub

Accessなど、別のデータベースを使用している方は下記の記事を見て、接続文字列を組み立ててください。

【VBAでDB接続】 ADOはMicrosoftが提供するデータベースアクセスのためのソフトウェア部品です。OLE DBをActiveXコントロールの形で使...

いかがですか?
データベースに入れたダミーデータがExcel上に表示されたと思います。

コードの解説を簡単にします。

  Dim adoCon As Object ' ADOコネクション
  Dim adoRs As Object  ' ADOレコードセット
  Dim SQL As String    ' SQL
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")
 
  ' ODBC接続 ※ODBCドライバーのバージョンは、ご自身のインストールしたものを記載してください。
  ' データベースのIDとパスワードを初期値から変えている方は、「UID」「PWD」をそれぞれ設定したものに変更してください。
  adoCon.Open _
      "DRIVER={MySQL ODBC 8.0 Unicode Driver};" & _
      " SERVER=localhost;" & _
      " DATABASE=employee;" & _
      " UID=root;" & _
      " PWD=;"

まず、この部分でADOコネクションとレコードセットを保持する変数を宣言し、「CreateObject」メソッドを使用して、ADODB.Connectionを変数に代入しています。

「CreateObject」を使用する理由としては、参照設定で事前バインディングする場合、インストールされているADOのバージョンなどに書き方が左右されるので、今回は、意識しなくても良い様に実行時バインディングを選択しました。

事前バインディングでも当然動きますし、事前バインディングの場合、コード補完も効くので分かる方は事前バインディングでも構いません。

注意点として、以下の部分は
“DRIVER={MySQL ODBC 8.0 Unicode Driver};”
ご自身のインストールされているODBCドライバーのバージョンに書き換えてください。

そして、ADODB.Connectionの「ConnectionString」に接続情報を渡し、OpenメソッドでADODBを開きます。

Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).ClearContents

ここは、A1から始まる使用セル領域のデータをクリアしているだけです。

 ' SQL文
  SQL = "SELECT * FROM users"

ここで、SQLという変数にデータベースへの問い合わせ文字列を代入します。
データベースへの問い合わせは、「SQL」と呼ばれる言語で記述します。
SQL」の詳細は、別途記事にまとめます。
ここでは、usersテーブルの全データを取得するコードとだけ覚えていただければよいです。

' SQLの実行
  Set adoRs = adoCon.Execute(SQL)

上で作ったSQL文をADODB.ConnectionのExecuteメソッドに渡し、実行します。

実行結果をADODB.Recordsetオブジェクト変数に代入します。

' 一括で出力
  Range("A1").CopyFromRecordset adoRs

レコードセットの内容を一括でA1から始まるセル領域へ出力します。
レコードセットはループで書き出すことも出来ますが、それは次回以降に行います。

' 解放処理
  adoRs.Close
  adoCon.Close
  Set adoRs = Nothing
  Set adoCon = Nothing

最後に、使用したオブジェクトにNothingを代入し開放します。

この処理を入れないと、オブジェクトをつかんだままになってしまうので、忘れない様にして下さい。

簡単に説明をしただけなので、イメージがつかめないかもしれませんが、このチュートリアルが終わるころには、大分データベースの処理にも慣れることができると思いますので、頑張りましょう!!

まとめ

今回で、準備はおしまいです。
次回から、いよいよ本格的にアプリケーションを構築していきます!

VBAとデータベースの連携を理解することで、会社の基幹システムなどと連携したシステムを構築できる様になり、業務が飛躍的に効率化されます。
また、基幹システムで手が届かない細かい部分の管理をExcelで出来るようになると、業務の変化に対応できる様になります。

是非、VBAとデータベースの連携を身に着けてください!!

コメントを残す