VBAからOracleに接続する方法(Oracle Provider for OLE DB接続)
2020/06/01
VBAから業務システムなどに使用しているデータベースに接続し、データを抽出することができます。
定期的に作成する資料などは、VBAでデータ取得処理を自動化し、ボタン一つで最新情報に更新することが可能になるため、資料作成にかかる時間を大幅に短縮することが期待できます。
この記事ではExcel VBAからOracleに接続してデータを取得する方法を記載します。
参照設定
ADO(ActiveX Data Objects)を使用することで、エクセルなどのOfficeソフトからOracleに接続することができます。
最初にADOを使用するための参照設定をExcelファイルに設定しましょう。
VBEを起動して[ツール]-[参照設定]の順でクリックします。
参照可能なライブラリファイルから「Microsoft ActiveX Data Objects X.X Library」を選択します。
※ADOのバージョンはOSなどの環境によって異なりますので、ご使用の環境に合わせて読みかえてください。
Oracle Clientのインストール
ADOからOracleへ接続するインターフェースとしてOLE DBやODBCなどがあります。
この記事ではOracle Provider for OLE DBを使用したOracle接続方法を記載していきます。
Oracle Provider for OLE DBはOracle Clientをインストールすることで使用可能になります。
※Oracle ClientとOfficeソフトのビット数(32ビット or 64ビット)が一致したバージョンをインストールしてください。
Oracle Database 12c Release 2 (12.2.0.1.0)
https://www.oracle.com/database/technologies/oracle12c-windows-downloads.html
Oracle Database 11g Release 2 (11.2.0.1.0) 64bit
https://www.oracle.com/database/technologies/112010-win64soft.html
Oracle Database 11g Release 2 (11.2.0.1.0) 32bit
https://www.oracle.com/database/technologies/112010-win32soft.html
Oracleデータベースへの接続文字列
Oracleへの接続情報の設定は、tnsnames.oraの設定情報を参照する方法とソースコード内に直接記載するの2種類があります。
'Oracle社 OLE DB接続 'tnsnames.oraを使用する場合 "Provider=OraOLEDB.Oracle;Data Source=ネットサービス名;User ID=ユーザID;Password=パスワード" 'Oracle社 OLE DB接続 '接続情報を直接記載する場合 "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=サーバ名)(PORT=ポート番号))(CONNECT_DATA=(SERVICE_NAME=サービス名)));User ID=ユーザID;Password=パスワード"
サンプルプログラム
Oracleに接続してデータを取得、切断をするサンプルプログラムを記載します。
Option Explicit 'TNSサービス名で接続する場合(tnsnames.ora) Private Const PROVIDER As String = "OraOLEDB.Oracle" Private Const DATA_SOURCE As String = "orcl" 'ネットサービス名 'TNSサービス名を使用せず直接接続する場合 Private Const HOST_NAME As String = "localhost" 'データベースのホスト名orIPアドレス Private Const PORT_NO As String = "1521" 'データベースのポート Private Const SERVICE_NAME As String = "orcl" 'サービス名 'データベースのアカウント情報 Private Const USER_ID As String = "user" 'データベースのユーザID Private Const PASSWORD As String = "password" 'データベースのパスワード Sub sample() On Error GoTo ERR_HANDLER Dim strSQL As String Dim i As Long '-------------------------------- ' データベース接続 '-------------------------------- Dim cn As New ADODB.Connection 'TNSサービス名で接続する場合 cn.ConnectionString = "Provider=" & PROVIDER _ & ";Data Source=" & DATA_SOURCE _ & ";User ID=" & USER_ID _ & ";PASSWORD=" & PASSWORD cn.Open ' 'TNSサービス名を使用せず直接接続する場合 ' cn.ConnectionString = "Provider=" & PROVIDER _ ' & ";Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" _ ' & "(HOST=" & HOST_NAME & ")" _ ' & "(PORT=" & PORT_NO & "))" _ ' & "(CONNECT_DATA=" _ ' & "(SERVICE_NAME=" & SERVICE_NAME & ")))" _ ' & ";User ID=" & USER_ID _ ' & ";PASSWORD=" & PASSWORD ' cn.Open '-------------------------------- ' SQLの実行 '-------------------------------- Dim rs As New ADODB.Recordset strSQL = "select * from dual" rs.Open strSQL, cn '取得データをセルに一括出力 rs.MoveFirst ActiveSheet.Range("A1").CopyFromRecordset rs '-------------------------------- ' データベース切断 '-------------------------------- If Not rs Is Nothing Then If rs.State = adStateOpen Then rs.Close Set rs = Nothing End If If Not cn Is Nothing Then If cn.State = adStateOpen Then cn.Close Set cn = Nothing End If Exit Sub ERR_HANDLER: 'エラーメッセージ Debug.Print Err.Number & ")" & Err.Description '-------------------------------- ' データベース切断 '-------------------------------- If Not rs Is Nothing Then If rs.State = adStateOpen Then rs.Close Set rs = Nothing End If If Not cn Is Nothing Then If cn.State = adStateOpen Then cn.Close Set cn = Nothing End If End Sub
Google AdSense PC
Google AdSense PC
関連記事
-
-
エクセルファイルを無料のビューア(Excel Mobile)で開く【Excel Viewerの代替手段】
Microsoft Officeがインストールされていないパソコンでエクセルを開くために、マイクロソフトが提供していたMicrosoft Excel Viewe …
-
-
Excelのセル幅に合わせて縦横比を維持したまま画像サイズを自動調整する方法
VBAで画像を挿入することができますが、単純にセル幅に合わせて画像サイズを変更すると、縦長や横長の画像として挿入されます。 縦横比を維持したまま、画像を挿入する …
-
-
[Windows]WSUSサーバの構築手順 6/6
Windows 2008 R2サーバにWSUSサーバを構築する クライアントPCがWSUSサーバから更新プログラムをダウンロードするにはグループポリシーを設定す …
-
-
VBAで指定フォルダ内の全てのExcelファイル・シートを順番に開く方法
この記事では、VBAで指定フォルダ内の全てのExcelファイル・シートを順番に開く方法について記載します。 指定フォルダの選択は、ダイアログを表示して任意の場所 …
-
-
[Windows]IISセッションタイムアウト設定
IISのセッションタイムアウトを設定する機会がありましたので、設定手順について記載します。 IIS7にセッションタイムアウトを設定する IISではASP.NET …
-
-
WEB会議・WEB面接を快適にするおすすめ機器の紹介
新型コロナウイルスの流行拡大に伴い、WEB会議・WEB面接を導入する企業が増えてきました。 この記事では、これからWEB会議・WEB面接を導入する予定の企業や実 …
-
-
Outlookでメール送信中になったまま送受信が終了しない場合
開封確認のメールが正常に送信されず、送信中のまま送受信が終了しない場合があります。 原因となる開封確認メールは送信トレイに存在しないため、削除することができませ …
-
-
[Windows]WSUSサーバの構築手順 4/6
Windows 2008 R2サーバにWSUSサーバを構築する WSUS(Windows Server Update Service)のインストール [スタート …
-
-
[Windows]Active ReportsでPDF出力時の注意事項
PDF出力時の「LicenseException」対応方法 環境 ActiveReports for .NET 7.0J 言語:C# 設定 Active Rep …
-
-
[Oracle]VBAからSELECT文を実行しデータを取得する方法
VBAからOracleデータベースでSELECT文を実行してデータを取得するライブラリを作成しましたので記載します。 Oracle接続にはOracle Clie …