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

2019/06/26 2020/01/09

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

Oracleへの接続設定

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

Skill Note

[Windows]VBAからOracleに接続する方法

http://skill-note.net/post-822/

エクセルなどのOfficeソフトからADO(ActiveX Data Objects)を使用して、Oracleに接続する方法を記載します。参照設定最初にADOを使用するための参照設定を行います。-をクリックします。参照可能なライブラリファイルから「Microsoft ActiveX Data Objects X.X...

検索フォームを作成する

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

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

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

Option Explicit

Sub Button_Click()

'モーダルモードで表示

UserForm1.Show

'モードレスモードで表示

'UserForm1.Show vbModeless

End Sub

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

Message コメントをキャンセル

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

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

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

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

: 既存パソコンを有効活用した、Windows 10への移行方法を考える

企業や個人用など数多くのパソコンで稼働しているOS、Windows 7のサポート期間が2020年1月14日に終了します。サポート期間終了に向けてWindows …

: [Windows]指定サイズのファイルを作成する

ネットワークの通信速度などを調査する場合にサイズの大きなファイルを作成する必要があります。この記事では「fsutil」コマンドを使用したダミーファイルの作成方 …

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

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

: VBAからOracleのストアドプロシージャを実行する方法

VBAからOracleのストアドプロシージャを実行する方法について記載します。 VBAからOracleに接続する方法は以前の記事を参考にしてください。ストアド …

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

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

: 【パソコン不要】HDDのデータをSSDに丸ごとコピーして交換する方法

近頃はSSDの記憶容量の増加、価格の値下がりが続き、HDDとの容量・価格差も縮まり購入しやすい状況となっています。HDDからSSDに交換することで読込・書込速度 …

: Outlookでメール送信中になったまま送受信が終了しない場合

開封確認のメールが正常に送信されず、送信中のまま送受信が終了しない場合があります。原因となる開封確認メールは送信トレイに存在しないため、削除することができませ …

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

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

PREV: エクセルで円と銭の通貨単位を表示する
NEXT: VBAでひらがな・カタカナの小文字(捨て仮名)を全角に変換する

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

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

Oracleへの接続設定

検索フォームを作成する

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

検索フォームの動作確認

検索フォームの表示

Message コメントをキャンセル

関連記事

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

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

既存パソコンを有効活用した、Windows 10への移行方法を考える

[Windows]指定サイズのファイルを作成する

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

VBAからOracleのストアドプロシージャを実行する方法

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

【パソコン不要】HDDのデータをSSDに丸ごとコピーして交換する方法

Outlookでメール送信中になったまま送受信が終了しない場合

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

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

Oracleへの接続設定

検索フォームを作成する

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

検索フォームの動作確認

検索フォームの表示

Google AdSense PC

Google AdSense PC

Message コメントをキャンセル

関連記事