Dear Friends,
Using Excel Macros (VBA) you can connect to Databases like SQL, Oracle or Access DB. In this Article, I will teach you, how you can do a connection with a Oracle Server.
We can do connection with Oracle either by giving SID (Oracle System ID) or Service Name. Whichever is available for the connection, we can use them to connect to the Oracle Database.
Prerequisite
Before running the below code, you need to Add reference for ADODB Connection. If you do not know how to add references for ADODB connection in Excel workbook, follow below steps.
Step to Add ADODB Connection References in Excel
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
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
is there any free vba obfuscator to protect my modules and vba project?
Hi Kannan,
Sorry i do not know any tool as such.
Thanks,
Vish
Is there a way to embed a ".xlsm" excel file to blogspot so that the users can actually do the calculation on the blog without me sharing either the excel or code? Is it feasible to do this? Does excel have this capabilities?
Hi,
I know you can embed spreadsheets developed at OneDrive (the Microsoft online free site). You can also embed ZOHO spreadsheets.
Check out my site formulafacil.blogspot.com. There are some MS spreadsheets embedded there.
Cheers,
The excel chart and picture displaces when the macro convert a range into a pdf file. How can I fix this issue so that the macro picks up respective cells and pics to convert into a pdf report.
The excel chart and picture displaces itself out of range when the vba macro converts it into an pdf report? How can this be fixed?
Hi,
Can you email me the code which you used. I guess you need to resize the chart before putting it in to pdf report.
Hi there, simply changed into alert to your blog thru Google, and found that it is really informative. I am gonna be careful for brussels. I'll appreciate in the event you proceed this in future. A lot of folks will be benefited from your writing. Cheers!
Thanks Beats for your beautiful words !!!
Hi Mishra,
your code works great, thanks for posting. I have been trying this for many days and your post is spot on. What i was looking for, very much helpful. However, when i am able to use only SID, i am not able to connect via Service name. Can you post me an example?. Thanks a lot.
Srikanthan N.
I actually found this on Google top 5 results when i was searching something! I think that says it all.
Thanks Anil 🙂
Can you add an example to this to show how we can execute the query and copy to the result set to excel?
Thanks for the feedback. You will get the next article with an example as well as a Free Sample Excel File with Completely configurable file.
Hello Vishwa,
I’m trying to run this code, but the whole excel file gets close when the macro execute the “con.Open (strCon)” line. I’m using excel 2003 in a windows xp Sp3 PC. Here is the code i made:
Private Sub Conex_Click()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim query As String
Dim Host As String
Dim Port As String
Dim SID As String
Dim User As String
Dim Password As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
‘these are not the real values
Host = Host
Port = Port
SID = SID
User = User
Password = Password
strCon = “Driver={Microsoft ODBC for Oracle}; ” & _
“CONNECTSTRING=(DESCRIPTION=” & _
“(ADDRESS=(PROTOCOL=TCP)” & _
“(HOST=” & Host & “)(PORT=” & Port & “))” & _
“(CONNECT_DATA=(SID=” & SID & “))); uid=” & User & “; pwd=” & Password & “;”
MsgBox strCon
con.Open (strCon)
End Sub
Thanks for the help!
Cheers.
Hi Vishwa,
Thanks for the knowledge sharing.The information regarding db connection is very useful in my current task.
i have a query regarding Date field format.i m fetching the db records successfully into excel but after that Date format is changed.Can u plztell me how we can keep the format of date field as it is in db while or after fetching the records using the conn string u mentioned.?
Thanks,
Manoj
Can you please tell, where we can get all the information about the host, description, port number.
Hi vishwa,
Is there any macro tool available to connect to Edifecs management application
can u please help me with macro code to connect to Edifecs management
Hi,
I try to run below script to connect Oracle DB, but its throwing error
Error message:
Run time error ‘-2147467256’ (80004005)
Automation error
Unspecified error
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=Arun-PC)(PORT=1521))” & _
“(CONNECT_DATA=(SID=orcl))); uid=SYS; pwd=msi13owner;”
‘— 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
Please can you tell me what’s wrong in my script.
Thanks
Arun kumar
HI , Thanks i am getting an Error when i use the same code, it says automation error, Unexpected error while running the last connection.str open command
Hi Allwyn,
Can you post the piece of code you are using. This error is due to incorrect syntax used in your Connection string.
when i tried to connect oracle data base with appropriate info, got below error message. can you pls help me on this
Error:-
The Oracle(tm) client and networking components were not found.
These components are supplied by oracle corporation and are part of the oracle version 7.3(or greater)client softwre installation.
You will be unable to use this driver until these components have been installed.
What is these components, and how to install it.
Can you please help me on this
Thanks for your code!!! 🙂
It helped me a lot!
Wish you success!!!
Hi guys,
Great Tuto, many thanks !
How can I once connected launch a Query ?
I
Hey,
I would like to know if it’s possible to extract data from “Oracle business intelligence discoverer desktop” to excel using a Macro. Oracle asks for a username and password before i want to enter any query.
to connect oracle data base with appropriate info, got below error message. can you pls help me on this
Error:-
The Oracle(tm) client and networking components were not found.
These components are supplied by oracle corporation and are part of the oracle version 7.3(or greater)client softwre installation.
You will be unable to use this driver until these components have been installed.
What is these components, and how to install it.
code i’m using
Sub Ora_Connection()
Dim con
Dim rs
Dim query As String
Set con = CreateObject(“ADODB.Connection”)
Set rs = CreateObject(“ADODB.Recordset”)
‘—- Replace below highlighted names with the corresponding values
strCon = “Driver={Microsoft ODBC for Oracle}; ” & _
“CONNECTSTRING=(DESCRIPTION=” & _
“(ADDRESS=(PROTOCOL=TCP)” & _
“(HOST=dweorddbv1.hq.bn-corp.com)(PORT=1521))” & _
“(CONNECT_DATA=(SID= einvd))); uid=INV_STG; pwd=INV_STG111;”
‘— 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
Hi,
You can refer the Oracle website to get the relevant Oracle client. Without installing that you would not be able to use this VBA code.
Oracle Client 11g: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html
What about for windows 8.1 (64bit)..which one i have to download…for similat type of issue
Am trying to connect oralce Database through Excel Macro..
But when i run script i got below error message.
Can you please suggest me how to resolve this issue
Error:-
[Microsoft][ODBC driver for oracle][Oracle]Error while trying to retrive text for error ORA-01019
After service name code, I am getting error “[Microsoft][ODBC driver for Oracle][Oracle]ORA-28030: Server enocountered problems accessing LDAP directory service”
Please help.
Moreover I wanted to connect oracle server on cloud.
Hello guys
can you please help me in connecting postgresql data base through excel vba.
thanks in advance
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;”
Above code is not working for me, i connected using server name…
plz guide me on above syntax!
superb piece of code worked like a charm, many thanks for this wonderful site and efforts taken to help many like me.
Hi Vishwa, i am getting below error
Run-time error ‘-2147467259 (80004005)’:
LMicrosoftlLODEC Driver Manager) Data source name not found and no
default driver specified
strcon = “Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX)(PORT=XXXX))(CONNECT_DATA=(SID=XXXX))); uid=XXXX; pwd=XXX;”
‘Open the ODBC Connection using this statement
cnDB.Open strcon
Hi Gaurav,
You need to install ODBC driver in your system in order to make a connection to database. You can refer the link for drivers to download and install. http://www.oracle.com/technetwork/database/windows/index-098976.html
hi
i am using the code provided by you..but i want to know the parameters how to give?from where i will get? i created odbc connection for oracle db .pls help me
Hi,
I need your help as i am getting error as below:
Run-time error ‘-2147467259 (80004005)’:
Automation Error
Unspecified Error
Below is the code I am using.
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
strCon = “Driver={Microsoft ODBC for Oracle}; ” & _
“CONNECTSTRING=(DESCRIPTION=” & _
“(ADDRESS=(PROTOCOL=TCP)” & _
“(HOST=xyzhostname)(PORT=1521))” & _
“(CONNECT_DATA=(SERVICE_NAME=xyzservicename))); uid=username; pwd=dummypwd;”
con.Open (strCon)
End Sub
Please help its urgent.
Hi, can you run the program in debug mode and let me know at which step you are getting this error?
I am getting error on this line
con.Open (strCon)
I am getting error on this line.
con.Open (strCon)
Please help. I tried all combination but still same.
Hi,
Have you installed oracle client in your system. Easy way to check is try connecting your Databse by TOAD or any other client. If you are unable to, then you can install the oracle client from the Oracle website. This should solve the problem.
I installed the oracle client from here http://www.oracle.com/technetwork/topics/winx64soft-089540.html
and set the path accordingly
Also I have Oracle SQL Developer installed, i am able to query from here.
Ok then there must be a problem with the connection string you are using. Kindly check the syntax and input as per the string format I have shared here on this page.
hi vishwa, my same code is not working it is always asking me for the data source name…. can u help me how to find that.?
Hi Sonali,
Without looking at your code, I can not help you with this. With the error you mentioned.. it looks like there some issue with the Source Name/path etc. Can you share your connection string by masking the real data.
hi I am getting below error:
[Microsodt] [ODBC driver for oracle] [Oracle] ORA-1215 ;TNS could not resolve the connect identifier specified
strCon = “Driver={Microsoft ODBC for Oracle}; ” & _
“CONNECTSTRING=(DESCRIPTION=” & _
“(ADDRESS=(PROTOCOL=TCP)” & _
“(HOST=srvXXXXXo.XXXXXXXk.us-east-1.rds.amazonaws.com)(PORT=1648))” & _
“(CONNECT_DATA=(SERVICE_NAME=DBTEST))); uid=db_username; pwd=XXXXXX;”
‘— Open the above connection string.
con.Open (strCon)
Hi Friend,
can you please help the connect the oracle using vba in Windows10, as above code is only working Windows7.
Thank You,
Saif
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=SCOTTUSER” & _
“(ADDRESS=(PROTOCOL=TCP)” & _
“(HOST=localhost)(PORT=1521))” & _
“(CONNECT_DATA=(SERVICE_NAME=orcl))); uid=scott; pwd=tiger;”
‘— 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
query = “select * from emp;”
End Sub
i am getting a run time error …..Please Help!!!!!
Hi,
I’m getting Data source name not found and no default driver specified run time error for the below code. Please help me to resolve this issue
strconnection= "Driver={Microsoft ODBC for Oracle}; " & "CONNECTSTRING=(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP) (HOST=localhost)(PORT=1521))" & "(CONNECT_DATA= (SERVER = DEDICATED) (SERVICE_NAME=orcl))); uid= userid; pwd=password;"Hi Saranya, try the following code and it should work:
strconnection= "Driver={Microsoft ODBC for Oracle}; " & "CONNECTSTRING=(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP) (HOST=localhost)(PORT=1521))" & "(CONNECT_DATA=(SERVICE_NAME=orcl))); uid= userid; pwd=password;"