Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 440 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Excel query to Oracle Database

TerryChen
Member Posts: 397
Dear all:
I want to use Excel link to Oracle Database to query data, and I don't want to install the Oracle Client and through ODBC. Can some one tell me how to do?
Regards
Terry
I want to use Excel link to Oracle Database to query data, and I don't want to install the Oracle Client and through ODBC. Can some one tell me how to do?
Regards
Terry
Best Answer
-
Yes, you can use OLE DB...
http://blog.mclaughlinsoftware.com/microsoft-excel/how-to-query-oracle-from-excel-2007/
Hope this helps,
Regards,
http://www.oracleracexpert.com
Install ASM lib in 10g
http://www.oracleracexpert.com/2009/08/install-and-configure-asmlib-in-10g.html
createdisk, deletedisk and querydisk in ASM
http://www.oracleracexpert.com/2009/09/createdisk-deletedisk-and-querydisk-in.html
Answers
-
Here you go for simple and easy steps Excel query to Oracle Database using ODBC
http://www.databasejournal.com/features/oracle/article.php/3358411/Connecting-with-Oracle-Accessing-Oracle-via-Access-and-Excel.htm
Regards
http://www.oracleracexpert.com
Overview of Transparent Application Failover in Oracle RAC
http://www.oracleracexpert.com/2010/04/overview-of-transparent-application.html
In function 'lcdprm':: Warning after patch in RAC
http://www.oracleracexpert.com/2010/04/in-function-lcdprm-warning-gets.html
Controlfile and Server parameter file AUTOBackup
http://www.oracleracexpert.com/2010/05/controlfile-and-server-parameter-file.html -
Dear Satishbabu:
Thank you for your reply. And I don't want to use the ODBC to link between Excel and Oracle. Is there another method to do ? Thank you!
Regards
Terry -
Yes, you can use OLE DB...
http://blog.mclaughlinsoftware.com/microsoft-excel/how-to-query-oracle-from-excel-2007/
Hope this helps,
Regards,
http://www.oracleracexpert.com
Install ASM lib in 10g
http://www.oracleracexpert.com/2009/08/install-and-configure-asmlib-in-10g.html
createdisk, deletedisk and querydisk in ASM
http://www.oracleracexpert.com/2009/09/createdisk-deletedisk-and-querydisk-in.html -
Terry,
I think I'm trying to do the same thing you are and that is not use ODBC/DSN. I think the solution is to use ADO. Here are some links that I found useful.
[http://www.mrexcel.com/forum/showthread.php?t=441453&highlight=oracle+connection+string]
[http://www.mrexcel.com/forum/showthread.php?t=427548&highlight=oracle+connection+string&page=2]
[http://www.mrexcel.com/forum/showthread.php?t=459863&highlight=oracle+connection+string] [http://www.mrexcel.com/forum/showthread.php?t=458814&highlight=oracle+connection+string]
[http://www.erlandsendata.no/english/index.php?d=envbadacconnstring]
Here is some code I'm using:
Sub Main()
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command
Dim sqlText As String
Dim Row As Long
Dim Findex As Long
Dim Data As Worksheet
Dim X As Long
Dim UID As String
Dim PWD As String
Dim Server As String
Application.Calculation = xlCalculationManual
UID = "USERID" 'Enter the User ID
PWD = "PASSWRD" 'Enter the password
Server = "SERVER" 'This comes from your TNSNames.ora file
Set Data = Sheets("Sheet1") 'Change this to the name of the sheet you want to return data to
Data.Select
Range("A:F").ClearContents 'Change A:F to the range you expect data to return to
Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=" & Server & ";" & "USER ID=" & UID & ";PASSWORD=" & PWD 'Note, I am using MSDAORA as I use an ORACLE DB, you will need to change it for what DB you are using
Cmd.ActiveConnection = Conn
Cmd.CommandType = adCmdText
' Put your query next
sqlText = " select B.CSE_ID AS ORIGINAL_ID, M.CSE_ID AS REF_CASE_ID, M.PGM_ID AS REF_PGM_CODE, " & _
" RPT.TEXT AS REF_PGM_DESC, PAT.PTNT_FNAME AS FIRST_NAME, PAT.PTNT_LNAME AS LAST_NAME, " & _
" FROM OPTUSER.DD_BOEHEALTH2 b LEFT JOIN SUMM_CCS.MEMBER_REF M ON B.CSE_ID=M.CSE_ID "
Cmd.CommandText = sqlText
Set RS = Cmd.Execute
For X = 0 To 17 'Change to the number of columns you are selecting MINUS 1. this loops through the column names
' in the query and puts them in the spreadsheet
Data.Cells(1, X + 1) = RS.Fields(X).Name
Next
Do While Not RS.EOF 'this loops through the data and puts it in the spreadsheet
Row = Row + 1
For Findex = 0 To RS.Fields.Count - 1
Data.Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
Next Findex
RS.MoveNext
Loop
Application.Calculation = xlCalculationAutomatic
Application.Calculate
End Sub
Hope that helps,
DanD -
Dear DanD:
Thank you for your sharing. It's very useful.
Regard
Terry -
Hi,
When i try to run the above given code in excel, getting the below error on the line "Dim Conn As New ADO.Connection". Note - I try to connect without oracle client installed in my machine.
---------------------------
Microsoft Visual Basic
---------------------------
Compile error:
User-defined type not defined
---------------------------
OK Help
---------------------------
Please help me to rectify it.
This discussion has been closed.