HomeОбразованиеRelated VideosMore From: WoolFool

How to connect Excel to Oracle with VBA

171 ratings | 26486 views
This video will show you how can you connect to a Oracle database and read data into your Excel sheet using VBA programming. Source Code: https://github.com/woolfool/ExcelConnectToOracle
Html code for embedding videos on your blog
Text Comments (31)
John Wilkinson (1 month ago)
Beautiful! Very elegant, clean, solution to a very common problem. The only thing I could ask for in addition (and I have to ask for more, right?) is to have the VBA code create the table from the recordset. That way you have run any number of queries from this and always have it write the complete table to the sheet, no matter what it looks like, without having to perform that manual step. I'm going to try and do that on my own, and if I do, I'll post the code, but if you want to add it here, that would be great! Thanks.
அருமை. Awesome dude ! Step by step explanation for mid-level and beginners will be useful in this video.
H Bomb (5 months ago)
How can I save a Oracle form using Excel VBI?
maxelideall (5 months ago)
Sorry, I don't speak English and maybe the translation is not correct. I want to ask you, I can do execute an SQL code if it´s much longer? I can call an file with SQL extension, from where I have the codes SQL? something like @c: \ SQLCODES \ myquery.slq for example
Luis Ernest (5 months ago)
I am getting an error when it's only one row
hridaya kumar (5 months ago)
How I will login to one Linux server on single click and get variable from Excel to Linux server
EcoExcel (8 months ago)
Thanks
Mehaa Shah (10 months ago)
Hi I seek your help. Where can I find DATA Source on my database connection details? I am using Oracle SQL Developer 3.0 When I run following SQL I get , SELECT * FROM v$version; Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production "CORE 11.2.0.4.0 Production" TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production Please can you help me knowing DATA Source? Thanks
Wladyslawa Bąk (11 months ago)
how are you greet
alvaro villena (1 year ago)
Very useful, please do more videos. Loved the intro music.
Shekhar Nalawade (1 year ago)
i am getting an error as Run time error 3706 Application Defined or object defined error
Andy Wood (5 months ago)
Me too! Driving me crazy! DSN is setup (test connects works OK) but I get that msg with no info on how to resolve it! So frustrating ... been days and days banging my head against this! Any clues what to do gratefully received!
Mike Carey (1 year ago)
Brilliant and very useful ! thank you very much.
Siddharth Madhukar (1 year ago)
How to solve Runtime error 2147 Automation error unspecified error ?
Siddharth Madhukar (1 year ago)
The error number is 2147467259 (80004005)
Siddharth Madhukar (1 year ago)
I got the error while establishing connection with my oracle database. cn.Open ( _ "User ID=SYSTEM" & _ ";Password=oracle" & _ ";Data Source=HR" & _ ";Provider=OraOLEDB.Oracle")
WoolFool (1 year ago)
Siddharth Madhukar on which line did you get this error?
please write syntax of connect it is not clear in vidio
Jhonny Walker (1 year ago)
hi, im currently having an issue wherein some users are encountering "Oracle Error: [Microsoft][ODBC dricer for Oracle][Oracle ]ORA-06413: Connection not open." when connecting to oracle database and some users were successfully connected to oracle database and was able to fetch the records.Do you have any idea how I can resolve this issue?
Vijayaraj Bijapure (1 year ago)
Hi, Where do we provide the HostName ,Port and SID required to connect to Oracle database?
Pedro Azevedo (6 months ago)
con don't exist. Create new variable "Dim str As String" set "str = "Driver={Microsoft ODBC for Oracle}; " & _ "CONNECTSTRING=(DESCRIPTION=" & _ "(ADDRESS=(PROTOCOL=TCP)" & _ "(HOST = srvpt115.pt.ind.sonae)(PORT=1521))" & _ "(CONNECT_DATA=(SID =SFCOHP))); uid=sfctask; pwd=taskohp;"" and make "cn.Open (str)"
Vijayaraj Bijapure (1 year ago)
Sub ConnectToOracle() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim Matrixdata As Variant Set cn = New ADODB.Connection Set rs = New ADODB.Recordset cn = "Driver={Microsoft ODBC for Oracle}; " & _ "CONNECTSTRING=(DESCRIPTION=" & _ "(ADDRESS=(PROTOCOL=TCP)" & _ "(HOST = pls enter the value)(PORT=pls enter the value))" & _ "(CONNECT_DATA=(SID =pls enter the value))); uid=pls enter the username; pwd=pls enter the password;" con.Open (cn) 'Cleanup at the end Set rs = Nothing Set cn = Nothing End Sub After executing the above code, we get the error "Runtime error '424' : Object required Not able to execute it
Vijayaraj Bijapure (1 year ago)
There is no database installed on my computer, I use the SQL developer with Hostname,port and SID to get connected to Oracle DB
Coy Dowell (1 year ago)
I had this issue, Run-time error ‘-2147467259 (80004005) http://dailydoseofexcel.com/archives/2011/09/01/odbc-microsoft-access-driver-is-not-a-valid-path/ got that corrected now getting Type mismatch (Error 13) on this line mtxData = Application.Transpose(rs.GetRows) it works without transpose mtxData = rs.GetRows
Coy Dowell (1 year ago)
on this line ActiveSheet.ListObjects("Oracle").DataBodyRange.Resize(UBound(mtxData, 1) - LBound(mtxData, 1) + 1, UBound(mtxData, 2) - LBound(mtxData, 2) + 1) = mtxData
Coy Dowell (1 year ago)
That seem to be it, now on to the next, Run-time error '9': Subscript out of range
WoolFool (1 year ago)
+Coy Dowell it is usually caused by NULL values in the returned result set. I had this issue lots of time. I usually solve it by: first finding out where is the NULL value, and then I wrap that field (in the SQL query) with a NVL function. Hope this helps you as well.
Om Sachdev (2 years ago)
Dont you need to give database host and port information anywhere?
xinyun kang (2 years ago)
Thanks a lot !
Kevin (2 years ago)
Thanks . Saved my day
Rajendran Ramasamy (2 years ago)
Very useful ...thank u

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.