Excelが大好きだ！

Excel大好き経理マンがExcelの事を書き綴っていきます。


スポンサードリンク

Excelで使用するSQL文をA5M2で作成・管理する方法

SQL VBA

ExcelでADODBを使用するときはVBEでSQL文を書きます。

が、正直めんどくさいんですよね、VBEでSQL文書くの。


そこでA5M2の登場です。

A5M2ではSQL文を入力補完機能で入力補助を受けながら書くことが出来ます。


今回はSQL文をA5M2で作成・管理しつつ、ExcelからA5M2で作成したSQL文を使用する方法紹介します。



VBEでSQL文を作成する際の不満

  • 入力補完が効かない
  • 改行がめんどくさい


1つ目はテーブル名やカラム名ばかりかSQL文のキーワードも補完が効かないので、全てを確認しつつ手入力を行う必要があります。

2つ目は、SQL文が長くなってきた時に改行を行うことがありますが、下記のように文章をつなぐのがめんどくさい。

A5M2とは

A5:SQL Mk-2

公式HPには下記のように紹介されています。

  A5:SQL Mk-2は複雑化するデータベース開発を支援するために開発されたフリーのSQLクライアントです。
 高機能かつ軽量で、使い方が分かりやすいことを目標に開発されています。
 SQLを実行したり、テーブルを編集するほかに、SQLの実行計画を取得したり、ER図を作成したりすることが出来ます。


このA5M2を使用してSQL文を管理・作成します。

A5M2をExcelに接続

まずはデータベースとなるExcelファイルを作成します。

今回データベースはいつもお世話になっております下記サイトで作成。

なんちゃって個人情報

次にA5M2からExcelに接続します。


接続方法は過去記事を参照ください。

www.excellovers.com


A5M2でSQL文を作成

このようにSQL文を作成する際に入力補完が効きますので、省力化かつ正確に入力することが出来ます。


入力補完は「Ctrl + Space」で表示できます。

入力補完初回起動時はデータベースへの接続を求められます。

上部のプルダウンから接続したいデータベースを選択してから、特に何も入力せずに「接続」をクリックでOK


Excelに接続する時に気をつけないといけない点は、テーブル名を「[ ]」で括る必要があります。

これは入力補完でもサポートされていませんので、自分で忘れずに入力する必要があります。


SQL文は自分が見やすいように適当な位置で改行しても大丈夫です。


SQL文が完成したら、任意の場所へ保存してください。

拡張子が「sql」というSQLファイルが作成されます。


ExcelでSQLファイルを参照

Excelから先ほど作成したSQLファイルを参照して、SQL文を実行します。


コードを記載する前にツール → 参照設定から下記2つにチェックを入れます。

画像ではリストの下の2つになります。

コード

標準モジュールを挿入して下記コードを記載してください。

Sub SQLファイル実行()
'--TextStreamオブジェクトSQLファイルを読み込み
    Dim myFSO As FileSystemObject
    Set myFSO = New FileSystemObject
    Dim TS As TextStream
    Set TS = myFSO.OpenTextFile(SQLファイルフルパス)　’←適宜書換
    
    Dim mySQL As String
    mySQL = TS.ReadAll
    TS.Close: Set TS = Nothing
    Set myFSO = Nothing

'--SQLファイルから読み込んだSQL文を基にSQL実行
    Dim myCON As ADODB.Connection
    Set myCON = New ADODB.Connection
    myCON.Provider = "Microsoft.ACE.OLEDB.12.0"
    myCON.Properties("Extended Properties") = _
        "Excel 12.0;IMEX=1;MAXSCANROWS=16"
    myCON.Open ThisWorkbook.FullName
    
    Dim myRS As ADODB.Recordset
    Set myRS = New ADODB.Recordset
    myRS.Open mySQL, myCON, adOpenStatic
    
    Worksheets("貼付").Range("A1").CopyFromRecordset myRS
    myRS.Close: Set myRS = Nothing
    myCON.Close: Set myCON = Nothing
End Sub


TextStreamオブジェクト

テキストファイルのデータを取り扱う方法は色々とありますが、今回はTextStreamオブジェクトを利用してテキストデータの中身を取得しました。


今回はReadAllメソッド使用しています。

メソッド名 機能
ReadAll TextStream ファイル全体を読み取り、結果を返します。

TextStream オブジェクト | Microsoft Docs


実行

実際に使用してみます。

SQLファイルを読み込んだところで中身を確認してみると、改行したままの状態で読み込まれていました。


SQLファイルの中身が改行されていても、正常にSQL文を実行することが出来ました。


まとめ

今回のネタは下記ツイートのツリーに触発されて作ってみました。