Forum Stats

  • 3,876,238 Users
  • 2,267,082 Discussions
  • 7,912,477 Comments

Discussions

Excel query to Oracle Database

TerryChen
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
1979240

Best Answer

  • Satishbabu Gunukula
    Satishbabu Gunukula Member Posts: 663 Bronze Trophy
    edited Dec 27, 2017 12:29PM 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

  • Satishbabu Gunukula
    Satishbabu Gunukula Member Posts: 663 Bronze Trophy
    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
  • TerryChen
    TerryChen Member Posts: 397
    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
  • Satishbabu Gunukula
    Satishbabu Gunukula Member Posts: 663 Bronze Trophy
    edited Dec 27, 2017 12:29PM 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
  • 778221
    778221 Member Posts: 1
    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
    1979240778221
  • TerryChen
    TerryChen Member Posts: 397
    Dear DanD:
    Thank you for your sharing. It's very useful.




    Regard
    Terry
  • User_KA8P9
    User_KA8P9 Member Posts: 1 Blue Ribbon
    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.