作者Eming (eming)
看板Database
標題[心得]Excel VBA接Oracle
時間Mon Jun 4 08:14:46 2012
以下是我從Excel接上Oracle的過程
花了不少時間走冤枉路 所以想說紀錄下來跟有需要的版友分享
(另外我是社會組畢業的 有些觀念不太清楚 請見諒)
系統資訊
作業系統:win7 pro 64 bits
資料庫:Oracle 10g
1.Driver選用
64-bit Oracle Data Access Components (ODAC)
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
下載解壓後 找到setup.exe 選run as administrator安裝
2.sqlnet.ora & tnsnames.ora設定
到Driver的資料夾的Network->Admin
(我的路徑如下C:->app->User->product->11.2.0->client_1->Network->Admin)
這邊我不懂為什麼Driver沒有帶我走一遍
印象中安裝32bit的Driver就有的樣子
以下是我的設定
tnsnames.ora
$DB_NAME =
(DESCRIPTION =
(ADDRESSLIST =
(ADDRESS = PROTOCOL = TCP)(HOST = $HOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = $DB_NAME)
)
)
sqlnet.ora
SQLNET.AUTHENTICATION_SEFVICES=NTS)
NAMES.DIRECTORY_PATH=(TNSNAMES)
3.ODBC設定
到我的電腦->控制台->system and security->Administrative Tools
->Data Sources(ODBC)
點開選User DSN
->Add 選Driver "Oracle in OraClient11g_home1"
Data Source Name:(自己取)
Desription:(自己取)
TNS Service Name:$DB_Name
User ID:$DB_Username
輸入後按"Test Connection"測試一下
4.Excel VBA
我覺得重要的是connect string 應該是ADO吧
DataSource要填之前在ODBC填的Data Source Name
strConOracle = "Provider=MSDASQL.1;Persist Security Info=False;" & _
"Password=XXXXXX;User ID=$DB_Username;Data Source=$DB_NAME"
還有在Command加上CommandTimeOut的property
避免復雜Query時間過長造成ORA-01013 Error
我這邊只用300秒
Sub OracelQuery()
Dim strConOracle As String
Dim oConOracle, oRsOracle, OracleCommand As Object
Dim strSQL As String
Dim lngCount As Long
Dim strCount As String
Dim x As String
Dim total_field_count, i As Integer
x = Workbooks("oracle").Worksheets("QUERY").Range("A1").Value
strConOracle = "Provider=MSDASQL.1;Persist Security Info=False;" & _
"Password=XXXXXX;User ID=$DB_Username;Data Source=$DB_Name"
Set oConOracle = CreateObject("ADODB.Connection")
Set oRsOracle = CreateObject("ADODB.RecordSet")
Set OracleCommand = CreateObject("ADODB.Command")
oConOracle.Open strConOracle
strSQL = x
With OracleCommand
.ActiveConnection = oConOracle
.CommandType = 1
.CommandText = strSQL
.CommandTimeout = 300
End With
Set oRsOracle = OracleCommand.Execute
total_field_count = oRsOracle.Fields.Count
Sheets("Unconfirmed Details").Select
Range("A2").Select
ActiveCell.CopyFromRecordset oRsOracle
For i = 1 To total_field_count
Workbooks("oracle").Worksheets("Unconfirmed
Details").Cells(1, i).Value = oRsOracle.Fields(i - 1).Name
Next
oConOracle.Close
Set oRsOracle = Nothing
Set oConOracle = Nothing
End Sub
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 24.6.30.127
※ 編輯: Eming 來自: 24.6.30.127 (06/04 08:18)
※ 編輯: Eming 來自: 24.6.30.127 (06/04 08:18)