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
関連記事
-
-
Office展開ツールを使用してMicrosoft(Office)365のインストールと更新プログラムを配信する
この記事では、Office展開ツール(ODT)を使用して、社内ネットワーク上のサーバーからMicrosoft365のインストール、更新プログラムを配信する方法を …
-
-
Excelファイルの更新をメールで通知する方法【業務自動化】
ファイルを複数人で共有している時など、更新時にメンバーへ連絡することがルール化されている場合があります。 毎回、口頭や手動でメールで連絡していると、連絡漏れや連 …
-
-
[Windows]IISセッション管理(状態サーバ)
IISのセッションをアウトプロセスで管理する方法 IISのセッションはインプロセスが既定設定となっておりワーカープロセスが再起動されるたびにセッション情報が破棄 …
-
-
VBAでひらがな・カタカナの小文字(捨て仮名)を全角に変換する
VBAで半角文字から全角文字に変換したり、ひらがなをカタカナに変換するには、「StrConv」関数に引数を指定することで意図した文字に変換することができます。た …
-
-
[Windows]IISセッションタイムアウト設定
IISのセッションタイムアウトを設定する機会がありましたので、設定手順について記載します。 IIS7にセッションタイムアウトを設定する IISではASP.NET …
-
-
[Windows]「このリモートコンピューターのIDを識別できません。接続しますか?」の対応方法
警告の原因と対応方法 リモートデスクトップ接続をしたときに表示されるこのメッセージ 「このリモートコンピューターのIDを識別できません。接続しますか?」 「はい …
-
-
[Windows]WSUSサーバの構築手順 2/6
Windows 2008 R2サーバにWSUSサーバを構築する Report Viewerのインストール Microsoftのサイトより[Microsoft R …
-
-
Emotetの感染チェックツール「EmoCheck」を定期的に自動実行する方法
Emotetの感染チェックツールEmoCheckをドメイン環境下のパソコンで定期的に自動実行する設定方法を記載します。 自動実行の概要 ① EmoCheckを起 …
-
-
Excelのセル幅に合わせて縦横比を維持したまま画像サイズを自動調整する方法
VBAで画像を挿入することができますが、単純にセル幅に合わせて画像サイズを変更すると、縦長や横長の画像として挿入されます。 縦横比を維持したまま、画像を挿入する …
-
-
[Windows]WSUSサーバの構築手順 5/6
Windows 2008 R2サーバにWSUSサーバを構築する WSUS(Windows Server Update Service)の設定 [スタート]-[す …