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
[Windows]IISセッションタイムアウト設定

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

microsoft
[Windows]共有フォルダのアクセス権を設定する

アクセス権の種類 共有フォルダのアクセス権は[共有アクセス許可]と[NTFSアクセス許可]の2つの許可エントリのセットによって決定します。 [共有アクセス許可] …

Microsoft TeamsのアカウントがないユーザをWEB会議に招待する方法

Microsoft Teamsは、Microsoftが提供しているコミュニケーションツールです。Teamsの機能として、チャットや音声通話・WEB会議などを利用 …

Microsoft365(Office2019)にVisioをインストールする方法

Office2019以降のインストール形式は、クイック実行形式に統一され、従来のインストーラーを使ったMSI形式のインストールはできなくなっています。 また、ク …

microsoft
エクセルで円と銭の通貨単位を表示する

Excelで入力した数値に円と銭の単位を表示する方法を記載します。 2種類の表示方法がありますので、用途に応じて使い分けてください。 セルの書式設定で表示 セル …

microsoft
[Oracle]VBAからSELECT文を実行しデータを取得する方法

VBAからOracleデータベースでSELECT文を実行してデータを取得するライブラリを作成しましたので記載します。 Oracle接続にはOracle Clie …

エクセルファイルを無料のビューア(Excel Mobile)で開く【Excel Viewerの代替手段】

Microsoft Officeがインストールされていないパソコンでエクセルを開くために、マイクロソフトが提供していたMicrosoft Excel Viewe …

VBAからSQL Serverに接続する方法(Microsoft OLE DB Provider for SQL Server)

VBAから業務システムなどに使用しているデータベースに接続し、データを抽出することができます。 定期的に作成する資料などは、VBAでデータ取得処理を自動化し、ボ …

microsoft
[Windows]共有フォルダへのアクセスログを取得する方法

VVAULT AUDITを使用したログ管理 Windowsでは監査ポリシーを設定することにより共有フォルダやファイルへのアクセスログを記録し、インベントビューア …

microsoft
[Windows]Active ReportsでPDF出力時の注意事項

PDF出力時の「LicenseException」対応方法 環境 ActiveReports for .NET 7.0J 言語:C# 設定 Active Rep …