Using Excel Macros (VBA) you can connect to any Databases like SQL, Oracle or Access DB. In this Article you will learn, how you can do a connection with a Oracle Server.
We can do connection with Oracle either by giving SID or Service Name. Whichever is available for the connection, we can use connect to the Oracle Database.

Before running the below code, you need to Add reference for ADODB Connection. Follow below steps to Add Reference:

1. Go to VB Editor Screen (Alt+F11)
2. Tools –> References…
3. From the List of Available References Select “Microsoft ActiveX Data Objects 2.0 Library” You can select 2.0 Version or any other higher version of this reference.
4. Click OK

||  Connection String with SID  ||  Connection String with Service(DB Name)  ||

Oracle Connection String with SID:

Use the below Code for connecting to the Oracle Database.

Sub Ora_Connection()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim query As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
'---- Replace below highlighted names with the corresponding values
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=Your Host Name)(PORT=Port Number))" & _
"(CONNECT_DATA=(SID=SID of your Database))); uid=User ID; pwd=Password;"
'---  Open   the above connection string.
con.Open (strCon)
'---  Now connection is open and you can use queries to execute them. 
'---  It will be open till you close the connection
End Sub

Oracle Connection String with Service:

Use the below Code for connecting to the Oracle Database.


Sub Ora_Connection()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim query As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
'---  Replace below highlighted names with the corresponding values
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=Your Host Name)(PORT=Enter Port Number))" & _
"(CONNECT_DATA=(SERVICE_NAME=database))); uid=Enter User ID; pwd=Enter Password;"
'---  Open the above connection string.
con.Open (strCon)
'---  Now connection is open and you can use queries to execute them.
'---  It will be open till you close the connection
End Sub

Read this Also: