Hello all,

I tried to use solution of "agarwaldvk" of the following solved thread with the number 40986
to get data in MS-EXCEL with using ORACLE as database.


The given code:


Sub GetData()

Dim SQL_String As String
Dim dbConnectStr As String
Set con = New ADODB.Connection
Set recset = New ADODB.Recordset
Dim recordCount As Long
dbConnectStr = "Provider=msdaora;Data Source=" & "Oracle_Database_Name;"


User Id=userId" & "; Password=" & "password"
'The statement above has been commented out. I use the statement below to prompt the user for the userId and password - which is what I prefer!

con.ConnectionString = dbConnectStr
con.Properties("Prompt") = adPromptAlways
con.Open dbConnectStr 'ConnectionString

'This is an example SQL code that you might want to run
'Select * From MyTable

'SQL_String = "Select count(*) from adm_user"
recset.Open SQL_String, con
recset.MoveLast
recordCount = recset.recordCount
recset.MoveFirst
Do While Not recset.EOF = True
'Have a loop here to go through all the fields
recset.MoveNext
Loop
recset.Close
End Sub



I realized the steps:

(1) Successfully installation of 18 XE Express
(2) Successfully connect with "User DSN" (ODBC-Datasource-Adminstration/Oracle ODBC Driver Connect): Input of "Service Name" / "User Name" / "Password"


My question:

No. 1:

Concerning the information of agarwaldvk >>> Data Source=" & "Oracle_Database_Name;"
What is the substitution of "Oracle_Database_Name". Is this the first string in my tnsnames.ora: XE?


XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RENEDI-HP)(PORT = 1521))

.
.
.


No. 2:

Using EXCEL 2016 and the link "Microsoft Active Data Objects 6.1 Libary" (msado 15.dll), I get an error in the fouth line:


Sub Option4()

Dim SQL_String As String
Dim dbConnectStr As String
Set con = New ADODB.Connection < Information of EXCEL: "Variable is not defined." What's to do?

Thanks for your infromation

Regards

JuMathias