Setting Up an ODBC Data Source for Oracle using vbscript

I am trying to connect to oracle database from excel. This is working fine when I manually create the DSN, but now I want to create DSN programmtically using VB. 开发者_如何学运维Please help me I am new to vbscripting.

Here is some sample code:

Dim objConn
Set objConn = CreateObject("ADODB.Connection")

Dim connString
connString = "YOUR ORACLE CONNECTION STRING HERE!"

objConn.Open connString

Dim objRS
Dim strSQL
strSQL = "SELECT * FROM YourTable"

Set objRS = objConn.Execute(strSQL)
If objRS.EOF Then
    ' No Records Returned
Else
    Do
             ' Do what you want with your output
        objRS.MoveNext
    Loop Until objRS.EOF
End If
End With

objRS.Close
Set objRS = Nothing


objConn.Close
Set objConn = Nothing

Use the Oracle Connection Strings page to find your connection string. This is VBScript, for VB you may have to make a few changes such as defining your variable types.

Dim strSQL As String

Here is an example of how you can use Oracle Objects for OLE Automation with Excel:

http://download.oracle.com/docs/cd/B28359_01/win.111/b28378/clients.htm#CIHFAHJJ

Example from the link above:

... 
' Declare variables 
Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraDynaset As OraDynaset 
Dim OraFields As OraFields

' Create the OraSession Object. The argument to CreateObject is the 
' name by which the OraSession object is known to the OLE system. 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 

' Create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) 

' Create the OraDynaset Object. 
Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&) 

' You can now display or manipulate the data in the dynaset. For example: 
Set OraFields = OraDynaset.fields 
OraDynaset.movefirst 
Do While Not OraDynaset.EOF 
    gMsgBox OraFields("ename").Value 
    OraDynaset.movenext 
Loop