dsn-less connection for oracle through excel vba

Question

how do i create a dsn-less connection for an oracle db through excel vba.

here's how i'm doing it in access vba (i have functions for the variables in the string, GetOracleDriver and TNSName because each machine can have their own) but in excel i don't see the oracle driver when i tried doing it by hand.

Dim NewConnect As String

NewConnect = "ODBC;DRIVER={" & GetOracleDriver & "};SERVER=" & TNSName & ".mycompany.com;UID=myuid;PWD=mypwd;DBQ=" & TNSName & ".mycompany.com;"

thank you

Answer 1

i apologize, this is another question i posted and then found the answer to

the code below was taken from http://www.vbaexpress.com/forum/showthread.php?26968-How-to-extract-data-from-Oracle-Database-into-Excel-Spreadsheet-via-VBA-code&p=186731&viewfull=1#post186731

and modified a little

Public Sub ImportData()
    Const strSQL_c As String = "SELECT * from NR_CF_CF3_DIR"
    Dim strConnection As String
    Dim strDBPath As String
    strConnection = "ODBC;DRIVER={Oracle in Oracle1};SERVER=mytnsname.mycompany.com;UID=myuid;PWD=mypwd;DBQ=mydbname.mycompany.com"

    ActiveWorkbook.Worksheets.Add
    ActiveSheet.Name = "QCF3_DIR"


    QueryDB strConnection, ActiveSheet.Cells(1, 1), strSQL_c
End Sub






Public Sub QueryDB(ByVal connectionString As String, ByVal target As Excel.Range, ByVal SQL As String)
    Dim qt As Excel.QueryTable
    Dim ws As Excel.Worksheet
    Set ws = target.Parent
    Set qt = ws.QueryTables.Add(connectionString, target, SQL)
    qt.Refresh BackgroundQuery:=False
End Sub