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