Excel VBAでOracleのデータを参照するマスター検索機能を作成する

エクセルのVBAでOracleに保存されているマスタを検索する機能を作成する機会がありましたので、サンプルプログラムを記載します。

Oracleへの接続設定

VBAからOracleに接続するにはADOの参照追加とOracle Clientのインストールが必要になります。
詳しくは以前に記載した「VBAからOracleに接続する方法」を参照して各種設定を行ってください。

エクセルなどのOfficeソフトからADO(ActiveX Data Objects)を使用して、Oracleに接続する方法を記載します。参照設定最初にADOを使用するための参照設定を行います。-をクリックします。参照可能なライブラリファイルから「Microsoft ActiveX Data Objects X.X Library」を選択します。 icon-check-square-o ADOのバージョンはOSなどの環境によって異なります。接続文字列一覧ADOからOracleへ接続するインターフェースにはOLE DBやODBCなどがありますが、ここではOLE DBでの接続文字列を記載します。Oracle社製のOLE DBを使用す...

検索フォームを作成する

Visual Basic Editor(VBE)を起動します。(Alt + F11 キーを押すと起動します)
VBEが起動したら新規のユーザーフォームを追加します。
ツールボックスから検索文字入力用のテキストボックス、明細表示用のリストボックス、検索ボタンを配置し、その他に必要に応じてラベルなどを追加してください。

検索ボタンクリック時の処理

フォーム上の検索ボタンをダブルクリックするとプロシージャが作成されますので、ここにクリック時の処理を記載します。
下記はテキストボックスの文字列に部分一致するデータを表示するサンプルプログラムです。

Option Explicit

Const DSN As String = ""    'データソース名
Const USER As String = ""   'ユーザ名
Const PWD As String = ""    'パスワード

Private Sub CommandButton1_Click()
    On Error GoTo ERR_HANDLER   'エラー発生時の処理
    
    Dim strCn As String
    Dim strSQL As String
    Dim i As Long
    
    'オブジェクトの作成
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    'TNSサービスを使用してOracleに接続
    strCn = ""
    strCn = strCn + "Provider=OraOLEDB.Oracle;"
    strCn = strCn + "Data Source=" + DSN + ";"
    strCn = strCn + "User ID=" + USER + ";"
    strCn = strCn + "Password=" + PWD + ";"
    cn.Open strCn
    
    'SQLの実行
    strSQL = ""
    strSQL = strSQL + " SELECT SHOHIN_CD"
    strSQL = strSQL + "      , SHOHIN_NM"
    strSQL = strSQL + " FROM SHOHIN"
    '大文字/小文字,全角/半角,ひらがな/カタカナを区別しないで検索
    strSQL = strSQL + " WHERE UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE(SHOHIN_NM)),'kana_fwkatakana')"
    strSQL = strSQL + "  LIKE '%' || UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE('" + TextBox1.Text + "')),'kana_fwkatakana') || '%'"
    strSQL = strSQL + " ORDER BY SHOHIN_CD"
    rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
    
    '取得データをリストボックスに表示
    With ListBox1
        .Clear
        .ColumnCount = 2            '列数
        .ColumnWidths = "40;125"    '列幅
        
        Do Until rs.EOF
            .AddItem ""
            For i = 0 To rs.Fields.Count - 1
                If Not IsNull(rs(i).Value) Then
                    .List(.ListCount - 1, i) = rs(i).Value
                End If
            Next
            rs.MoveNext
        Loop
    End With
    
    'オブジェクトのクローズ
    rs.Close
    cn.Close
        
    'オブジェクトの開放
    Set rs = Nothing
    Set cn = Nothing
    
    Exit Sub
    
ERR_HANDLER:
    'オブジェクトの開放
    Set rs = Nothing
    Set cn = Nothing
    
    Exit Sub
    
End Sub

検索フォームの動作確認

プロシージャの記載が終わったら検索処理が正しく動作するか確認します。
F5キーを押すと検索フォームが起動されますので、検索ボタンを押し、データベースの接続・データの取得ができるか確認します。

検索フォームの表示

標準モジュールを追加して検索フォームを表示するプロシージャを作成します。

Option Explicit

Sub Button_Click()

    'モーダルモードで表示
    UserForm1.Show
    'モードレスモードで表示
    'UserForm1.Show vbModeless

End Sub

あとは、ワークシート上に追加したボタンから作成したプロシージャを呼び出します。