Here's an example of using VBA to connect Oracle and manipulate Oracle-related data through Excel
Excel connects to the database through VBA need to install the appropriate Oracle client tools and reference ADO related components, reference ADO related components can follow the following steps:
1. Open the VBA editor and select "Tools" and "References" in the menu.
2. Ensure that the "Microsoft Activitex Data Objects 2.8 Library" and "Microsoft Activitex data Objects Recordset 2.8 Library" are selected.
To establish the connection process, the code is as follows:
Copy Code code 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 related configuration
orausr= "User"
orapwd= "Password"
ConnStr = "Provider = msdaora.1; Password= "& Orapwd & _
"; User id= "& Orausr & _
";D ata 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 related configuration
orausr= "User"
orapwd= "Password"
ConnStr = "Provider = msdaora.1; Password= "& Orapwd & _
"; User id= "& Orausr & _
";D ata 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 statements as needed, get the relevant data, and output to Excel to complete the data processing
The above code is tested under Windows XP sp3/2003 SP2 + Office2003.
