Connecting oracle and excel sheet

[sathishesb]

Guys, help me :

How to connect excel and oracle using vba code?

i have database name, username and password plzzz help me with connection codes...


awaiting for your reply guys!!!

[Bob Phillips]

Take a look at http://www.carlprothman.net/Default....acleFromOracle

[sathishesb]

I have tried the below code

Sub connection()
Dim ProdConnectString As String
Dim conn As New ADODB.connection
ProdConnectString = "provider=site address;user id=xxxx;password=yyyyy;data source = aaaaaaaa"
conn.ConnectionString = ProdConnectString
conn.CursorLocation = adUseClient
conn.Open
End Sub

But i am getting an error "user-defined type not defined" in the highlited area..
Plzzz help me to proceed

[Bob Phillips]

You have to set a reference to Microft ActiveX Data Objects.

[sathishesb]

Can you explain, how to do it???
plzz

[Bob Phillips]

Go to Tools>References in the VBIDE and select it from the list.

[Bob Phillips]

You could even use late-binding

[vba]

Sub connection()
Dim ProdConnectString As String
Dim conn As Object

Set conn = CreateObject("ADODB.connection")
ProdConnectString = "provider=site address;user id=xxxx;password=yyyyy;data source = aaaaaaaa"
conn.ConnectionString = ProdConnectString
conn.CursorLocation = adUseClient
conn.Open
End Sub
[/vba]

[sathishesb]

I have set a reference to microsft activex data objects..
But i am getting an error "
Run time error 3706. Provider can not be found. It may not be pro perly installed"

[Bob Phillips]

Oracle will provide an OLEDB data provider which you need to have installed. Try the Microsoft version, or evene the ODBC connection string.

[shrivallabha]

You could even use late-binding

[vba]

Sub connection()
Dim ProdConnectString As String
Dim conn As Object

Set conn = CreateObject("ADODB.connection")
ProdConnectString = "provider=site address;user id=xxxx;password=yyyyy;data source = aaaaaaaa"
conn.ConnectionString = ProdConnectString
conn.CursorLocation = adUseClient
conn.Open
End Sub
[/vba]

The meaning of Early Binding & Late Binding is dawning slowly on me.

For Early Binding, the user must have the Objects declared and available in the library. Advantage is: he will get to see all the options available.

For Late Binding, the above is not necessary. The syntax has to be correct or it will result in some kind of Run Time Error. It has this obvious disadvantage. So how does one take a call on Binding option?

[Bob Phillips]

I don't think that will fix this problem, it can't make a connection, eraly or late, because the data provider is not found.