【ExcelVBA】Excelのマクロを使ってMySQLのデータベースにあるテーブル名を全て取得するには

この記事では、Excelのマクロを使ってMySQLのデータベースにあるテーブル名を全て取得する方法についてご説明します。

Table of Contents

【動画】Excelのマクロを使ってMySQLのデータベースにあるテーブル名を全て取得する実際の動き

本題に入る前に、まずは次の動画をご覧ください。

Connectionインスタンスの生成してMySQLのデータベースへの接続情報を用意したらConnectionインスタンスのConnectionStringプロパティに代入し、Openメソッドを実行することで、マクロがMySQLのデータベースに接続しています。

マクロがMySQLのデータベースに接続したら「INFORMATION_SCHEMA」の「TABLES」のテーブルに対して、検索する先のデータベース名(「B2」のセルに入力されたデータベース名)にあるテーブルを全て取得します。

マクロ作成の流れ

STEP.1

Connectionインスタンスを生成する

Connectionインスタンスを生成します。

STEP.2

MySQLへの接続情報を取得する

MySQLへの接続情報を取得します。

STEP.3

STEP.2の接続情報をOpenメソッドの引数に指定してOpenメソッドを実行する

STEP.2の接続情報をOpenメソッドの引数に指定してOpenメソッドを実行します。
実行すると、ExcelのマクロがMySQLのデータベースに接続されます。

STEP.4

「INFORMATION_SCHEMA」の「TABLES」のテーブルに対して、検索する先のデータベース名にあるテーブルを全て取得する

「INFORMATION_SCHEMA」の「TABLES」のテーブルに対して、検索する先のデータベース名にあるテーブルを全て取得します。

目次へ戻る

ExcelファイルとMySQLのデータベースの例

今回は次のExcelファイルを用意しました。

B2のセルに、検索先のデータベース名を入力して検索ボタンを押すと、データベースにある全てのテーブル名がD列のピンク色のセルに出力されます。

実際にデータベース「sakila」にあるテーブルを確認すると、D列のピンク色のセルに出力されたテーブルがデータベース「sakila」にあることが確認できます。

もしデータベースにテーブルが存在しない場合は下のように「テーブルは存在しません」とメッセージが表示されます。

目次へ戻る

コードの例

Option Explicit

Private Sub btn_exec_Click()

    Dim ws          As Worksheet            'ワークシート変数
    Dim cnt         As Long                 'カウンタ
    Dim cn          As ADODB.Connection     'Connection用変数
    Dim rs          As ADODB.Recordset      'レコードセット用変数
    Dim sqlStr      As String               'SQL用変数
        
    'シートを取得する
    Set ws = Worksheets("work")
    
    'カウンタを初期化する
    cnt = 2
    
    'セルをクリアする
    ws.Range("D2:D22").ClearContents
    
    'Connectionインスタンスの生成
    Set cn = New ADODB.Connection
    
    'MySQLへの接続情報の取得
    cn.ConnectionString = "Driver={MySQL ODBC 8.1 ANSI Driver};" & _
                          "Server=localhost;" & _
                          "Port=3306;" & _
                          "User=root;" & _
                          "Password=testPass@123456;"
                          
    'コネクションを開く
    cn.Open
    
    'Recordsetオブジェクトのインスタンスを生成する
    Set rs = New ADODB.Recordset
    
    'SQLクエリを実行して、テーブル名を取得する
    sqlStr = "SELECT TABLE_NAME"
    sqlStr = sqlStr & " FROM information_schema.tables"
    sqlStr = sqlStr & " WHERE table_schema = '" & Worksheets("work").Range("fDB").Value & "'"
    sqlStr = sqlStr & " AND"
    sqlStr = sqlStr & " table_type = 'BASE TABLE'"
    
    'SELECT文を実行してRecordsetを開く
    rs.Open sqlStr, cn, adOpenStatic
    
    If rs.EOF Then
    
        'テーブルが存在しない場合
        
        MsgBox "テーブルは存在しません"
        
    Else
        
        'テーブルが存在する場合
    
        Do Until rs.EOF
        
            'テーブル名をセルに出力する
            Worksheets("work").Range("D" & cnt).Value = rs.Fields(0).Value
            
            'Recordsetのレコードのカーソルを次に移動する
            rs.MoveNext
            
            cnt = cnt + 1
            
            DoEvents
                
        Loop
    
    End If
    
    '後処理
    cn.Close
    
    Set cn = Nothing
    Set rs = Nothing
    
End Sub

注目すべきコード①

最初に見て頂きたいのは21行目です。

    'Connectionインスタンスの生成
    Set cn = New ADODB.Connection

コードの説明

以上のコードは、マクロがMySQLのデータベースに接続するための接続情報を取得して接続するコードです。

このインスタンスがないとマクロがMySQLのデータベースに接続することができないので必ず生成しておきます。

注目すべきコード②

次に見て頂きたいのは24行目から28行目です。

    'MySQLへの接続情報の取得
    cn.ConnectionString = "Driver={MySQL ODBC 8.1 ANSI Driver};" & _
                          "Server=localhost;" & _
                          "Port=3306;" & _
                          "User=root;" & _
                          "Password=testPass@123456"

コードの説明

以上のコードは、マクロがMySQLのデータベースに接続するための接続情報を用意するコードです。

MySQLのデータベースに接続するための接続情報はいくつかの引数が必要になります。

Driver

Driverには、マクロがMySQLのデータベースに接続するのに必要なODBCドライバーの情報を指定します。

Server

Serverには、接続したいMySQLのサーバの名称を指定します。

今回はlocalhostに接続します。

【参考：MySQL Workbenchの画面】

Port

Portには、ポートの値を指定します。

【参考：MySQL Workbenchの画面】

User

Userには、MySQLのデータベースに接続するためのユーザ名を指定します。

【参考：MySQL Workbenchの画面】

Password

Passwordには、MySQLのデータベースに接続するためのパスワードを指定します。

注目すべきコード③

次に見て頂きたいのは31行目です。

    'コネクションを開く
    cn.Open

コードの説明

以上のコードは、ConnectionインスタンスのOpenメソッドを実行して、マクロがMySQLのデータベースに接続する処理のコードです。

Connectionインスタンスには「注目すべきコード②」で説明した通り、マクロがMySQLのデータベースに接続するための接続情報をConnectionStringプロパティに代入しているので、ここではOpenメソッドを実行することでマクロがMySQLのデータベースに接続されます。

注目すべきコード④

次に見て頂きたいのは34行目から44行目です。

    'Recordsetオブジェクトのインスタンスを生成する
    Set rs = New ADODB.Recordset
    
    'SQLクエリを実行して、テーブル名を取得する
    sqlStr = "SELECT TABLE_NAME"
    sqlStr = sqlStr & " FROM information_schema.tables"
    sqlStr = sqlStr & " WHERE table_schema = '" & Worksheets("work").Range("fDB").Value & "'"
    sqlStr = sqlStr & " AND"
    sqlStr = sqlStr & " table_type = 'BASE TABLE'"
    
    'SELECT文を実行してRecordsetを開く
    rs.Open sqlStr, cn, adOpenStatic

コードの説明

以上のコードは、Recordsetオブジェクトのインスタンスを生成した後にデータベース名を取得するSELECT文を用意してそのSELECT文を実行する処理のコードです。

コードの詳細

34行目のコードでは、Recordsetオブジェクトのインスタンスを生成しています。

Recordsetオブジェクトのインスタンスを生成することで、マクロがSQLのSELECT文を実行したり、SELECT文を実行した後にデータを取得することができるようになります。

37行目から41行目では、MySQLのデータベースにあるテーブル名を全て取得するSELECT文を用意しています。

MySQLのデータベースにあるテーブル名を全て取得する取得先のテーブルは「INFORMATION_SCHEMA」の「TABLES」のテーブルです。(38行目で指定)

検索するテーブル名は、「TABLES」のテーブルにあるフィールド「TABLE_SCHEMA」にあるので、WHERE句にしているフィールド名にtable_schemaを、条件値に「Worksheets(“work”).Range(“fDB”).Value」(B2のセルの値)を指定しています。(39行目で指定)

また、テーブル名があるフィールド「TABLE_NAME」にはテーブル名とVIEW名が混在しており、テーブル名だけを取得するには条件を追加する必要があります。

その条件とは、フィールド「TABLE_TYPE」の値が「BASE TABLE」であることです。

「INFORMATION_SCHEMA」の「TABLES」のテーブルの中を見てみると、以下の通りにフィールド「TABLE_TYPE」には「BASE TABLE」と「VIEW」が混在しています。

「BASE TABLE」はテーブル、「VIEW」はVIEWを指します。

「VIEW」の値のデータは取得したくないので、テーブル名だけを取得するために「TABLE_TYPE」の値が「BASE TABLE」をWHERE句に指定します。(41行目で指定)

ちなみに、37行目から41行目のコードだけではSELECT文が分かりにくいと思うので、実際に実行するSELECT文を以下にお見せします。

SELECT文(データベースにあるテーブルをすべて取得する)

SELECT
    TABLE_NAME
FROM
    information_schema.tables
WHERE
    table_schema = 'sakila'
AND table_type = 'BASE TABLE'

44行目のコードでは、SELECT文をOpenメソッドの引数に指定して実行することで、SELECT文が実行されます。

注目すべきコード⑤

次に見て頂きたいのは44行目から52行目です。

    If rs.EOF Then
    
        'テーブルが存在しない場合
        
        MsgBox "テーブルは存在しません"
        
    Else
        
        'テーブルが存在する場合

以上のコードでは、recordsetに格納されているデータが存在しているかを判定している処理のコードです。

もしデータが無い場合は50行目でテーブルが存在していない旨のメッセージを出力します。

データがある場合は52行目のELSEに該当し、ELSE内で「注目すべきコード⑥」の処理を行います。

注目すべきコード⑥

次に見て頂きたいのは56行目から68行目です。

        Do Until rs.EOF
        
            'テーブル名をセルに出力する
            Worksheets("work").Range("D" & cnt).Value = rs.Fields(0).Value
            
            'Recordsetのレコードのカーソルを次に移動する
            rs.MoveNext
            
            cnt = cnt + 1
            
            DoEvents
                
        Loop

コードの説明

以上のコードは、「INFORMATION_SCHEMA」の「TABLES」のテーブルから取得したテーブル名をExcelのセルに出力する処理のコードです。

コードの詳細

56行目のコードでは、recordsetに格納されたデータ(今回はテーブル名)の数分ループ処理を行うDo文で、例えばデータ件数が3件ある場合はループを3回繰り返します。

59行目のコードでは、recordsetに格納されたテーブルの名称をD列のセルに出力します。

62行目のコードでは、recordsetに格納された次のデータを参照するためMoveNextメソッドを実行しています。

例えば1つ目のデータを参照した後に2つ目のデータを参照するにはこのMoveNextを実行しないといけません。

MoveNextを実行しないと永久に1つ目のデータを参照し続けます。

なので、2つ目、3つ目・・・と順々にデータを参照するためには忘れずにMoveNextメソッドを実行します。

目次へ戻る

動作確認

「ExcelファイルとMySQLのデータベースの例」をご覧ください。

目次へ戻る

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

なぜ必要かというと、Excelのマクロのコードの7行目の「ADODB.Connection」と8行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。

    Dim cn          As ADODB.Connection     'Connection用変数
    Dim rs          As ADODB.Recordset      'レコードセット用変数

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

目次へ戻る

最後に

この記事では、Excelのマクロを使ってMySQLのデータベースにあるテーブル名を全て取得する方法についてご説明しました。

Excelのマクロを使ってMySQLのデータベースにあるテーブル名を全て取得したいときは本記事を参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間無料体験」はこちら

目次へ戻る