エクセルの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あとは、ワークシート上に追加したボタンから作成したプロシージャを呼び出します。