Excel connects to Oracle through ADO and operates Oracle, which brings great benefits to those who are not strong in programming. Combined with Excel Data Processing and chart creation, you can easily handle some common tasks.
Excel connects to Oracle through ADO and operates Oracle, which brings great benefits to those who are not strong in programming. Combined with Excel Data Processing and chart creation, you can easily handle some common tasks.
The following is an example of using Excel VBA to connect to Oracle and operate Oracle-related data.
To connect to the database through VBA, Excel needs to install the corresponding Oracle client tool and reference the relevant components of ADO. to reference the components of ADO, follow these steps:
1. Open the VBA editor and click "Tools" and "Reference" in the menu ";
2. Make sure that "Microsoft ActiviteX Data Objects 2.8 Library" and "Microsoft ActiviteX Data ObjectS Recordset 2.8 Library" are checked.
The code for establishing a connection is as follows:
The Code is as follows:
Public Sub ConOra ()
On Error GoTo ErrMsg:
Dim ConnDB As ADODB. Connection
Set ConnDB = New ADODB. Connection
Dim ConnStr As String
Dim DBRst As ADODB. Recordset
Set DBRst = New ADODB. Recordset
Dim SQLRst As String
Dim OraOpen As Boolean
OraOpen = False
OraID = "Orcl" 'oracle Database Configuration
OraUsr = "user"
OraPwd = "password"
ConnStr = "Provider = MSDAORA.1; Password =" & OraPwd &_
"; User ID =" & OraUsr &_
"; Data Source =" & OraID &_
"; Persist Security Info = True"
ConnDB. CursorLocation = adUseServer
ConnDB. Open ConnStr
OraOpen = true' after successful execution, the database is opened
'Msgbox "Connect to the oracle database Successful! ", VbInformation," Connect Successful"
DBRst. ActiveConnection = ConnDB
DBRst. CursorLocation = adUseServer
DBRst. LockType = adLockBatchOptimistic
SQLRst = "Select * From TstTab"
DBRst. Open SQLRst, ConnDB, adOpenStatic, adLockBatchOptimistic
DBRst. MoveFirst
Exit Function
ErrMsg:
OraOpen = False
MsgBox "Connect to the oracle database fail, please check! ", VbCritical," Connect fail! "
End Function
Public Sub ConOra ()
On Error GoTo ErrMsg:
Dim ConnDB As ADODB. Connection
Set ConnDB = New ADODB. Connection
Dim ConnStr As String
Dim DBRst As ADODB. Recordset
Set DBRst = New ADODB. Recordset
Dim SQLRst As String
Dim OraOpen As Boolean
OraOpen = False
OraID = "Orcl" 'oracle Database Configuration
OraUsr = "user"
OraPwd = "password"
ConnStr = "Provider = MSDAORA.1; Password =" & OraPwd &_
"; User ID =" & OraUsr &_
"; Data Source =" & OraID &_
"; Persist Security Info = True"
ConnDB. CursorLocation = adUseServer
ConnDB. Open ConnStr
OraOpen = true' after successful execution, the database is opened
'Msgbox "Connect to the oracle database Successful! ", VbInformation," Connect Successful"
DBRst. ActiveConnection = ConnDB
DBRst. CursorLocation = adUseServer
DBRst. LockType = adLockBatchOptimistic
SQLRst = "Select * From TstTab"
DBRst. Open SQLRst, ConnDB, adOpenStatic, adLockBatchOptimistic
DBRst. MoveFirst
Exit Function
ErrMsg:
OraOpen = False
MsgBox "Connect to the oracle database fail, please check! ", VbCritical," Connect fail! "
End Function
You can adjust the SQL statement as needed to obtain relevant data and output it to Excel for data processing.
The above code is tested and passed in Windows XP SP3/2003 SP2 + Office2003.
