Skill Note

平凡なエンジニアがメモ代わりにプログラミング, インフラ, ネットワークを書き綴るブログ

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

      2020/01/09

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

Oracleへの接続設定

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

検索フォームを作成する

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

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

Google AdSense PC

Google AdSense PC

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

  関連記事

microsoft
Office展開ツールを使用してMicrosoft(Office)365のインストールと更新プログラムを配信する

この記事では、Office展開ツール(ODT)を使用して、社内ネットワーク上のサーバーからMicrosoft365のインストール、更新プログラムを配信する方法を …

Excelファイルの更新をメールで通知する方法【業務自動化】

ファイルを複数人で共有している時など、更新時にメンバーへ連絡することがルール化されている場合があります。 毎回、口頭や手動でメールで連絡していると、連絡漏れや連 …

microsoft
[Windows]IISセッション管理(状態サーバ)

IISのセッションをアウトプロセスで管理する方法 IISのセッションはインプロセスが既定設定となっておりワーカープロセスが再起動されるたびにセッション情報が破棄 …

microsoft
VBAでひらがな・カタカナの小文字(捨て仮名)を全角に変換する

VBAで半角文字から全角文字に変換したり、ひらがなをカタカナに変換するには、「StrConv」関数に引数を指定することで意図した文字に変換することができます。た …

microsoft
[Windows]IISセッションタイムアウト設定

IISのセッションタイムアウトを設定する機会がありましたので、設定手順について記載します。 IIS7にセッションタイムアウトを設定する IISではASP.NET …

microsoft
[Windows]「このリモートコンピューターのIDを識別できません。接続しますか?」の対応方法

警告の原因と対応方法 リモートデスクトップ接続をしたときに表示されるこのメッセージ 「このリモートコンピューターのIDを識別できません。接続しますか?」 「はい …

microsoft
[Windows]WSUSサーバの構築手順 2/6

Windows 2008 R2サーバにWSUSサーバを構築する Report Viewerのインストール Microsoftのサイトより[Microsoft R …

microsoft
Emotetの感染チェックツール「EmoCheck」を定期的に自動実行する方法

Emotetの感染チェックツールEmoCheckをドメイン環境下のパソコンで定期的に自動実行する設定方法を記載します。 自動実行の概要 ① EmoCheckを起 …

microsoft
Excelのセル幅に合わせて縦横比を維持したまま画像サイズを自動調整する方法

VBAで画像を挿入することができますが、単純にセル幅に合わせて画像サイズを変更すると、縦長や横長の画像として挿入されます。 縦横比を維持したまま、画像を挿入する …

microsoft
[Windows]WSUSサーバの構築手順 5/6

Windows 2008 R2サーバにWSUSサーバを構築する WSUS(Windows Server Update Service)の設定 [スタート]-[す …