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

2017/02/12 2020/06/01

VBAから業務システムなどに使用しているデータベースに接続し、データを抽出することができます。

定期的に作成する資料などは、VBAでデータ取得処理を自動化し、ボタン一つで最新情報に更新することが可能になるため、資料作成にかかる時間を大幅に短縮することが期待できます。

この記事ではExcel VBAからOracleに接続してデータを取得する方法を記載します。

できる大事典　Excel　VBA　2016／2013／2010／2007対応（できる大事典） [ 国本温子 ]

created by Rinker

参照設定

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社 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

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

Message コメントをキャンセル

: VBAで形式を指定して日付を取得する

VBAで西暦や和暦の日付形式に変換するには「FORMAT」関数に引数を指定することで意図した日付形式に変換することができます。日付を表示 Date関数で今日の …

: [Windows]「このリモートコンピューターのＩＤを識別できません。接続しますか？」の対応方法

警告の原因と対応方法リモートデスクトップ接続をしたときに表示されるこのメッセージ「このリモートコンピューターのＩＤを識別できません。接続しますか？」「はい …

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

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

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

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

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

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

: Excelのセル幅に合わせて縦横比を維持したまま画像サイズを自動調整する方法

VBAで画像を挿入することができますが、単純にセル幅に合わせて画像サイズを変更すると、縦長や横長の画像として挿入されます。縦横比を維持したまま、画像を挿入する …

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

Windows 2008 R2サーバにWSUSサーバを構築する Windows Server Update Services(WSUS)とは Windows S …

: [Windows]リモートデスクトップのシングルサインオン

ドメイン参加PCでシングルサインオンする Active Directoryのドメインに所属した場合、既定の設定のままでは、資格情報が使用できないため、リモート …

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

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

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

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

PREV: [Windows]指定サイズのファイルを作成する
NEXT: 無料の設定ツールを使用して複数台のiPadを一括でセットアップする

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

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