Excelで楽々C/Sプログラミング RDBMS連携編

福岡寿和 TOSHIKAZU, Fukuoka　　


Microsoft Excel95及びMicrosoft Excel 97（以下、Excel。基本的にExcel5.0などの16bit版は除外）とRDBMSを接続するには、図１のような方法があります。この他にもRDBMS独自のミドルウェアが32bit-API(SQL-DMOなど)やActiveX Server(Oracle Objects for OLEなど)で提供されているときは、そのミドルウェアも接続方法の選択対象になります。

図１

では、一つ一つの方法について、Microsoft SQL Server 6.5（MS SQL Server）と接続するには、具体的にどのようにプログラムすればよいのかを見ていきたいと思います。Excelでプログラムするときは、Visual Basic for Applications (以下、VBA)を使ってプログラムします。VBAは、Visual Basicと兄弟のようなもので若干の差はあるものの基本的な部分はほぼ同じ文法になっています。ですから、VBAを使ったサンプルがないときは、Visual Basicのサンプルから使い方を類推することも可能です。ただし、そのオブジェクト構造は、Applicationオブジェクトを頂点としたExcelに特化した構造になっています（図２）。

図２

なお、本記事は、Microsoft Office97 Professional VersionのMicrosoft Excel 97をMicrosoft Windows 95 Ver4.00.950B / IE 4.0 4.71.1712.6で動作させて確認した内容に基いて記述しています。Microsoft SQL Server 6.5にはService Pack 3を当ててあります。細かなバージョンの違いで動作が異なることはないと思いますが、動作がおかしいときには、まずソフトウェアやＯＳのバージョンを疑って見てください。

Excelプログラミングの基本
〜オブジェクト関連のVBA〜

Visual BasicとVBAの大きな以外は、その取り扱い対象が異なる点でしょう。Visual Basicは、基本的には、Formオブジェクトの中にControlオブジェクトがあるので、現在処理中のFormオブジェクトを表す「Me.」を省略できるくらいですが、VBAでは、「Application . ActiveWorkbook . ActiveSheet」と３階層分の省略が可能です。そのため、普通は１ブック内で処理が収まっていることが大半なので、
Range("A1").Value = 1
や
Worksheets("Sheet1").Range("A1").Value = 1
などのように記述すると覚えていれば充分でしょう。また、省略できるオブジェクトは省略した方が動作速度の向上につながります。

オブジェクト変数
プログラムは、定数だけではなく変数があって初めてプログラムとして機能します。同様に先ほどのオブジェクト構造で現れるオブジェクトを変数に関連付けられるとプログラムの幅が広がります。VBAではオブジェクト型変数でそのことを実現しています。
オブジェクト変数の宣言は、通常の変数と同様に
Dim <変数名> As <オブジェクト型名>
と宣言します。例えば、ワークシートを関連付ける変数を宣言するときは、
Dim xlsshtUI As Sheet
のようになります。
宣言した変数に実際にオブジェクトを関連付けるときは、「Set」文を使います。
Set <オブジェクト変数> = <オブジェクト>
このようにして関連付けることにより
Worksheets("Sheet1").Range("A1").Value = 1
を
Set xlsshtUI = Worksheets("Sheet1")
：
XlsshtUI.Range("A1").Value = 1
のように記述できます。

With-End With
VBAでプログラムすると、一つのオブジェクトに対して連続してプロパテイを設定するときによく出会います。このようなときは、「With-End With」文を使うと便利です。
With <オブジェクト>
：
.<プロパティ>
.<メソッド>
.<下層のオブジェクト>
：
End With
のようにある「.」の左側をWithで指定すると対になるEnd Withまでは「.」以降のみを記述するだけで済みます。「With-End With」文は「For-Next」文と同様に入れ子が可能です。
With Worksheets("Sheet1").Cells(1, 1)
　.Formula = "=SQRT(50)"
　With .Font
　　.Name = "Arial"
　　.Bold = True
　　.Size = 8
　End With
End With
また、「With-End With」を使う方が動作速度の向上につながります。

For Each-Next
あるコレクションのすべてのオブジェクトに対して繰り返しの処理を行いたいときは、「For Each-Next」文を使います。構文は、
For Each <変数> In <コレクション>
　<処理>
Next
となります。このとき<コレクション>の代わりにセル範囲（例：Range("A1:D10")）を指定することもできます。

Excelプログラミングの基本
〜ＵＩシートとデータシートの分離〜

Excelを使ってプログラムを作るときに、最初に検討しなければならないのは、ワークシートをどのように使うかです。
Excelを使って表を作るときは、１枚のワークシートで、データを入力し、式を入れて、書式を設定します。しかし、ExcelでRDBMSと連携するときは、データはあくまでRDBMS上にあり、それをワークシートに転記して利用することになります。そのため､複雑な書式にすると書式が変更される度に転記の位置が変更になってしまいます。そこで、データを転記するワークシート（データシート）と書式を整えたワークシート（ＵＩシート）の２つを作り、ＵＩシートからデータシート上のデータを参照するのがよいでしょう（図３）。

図３

この形態ならば、データシートには書式設定も必要ないので、データ量が増加しても１ワークシートにしたときよりも、ファイルサイズが余り増加せず、転記スピードの点でも有利です。
それではまず、どのようにデータシートにMS SQL Serverのデータを転記したらよいのかを検討したいと思います。

データシートを作成する
〜Microsoft Query（MS Query）を使う

データシートにMS SQL Server上のデータを転記する一番簡単な方法は、MS Queryを使う方法です。MS Queryは、ODBCを経由してMS SQL Server（又はODBCデータソース）上のデータを指定した条件で選択、加工してExcelのシート上に表示するプログラムです。表示専用のツールなので、MS SQL Serverへのデータ投入には使えませんが、検索条件をいろいろ変化させて、必要な情報を画面上で確認してからExcelに転記できる利点があります。

MS Queryのインストール
MS SQL Serverと一番楽に接続する方法は、MS Queryを使う方法です。MS Queryは、標準セットアップではインストールされないので、Office97のSetupを起動してインストールの指定をしてください(図４)。

図４

MS Queryの使い方
  1. Excelで[データ]-[外部データの取り込み]-[新しいクエリーの作成]メニューを選択して、MS Queryが起動します。
  2. MS Queryが起動すると接続するODBCデータソースの選択画面が表示されるので、MS SQL ServerのODBCデータソース名を選択してください。事前にデータソースを作成していないときは、<新規データソース>を選択して、データソースを作成することもできます。
  3. クエリーウィザードが表示させるので、Excelに表示したい列（図５）、条件（図６）、ソート順（図７）を指定します。

    図５

    図６

    図７

  4. すぐに[Excelにデータを返す]か、[MS Queryでクエリーの編集を行う]か選択できるので、後者を選択してMS Query上でデータを確認します（図８）。

    図８

  5. データ内容が期待通りならば、MS Queryを終了して、Excelにデータを返します（図９）。

    図９

このようにしてデータシートを作成した後は、 [外部データ]ツールバーの[データの更新]ボタン（図10）をクリックするだけで最新の情報がデータシートに転記されます。[外部データ]ツールバーが表示されないときは、[表示]-[ツールバー]メニューから[外部データ]を選択するとツールバーが表示されます。

図10

最新データの自動取得
Excelのブックを開いたときに自動的に最新データを取得するためには、[外部データ]-[データ範囲プロパティ]から［外部データ範囲のプロパティ］ダイアログボックスを開き、そこで「ファイルを開くときにデータを更新する」をチェックすることで可能になります（図11）。また、「保存前にワークシートから外部データを削除する」も併せてチェックすることでファイルサイズを小さくすることができます（サンプルMSQRY.xlsでは、19,968バイト→15,872バイト）。

図11

MS Queryの欠点
ExcelとMS Queryは、DDE（Dynamic Data Exchange）通信により連携します。DDE通信は、大量データの転送には不向きなプロトコル（取り決め）なので、MS Queryで実行してクエリーの結果レコードが多いときは、使い勝手が悪化します。楽な方法ですが、比較的小規模のデータを扱うときに限って使うのがよいでしょう。

データシートを作成する
〜DAO/Jet3.5を使う〜

MS Queryの手軽さは捨て難いものがありますが、手軽さと確実さをトレードオフする訳にはいきません。本格的にC/SのフロントエンドとしてExcelを使うときは、やはりデータシートへの設定もVBAを使って確実に行うのがよいでしょう。

DAO/Jetの組み込み
DAO/Jetを使うときは、Microsoft DAO 3.5 Object Library を組み込みます。Excel97では、[ツール]-[マクロ]-[Visual Basic Editor]メニューからVBAの編集画面であるVisual Basic Editor（VBE）を起動して、VBEの[ツール]-[参照設定]メニューで[参照設定]ダイアログボックスを開き、「Microsoft DAO 3.5 Object Library」をチェックします。

DAO/Jetの使い方
DAO/Jetを使うときの基本的な流れとしては、 という流れになります。
この流れに沿って、VBAでプログラムすると
  1. SQL Serverに割当てたODBCデータソースをDAO/Jetが処理するデータベースとして開くためには、OpenDatabaseメソッドを使います。
  2. SQL Serverの任意のテーブルから、表示したい列、条件、ソート順を指定するには、SQL文の知識が必要です。でも、「MS Queryを使うときには、SQL文を意識しなくてもよかったのに、やはりDAO/Jetを使うのは大変だ」などと思わないでください。MS Queryを使うとしてもきちんとしたシステムを構築するためには、テスト時にSQL文の知識は必要なのです。つまり、SQL文はRDBMSを使うためには避けて通れない知識と言えるでしょう。DAO/Jetでは、SQL文を指定して得た結果をレコードセットと呼びます。そして、レコードセットを取得するには、OpenRecordsetメソッドの第一パラメタにSQL文を指定して行います。
  3. レコードセットをすべて読み切るまで4.〜5.を繰り返します。
  4. レコードセットのフィールド数は、FiledsコレクションのCountプロパティを参照することで取得できますので、For-Next文を使って、Excelワークシートの指定行に設定していきます。
  5. ワークシートの指定行を移動し、MoveNextメソッドを使ってレコードセットの次のレコードを取得します。
  6. レコードセットを閉じます。
  7. ODBCデータソースを閉じて、RDBMSとの接続を切断します。
のようになります（リスト１）。

リスト１　DAO/Jet3.5サンプル
Private Sub Workbook_Open()
    Dim strRange    As String       'コピー先
    Dim ilngLoop    As Long         'ループカウンタ
    Dim ilngCol     As Long         'ループカウンタ
    Dim dbsSQL      As Database     'データベース
    Dim rdynSQL     As Recordset    'レコードセット
    Dim strSQL      As String       'SQL文
    Dim strConnect  As String       'Connect文字列
    
    strSQL = "SELECT * from authors"
    strConnect = "ODBC; UID=sa;PWD="

'1. ODBCデータソースを指定してデータベースを開く
    Set dbsSQL = Workspaces(0).OpenDatabase("SQLS", False, False, strConnect)
'2. SQL文を指定してレコードセットを開く
    Set rdynSQL = dbsSQL.OpenRecordset(strSQL, dbOpenDynaset)
    With Worksheets("データシート")
        ilngLoop = 2
'3. レコードセットをすべて読み切るまでC〜Dを繰り返す
        Do While Not rdynSQL.EOF
'4. カレントレコードの全フィールドをExcelワークシートのカレント行の列に設定
            For ilngCol = 0 To rdynSQL.Fields.Count - 1
                .Cells(ilngLoop, ilngCol + 1) = rdynSQL(ilngCol)
            Next
'5. ワークシートのカレント行を移動し、次のレコードを取得
            ilngLoop = ilngLoop + 1
            rdynSQL.MoveNext
        Loop
'6. レコードセットを閉じる
        rdynSQL.Close
'7. データベースを閉じる
        dbsSQL.Close
    End With
    
    With Worksheets("ＵＩシート")
        .Range("5:6").Copy
        For ilngLoop = 7 To 27 Step 2
            strRange = CStr(ilngLoop) & ":" & CStr(ilngLoop + 1)
            .Range(strRange).Select
            ActiveSheet.Paste
        Next
    End With
    
exitOpen:
    On Error Resume Next
    rdynSQL.Close
    dbsSQL.Close
    Exit Sub
    
errOpen:
    MsgBox "WorkBook_Open:" & Error&, vbOKOnly & vbExclamation, app.Title
    Resume exitOpen:
End Sub

DAO/Jetの欠点
DAO/Jetは、AccessのデータベースエンジンJetを使う手法です。そのため、あくまでもAccessのMDBファイルやテキストファイルなどのローカルファイルと連結するための手法なのです。もちろん、ローカルファイルとの接続にODBCを使っているので、その機能を間接的に使うことで無理矢理RDBMSと接続することも可能です。そのため、決してRDBMSの機能を充分発揮する手法ではありません。検索速度が問題にならないときやExcel95など他の手法が使えないときに使用を限定した方がよいでしょう。

データシートを作成する
〜ODBC Directを使う〜

では、RDBMSと接続するときにはどうしたらよいのでしょうか。その回答の一つがExcel97から追加されたODBC Directを使う方法です。
ODBC Directは、JetではなくRDO2.0を使ってODBCを使いますが、直接RDO2.0を使うときと異なり、DAO/Jetのメソッド、プロパティ、イベントと同じ文法で接続できます。

ODBC Directの組み込み
ODBC Directを組み込む方法は、DAO/Jetを組み込む方法と同じく、VBEの[ツール]-[参照設定]メニューで[参照設定]ダイアログボックスを開き、「Microsoft DAO 3.5 Object Library」をチェックします。

ODBC Directの使い方
ODBC Directの使い方は、基本的にDAO/Jetの使い方と同じです（リスト２）。

リスト２　ODBC Directサンプル（抜粋）
Private Sub Workbook_Open()
    Dim strRange    As String       'コピー先
    Dim ilngLoop    As Long         'ループカウンタ
    Dim ilngCol     As Long         'ループカウンタ
    Dim wrkODBC     As Workspace    'ワークスペース
    Dim dbsSQL      As Database     'データベース
    Dim rdynSQL     As Recordset    'レコードセット
    Dim strSQL      As String       'SQL文
    Dim strConnect  As String       'Connect文字列

    strSQL = "SELECT * from authors"
    strConnect = "ODBC; UID=sa;PWD="

'ODBC Directワークスペースを開く
    Set wrkODBC = CreateWorkspace("ODBC", "admin", "", dbUseODBC)
'1. ODBCデータソースを指定してデータベースを開く
    Set dbsSQL = wrkODBC.OpenDatabase("SQLS", False, False, strConnect)
'2. SQL文を指定してレコードセットを開く
    Set rdynSQL = dbsSQL.OpenRecordset(strSQL, dbOpenDynaset)
    With Worksheets("データシート")
        ilngLoop = 2
'3. レコードセットをすべて読み切るまでC〜Dを繰り返す
        Do While Not rdynSQL.EOF
'4. カレントレコードの全フィールドをExcelワークシートのカレント行の列に設定
            For ilngCol = 0 To rdynSQL.Fields.Count - 1
                .Cells(ilngLoop, ilngCol + 1) = rdynSQL(ilngCol)
            Next
'5. ワークシートのカレント行を移動し、次のレコードを取得
            ilngLoop = ilngLoop + 1
            rdynSQL.MoveNext
        Loop
    End With
'6. レコードセットを閉じる
    rdynSQL.Close
'7. データベースを閉じる
    dbsSQL.Close
'ODBC Directワークスペースを閉じる
    wrkODBC.Close
    Set wrkODBC = Nothing
    
    With Worksheets("ＵＩシート")
        .Range("5:6").Copy
        For ilngLoop = 7 To 27 Step 2
            strRange = CStr(ilngLoop) & ":" & CStr(ilngLoop + 1)
            .Range(strRange).Select
            ActiveSheet.Paste
        Next
    End With
    
exitOpen:
    On Error Resume Next
    rdynSQL.Close
    dbsSQL.Close
    Exit Sub
    
errOpen:
    MsgBox "WorkBook_Open:" & Error&, vbOKOnly & vbExclamation, app.Title
    Resume exitOpen:
End Sub

ODBC Directの注意点
ODBC Directを使うときの注意点は、DAO/JetでもRDOでもなく、あくまでもODBC Directを使っていることを忘れないことです。ですから、ヘルプなどで使い方を調べるときも、その機能がODBC Directでも有効かも併せて調べてください。

データシートを作成する
〜RDO2.0を使う〜
現在調査中ですが、ExcelでRDO2.0を使用するには、配布先にVBの開発環境が必要なようです。

Excel97のみインストールされた環境では、ODBC Directとして間接的にRDO2.0の機能を使うしかありませんが、Visual Basic 5.0の開発環境や実行環境がインストールされている環境では、直接RDO2.0の機能を使うことができます。

RDO2.0の組み込み
RDO2.0を使うときは、VBEの[ツール]-[参照設定]メニューで[参照設定]ダイアログボックスを開き、「Microsoft RDO 2.0 Object Library」をチェックして（図12）、Microsoft RDO 2.0 Object Library を組み込みます。

図12

RDO2.0の使い方
RDO2.0を使うときの基本的な流れとしては、 という流れになります。
この流れに沿って、VBAでプログラムすると
  1. SQL Serverに割当てたODBCデータソースをRDO2.0が処理するデータベースとして開くためには、OpenConnectionメソッドを使います。
  2. RDO2.0では、SQL文を指定して得た結果を結果セットと呼びます。そして、結果セットを取得するには、OpenResultsetメソッドの第一パラメタにSQL文を指定して行います。
  3. 結果セットをすべて読み切るまで4.〜5.を繰り返します。
  4. 結果セットのフィールド数は、rdoColumnsコレクションのCountプロパティを参照することで取得できますので、For-Next文を使って、Excelワークシートの指定行に設定していきます。
  5. ワークシートの指定行を移動し、MoveNextメソッドを使って結果セットの次のレコードを取得します。
  6. 結果セットを閉じます。
  7. ODBCデータソースを閉じて、RDBMSとの接続を切断します。
のようになります（リスト３）。

リスト３　RDO2.0サンプル
Private Sub Workbook_Open()
    Dim strRange    As String           'コピー先
    Dim ilngLoop    As Long             'ループカウンタ
    Dim ilngCol     As Long             'ループカウンタ
    Dim rdcnnRDO    As rdoConnection    'コネクション
    Dim rdrslRDO    As rdoResultset     '結果セット
    Dim strSQL      As String           'SQL文
    Dim strConnect  As String           'Connect文字列

    strSQL = "SELECT * from authors"
    strConnect = "DSN=SQLS;UID=sa;PWD=;"

'1. ODBCデータソースを指定してデータベースを開く
    Set rdcnnRDO = rdoEnvironments(0).OpenConnection("", rdDriverNoPrompt, True, strConnect)
'2. SQL文を指定して結果セットを開く
    Set rdrslRDO = rdcnnRDO.OpenResultset(strSQL, rdOpenKeyset)
    With Worksheets("データシート")
        ilngLoop = 2
'3. 結果セットをすべて読み切るまでC〜Dを繰り返す
        Do While Not rdrslRDO.EOF
'4. カレントレコードの全フィールドをExcelワークシートのカレント行の列に設定
            For ilngCol = 0 To rdrslRDO.rdoColumns.Count - 1
                .Cells(ilngLoop, ilngCol + 1) = rdrslRDO(ilngCol)
            Next
'5. ワークシートのカレント行を移動し、次のレコードを取得
            ilngLoop = ilngLoop + 1
            rdrslRDO.MoveNext
        Loop
    End With
'6. 結果セットを閉じる
    rdrslRDO.Close
'7. データベースを閉じる
    rdcnnRDO.Close
    
    With Worksheets("ＵＩシート")
        .Range("5:6").Copy
        For ilngLoop = 7 To 27 Step 2
            strRange = CStr(ilngLoop) & ":" & CStr(ilngLoop + 1)
            .Range(strRange).Select
            ActiveSheet.Paste
        Next
    End With
    
exitOpen:
    On Error Resume Next
    rdrslRDO.Close
    rdcnnRDO.Close
    Exit Sub
    
errOpen:
    MsgBox "WorkBook_Open:" & Error&, vbOKOnly & vbExclamation, app.Title
    Resume exitOpen:
End Sub

RDO2.0の注意点
Visual Basic 5.0の開発環境がインストールされていない環境にRDO2.0を使うExcelファイルを配布するときは、Visual Basic 5.0でRDOを使う簡単なプログラムを作成して、Setupファイルを作って同時に配布する必要があります。

検索速度を向上させるには

今までのサンプルコードを実行してみると、ＵＩシートの展開が遅いのが分かると思います。これは、１レコード分の定義を作成しておいて、それをCopy&Pasteして展開しているのが原因です。しかし、これはレコード数が一定していないデータを一度に表示する仕様を実現するためには仕方のないことです。しかし、業務システムのフロントエンドとして考えたとき、データ数の影響を受けすぎるのは、やはり問題です。そこで、一度に表示するという仕様を考え直して、RDBMSからデータシートには一度に転記をして、その後、任意の範囲をＵＩシートで参照する仕様に変更することでかなり画面表示のレスポンスが向上します。

ＵＩシートの改良（リスト４・図13）

リスト４　検索速度改善後のODBC Directサンプル
Option Explicit
Public plngRow      As Long
Private Sub cmdExit_Click()
    ActiveWorkbook.Close savechanges:=False
End Sub
Private Sub cmdNext_Click()
    Dim ilngLoop        As Long

    plngRow = plngRow + 10
    If plngRow >= Worksheets("データシート").Cells(1, 1) - 10 Then
        cmdNext.Enabled = False
    End If
    For ilngLoop = 0 To 9
        Cells(ilngLoop * 2 + 5, 1) = "=データシート!A" & CStr(plngRow + ilngLoop + 2)
        Cells(ilngLoop * 2 + 5, 2) = "=データシート!B" & CStr(plngRow + ilngLoop + 2)
        Cells(ilngLoop * 2 + 5, 3) = "=データシート!C" & CStr(plngRow + ilngLoop + 2)
        Cells(ilngLoop * 2 + 5, 4) = "=データシート!D" & CStr(plngRow + ilngLoop + 2)
        Cells(ilngLoop * 2 + 6, 2) = "=データシート!E" & CStr(plngRow + ilngLoop + 2) & " & "","" & " & _
                                      "データシート!F" & CStr(plngRow + ilngLoop + 2) & " & "","" & " & _
                                      "データシート!G" & CStr(plngRow + ilngLoop + 2) & " & "","" & " & _
                                      "データシート!H" & CStr(plngRow + ilngLoop + 2)
    Next
    cmdPrev.Enabled = True
End Sub
Private Sub cmdPrev_Click()
    Dim ilngLoop        As Long
    
    plngRow = plngRow - 10
    If plngRow = 0 Then
        cmdPrev.Enabled = False
    End If
    For ilngLoop = 0 To 9
        Cells(ilngLoop * 2 + 5, 1) = "=データシート!A" & CStr(plngRow + ilngLoop + 2)
        Cells(ilngLoop * 2 + 5, 2) = "=データシート!B" & CStr(plngRow + ilngLoop + 2)
        Cells(ilngLoop * 2 + 5, 3) = "=データシート!C" & CStr(plngRow + ilngLoop + 2)
        Cells(ilngLoop * 2 + 5, 4) = "=データシート!D" & CStr(plngRow + ilngLoop + 2)
        Cells(ilngLoop * 2 + 6, 2) = "=データシート!E" & CStr(plngRow + ilngLoop + 2) & " & "","" & " & _
                                      "データシート!F" & CStr(plngRow + ilngLoop + 2) & " & "","" & " & _
                                      "データシート!G" & CStr(plngRow + ilngLoop + 2) & " & "","" & " & _
                                      "データシート!H" & CStr(plngRow + ilngLoop + 2)
    Next
    If plngRow <= Worksheets("データシート").Cells(1, 1) - 10 Then
        cmdNext.Enabled = True
    End If
End Sub

図13

  1. データシートからの転記部分を１０レコード固定にする。また、データシートの先頭１０レコードを指定しておくことにより、初期表示を早くします。
  2. 前へ＜]ボタンを追加して、ボタンがクリックされたときは、データシートの参照位置を１０レコード前に移動するために、ＵＩシートの参照を書き換えます。このとき先頭レコードより前に移動しないようにします。
  3. [＞後へ]ボタンを追加して、ボタンがクリックされたときは、データの参照位置を１０レコード後ろに移動するためにＵＩシートの参照を書き換えます。このとき最終レコードより前に移動しないようにします。

ユーザーフォームを作成する

ＵＩシートは、あくまでもデータの表示用のシートでした。各種の情報を入力するためには、データシートと同じ形式の入力シートを用意して、一覧形式で入力する方法と、「ユーザーフォーム」機能を使って、入力フォームを作り、そこでデータの入力を行う方法が考えられます。今回は、「ユーザーフォーム」を使う方法を採用します。
「ユーザーフォーム」とデータシート、ＵＩシートの関係は図14のようになります。

図14

ここでのポイントは、入力データはあくまでExcel上のデータシートに反映し、RDBMSには即座に反映しない点にあります。これは速度向上による操作性向上も目的の一つですが、RDBMS上のデータに影響を与えずに、合計値や分析結果の変化を確認する仮更新機能を目的としているからです。仮更新機能があれば、他のユーザーに気兼ねなくデータ値の変更を試行錯誤できて、Excelの分析ツールとしての局面を有効利用することができます。

ユーザーフォームの組み込み
ユーザーフォームを使うときは、VBEの[挿入]-[ユーザーフォーム]メニューを選んで、ユーザーフォームを開きます（図15）。同時に[ツールボックス]ウィンドウが開きます。この[ツールボックス]ウィンドウ上のコントロールを使ってフォームをデザインします。

図15

ユーザーフォームのデザインの仕方
  1. Nameプロパティの設定
    ユーザーフォームの名前をFormオブジェクトのNameプロパティに設定します。ただし、Nameプロパティは、Formオブジェクトのプロパティウィンドウで「（オブジェクト名）」と表示されているので注意してください。なお、このNameプロパティの値を使ってVBAでの識別に使われます。そのため、名前の先頭には「frm」をつけてFormオブジェクトであることが一目で分かるようにするとよいでしょう。
  2. Captionプロパティの設定
    ユーザーフォームのタイトルバーに表示する内容をFormオブジェクトのCaptionプロパティに設定します。ユーザーフォームの目的にあった内容を設定してください。
  3. コントロールの配置（図16）

    図16

    Windowsユーザーインターフェイスデザインガイドなどを参考にして、ユーザーフォームにコントロールを配置します。

  4. コントロールのプロパティの設定
    配置したコントロールのプロパティを設定します。このときもWindowsユーザーインターフェイスデザインガイドを参考にして、[OK]ボタンのDefaultプロパティをTrue、[キャンセル]ボタンのCancelプロパティをTrueに設定します。
  5. ユーザーフォームのデザインの注意点
    ユーザーフォームは、Visual Basicのフォームと似て異なるものです。例えば、コントロール配列がなかったり、コマンドボタンにアクセスキーを設定できなかったりと微妙な点ですが、作業効率や操作性に関わるところが一致していません。Visual Basicをすでに利用している人も一度きちんとユーザーフォームを調査した方がよいでしょう。「どうせ、VBと同じでしょ」と思って、実際に触らずに画面を設計してしまうと、ユーザーフォームで実現できないユーザーインターフェイスを設計してしまうことでしょう。

ユーザーフォームの使い方
  1. 作家情報を左クリックすると、ユーザーフォームに表示情報を設定します。
  2. ユーザーフォームのShowメソッドを使って、ユーザーフォームを表示します。
  3. ユーザーフォームの[OK]ボタンのClickイベントで、表示情報を「データシート」に転記して、フォームを閉じます。
  4. ユーザーフォームの[キャンセル]ボタンのClickイベントで、転記は行わずフォームを閉じます。
  5. ユーザーフォームを消去します。

リスト５　ユーザーフォームサンプル（抜粋）
Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)
    Dim lngRow      As Long
    
'1. ユーザーフォームに表示情報を設定します。
    lngRow = (Row - 5) \ 2 + 2
    With frmUser
        .txtID = Worksheets("データシート").Cells(lngRow, 1)
        .txtlname = Worksheets("データシート").Cells(lngRow, 2)
        .txtFname = Worksheets("データシート").Cells(lngRow, 3)
        .txtPhone = Worksheets("データシート").Cells(lngRow, 4)
        .txtAddr = Worksheets("データシート").Cells(lngRow, 5)
        .txtCity = Worksheets("データシート").Cells(lngRow, 6)
        .txtState = Worksheets("データシート").Cells(lngRow, 7)
        .txtZIP = Worksheets("データシート").Cells(lngRow, 8)
    End With
'2. ユーザーフォームのShowメソッドを使って、ユーザーフォームを表示します。
    frmUser.Show
'5. ユーザーフォームを消去します。
    Set frmUser = Nothing
End Sub

Private Sub cmdOK_Click()
    Dim lngRow      As Long
    
'3. 表示情報を「データシート」に転記して、フォームを閉じます。
    lngRow = CLng(Tag)
    With Worksheets("データシート")
        .Cells(lngRow, 1) = txtID
        .Cells(lngRow, 2) = txtLname
        .Cells(lngRow, 3) = txtFname
        .Cells(lngRow, 4) = txtPhone
        .Cells(lngRow, 5) = txtAddr
        .Cells(lngRow, 6) = txtCity
        .Cells(lngRow, 7) = txtState
        .Cells(lngRow, 8) = txtZIP
    End With
    Unload Me
End Sub

Private Sub cmdCancel_Click()
'4.  転記は行わずフォームを閉じます。
    Unload Me
End Sub

データとアルゴリズムを分離するには

なぜ、分離する必要があるのか
今回のサンプルでは、データシート上にRDBMSのレコードの内容を保存しないことで、データとアルゴリズムを分離しています。しかし、RDBMS上のデータを取り込んでExcelで修飾してメイルに添付して送信するようなときは、メイルの受信先からRDBMSに接続できない事態を考慮して、データシートに値を保存しなければなりません。このように値の入ったデータシートを配布するときは、VBA部分を分離することでExcelファイルのサイズを小さくすることができます。

分離方法〜VBAで実現
一番簡単な分離方法は、ＵＩシートとデータシートをVBAから分離して、テンプレート化する方法です。そして、VBAのみのブックでは、
  1. テンプレートを開きます。
  2. 保存先のファイル名を指定します。
  3. 開いたテンプレートのデータシートに値を設定します。
  4. 指定されたファイル名で値が設定されたブックを保存します。
とプログラムします（リスト６）。

リスト６　データとアルゴリズムの分離サンプル
Option Explicit
Private Sub Workbook_Open()
    Dim strRange    As String       'コピー先
    Dim ilngLoop    As Long         'ループカウンタ
    Dim ilngCol     As Long         'ループカウンタ
    Dim wrkODBC     As Workspace    'ワークスペース
    Dim dbsSQL      As Database     'データベース
    Dim rdynSQL     As Recordset    'レコードセット
    Dim strSQL      As String       'SQL文
    Dim strConnect  As String       'Connect文字列
    Dim strName     As String       '生成時ブック名
    Dim strNewName  As String       '新ブック名
    
'1.  テンプレートを開きます。
    Workbooks.Add (ActiveWorkbook.Path & "\SEP.xlt")
    strName = ActiveWorkbook.Name & ".xls"
    
'2.  保存先のファイル名を指定します。
    If Application.Dialogs(xlDialogSaveWorkbook).Show Then
        strNewName = ActiveWorkbook.Name
    End If
    
'3.  開いたテンプレートのデータシートに値を設定します。
    strSQL = "SELECT * from authors"
    strConnect = "ODBC; UID=sa;PWD="
    'ODBC Directワークスペースを開く
    Set wrkODBC = CreateWorkspace("ODBC", "admin", "", dbUseODBC)
    'ODBCデータソースを指定してデータベースを開く
    Set dbsSQL = wrkODBC.OpenDatabase("SQLS", False, False, strConnect)
    'SQL文を指定してレコードセットを開く
    Set rdynSQL = dbsSQL.OpenRecordset(strSQL, dbOpenDynaset)
    With ActiveWorkbook.Worksheets("データシート")
        ilngLoop = 2
    'レコードセットをすべて読み切るまでC〜Dを繰り返す
        Do While Not rdynSQL.EOF
    'カレントレコードの全フィールドをExcelワークシートのカレント行の列に設定
            For ilngCol = 0 To rdynSQL.Fields.Count - 1
                .Cells(ilngLoop, ilngCol + 1) = rdynSQL(ilngCol)
            Next
    'ワークシートのカレント行を移動し、次のレコードを取得
            ilngLoop = ilngLoop + 1
            rdynSQL.MoveNext
        Loop
    End With
    'レコードセットを閉じる
    rdynSQL.Close
    'データベースを閉じる
    dbsSQL.Close
    'ODBC Directワークスペースを閉じる
    wrkODBC.Close
    Set wrkODBC = Nothing
    
    If ilngLoop >= 12 Then
        ActiveWorkbook.Worksheets("ＵＩシート").cmdNext.Enabled = True
    End If
    
exitOpen:
    On Error Resume Next
    rdynSQL.Close
    dbsSQL.Close
    '総レコード数をデータシートに設定する
    Workbooks(strName).Worksheets("データシート").Cells(1, 1) = ilngLoop - 2
    If strNewName <> "" Then
'4.  指定されたファイル名で値が設定されたブックを保存します。
        Workbooks(strName).Activate
        ActiveWorkbook.SaveAs (strNewName)
    End If
    Exit Sub
    
errOpen:
    MsgBox "WorkBook_Open:" & Error$, vbOKOnly & vbExclamation
    Resume exitOpen:
End Sub

おまけ
〜Oracle Objects for OLE(oo4o)を使う〜

Oracle Objects for OLE(oo4o)は、Visual Basicだけではなく、Excelでも使うことができます。また、oo4o Ver2.2からはCopyToClipboardにより取得データをクリップボードに転送することが可能になりました。この新機能を使えば、比較的簡単にデータシートに値を転記できます。

oo4oの組み込み
oo4oは、特に参照設定しなくても使うことができます。しかし、参照設定（[参照設定]ダイアログボックスを開き、「OracleInProcServer 2.2 Type Library」をチェック）をして、オブジェクト型の変数ではなく、OraSession型などの変数を宣言する（早期バインドといいます）ことにより、oo4oのタイプライブラリへの頻繁なアクセスを抑止するのでパフォーマンスが多少改善されます。
なお、oo4oVer2.1で早期バインドを行うためにタイプライブラリを参照設定すると動作が不安定になるときがありましたが、oo4oVer2.2では改善されているようです。

oo4oの使い方
oo4oを使うときの基本的な流れとしては、 という流れになります。
この流れに沿って、VBAでプログラムすると
  1. ActiveXDLLであるoo4oを起動します。
  2. SQL*Net接続文字列を指定して、Oracleと接続します。
  3. SQL文を指定してレコードを取得します。
  4. CopyToClipboardを使って、クリップボードに全レコードを転記します。
  5. クリップボードからデータシートに転記します。
  6. レコードセットを開放します。
  7. Oracleとの接続を開放します。
  8. oo4oとの接続を開放します。
のようになります（リスト７）。

リスト７　oo4oサンプル
Private Sub Workbook_Open()
    Dim objSess         As Object
    Dim objDb           As Object
    Dim objDs           As Object
    Dim objCol          As Object
    Dim strError        As String
    Dim ilngLoop        As Long
    Dim strRange        As String
  
    On Error GoTo errOpen:
    
'1. ActiveXDLLであるoo4oを起動します。
    Set objSess = CreateObject("OracleInProcServer.XOraSession")
'2. SQL*Net接続文字列を指定して、Oracleと接続します。
    Set objDb = objSess.OpenDatabase("PCDN", "scott/tiger", 0&)
'3. SQL文を指定してレコードを取得します。
    Set objDs = objDb.DbCreateDynaset("select * from emp", 12&)
 
    Set objCol = objDs.Fields
    For ilngLoop = 1 To objCol.Count
        Worksheets("データシート").Cells(1, ilngLoop).Value = objCol(ilngLoop - 1).Name
    Next
     
'4. CopyToClipboardを使って、クリップボードに全レコードを転記します。
    objDs.CopyToClipboard -1
'5. クリップボードからデータシートに転記します。
    Sheets("データシート").Select
    Range("A2").Select
    ActiveSheet.Paste
    Sheets("ＵＩシート").Select
'6. レコードセットを開放します。
    Set objDs = Nothing
'7. Oracleとの接続を開放します。
    Set objDb = Nothing
'8. oo4oとの接続を開放します。
    Set objSess = Nothing
  
    With Worksheets("ＵＩシート")
        .Range("5:6").Copy
        For ilngLoop = 7 To 27 Step 2
            strRange = CStr(ilngLoop) & ":" & CStr(ilngLoop + 1)
            .Range(strRange).Select
            ActiveSheet.Paste
        Next
    End With

exitOpen:
    On Error Resume Next
    Set objDs = Nothing
    Set objDb = Nothing
    Set objSess = Nothing
    Exit Sub
    
errOpen:
    MsgBox "WorkBook_Open:" & Error$, vbOKOnly & vbExclamation
    Resume exitOpen:
End Sub
oo4oは、Ver2.2になってパフォーマンスが改善されたり色々な新機能が追加されていますので、機会がありましたら、改めて紹介してみたいと思っています。

さて、ExcelのVBAを使ってRDBMSと連携する方法を何通りか紹介してきました。特に参照系や集計系などのシステムではVisual Basicで作成したときに匹敵するものがExcelで作れると思います。また、RDBMSの値を使ってシュミレーション（試算）するようなものでは、Visual Basicを使うよりも柔軟なシステムを構築できる可能性があります。Microsoft Officeの中でも一番完成度が高く、最も利用されているExcelをクライアントアプリケーションの中核にしたシステム開発を検討する価値は充分あると言えるでしょう。


VB Magazine ライブラリ | Visual Basic Workgroup
int21 ホームページ | PCDN ホームページ

PCDN LOGO