PDA

View Full Version : Connecting oracle and excel sheet



sathishesb
12-30-2010, 07:28 AM
:help
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
12-30-2010, 07:44 AM
Take a look at http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForOracleFromOracle

sathishesb
12-30-2010, 09:13 AM
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 :dunno

Bob Phillips
12-30-2010, 10:35 AM
You have to set a reference to Microft ActiveX Data Objects.

sathishesb
12-30-2010, 11:11 AM
Can you explain, how to do it???
plzz

Bob Phillips
12-30-2010, 11:25 AM
Go to Tools>References in the VBIDE and select it from the list.

Bob Phillips
12-30-2010, 11:26 AM
You could even use late-binding



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

sathishesb
12-30-2010, 12:00 PM
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":banghead:

Bob Phillips
12-30-2010, 12:11 PM
Oracle will provide an OLEDB data provider which you need to have installed. Try the Microsoft version, or evene the ODBC connection string.

shrivallabha
12-31-2010, 01:57 AM
You could even use late-binding



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


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
12-31-2010, 03:00 AM
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.