Connect Excel to Oracle

[Harald Staff]

Hi Radek

Will the menu "Data > Get external data > New database query" do ? Or do you
need a code solution ?

Best wishes Harald

"Radek Obrubko" <tchi...@it.com.pl> skrev i melding
news:anhbcd$1o6b$1...@news2.ipartners.pl...
> Hi,
>
> I'd like to connect Excel 2000 to Oracle 8.1.7.2.1.
> I have installed driver ODBC to Oracle.
> Connection by ODBC works correctly. I tested it.
> and I have to read one table from Oracle to Excel.
> How do it ?
> Can I get any samples ?
>
> Radek
>
>

[Radek Obrubko]

Hi Harald ,

Yes, I need a code solution.

Radek

Użytkownik "Harald Staff" <harald...@nrk.no> napisał w wiadomości
news:emYTfpwaCHA.392@tkmsftngp09...

[Harald Staff]

Hi Radek

Then we are diving into the wonderful world of ADO.

1 in VBE Tools -References, set references to
> Microsoft ActiveX Data Objects 2.1 (or higher)
> Microsoft ADO Ext 2.1 (or higher) for DLL and security

2
Paste this code into a module:

Sub ADOOpenRecordset()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim StrSQL As String

'whatever SQL statement here, like:
StrSQL = "SELECT CUSTID, CUSTNAME, CUSTPHONE" & Chr(10)
StrSQL = StrSQL & "FROM T_CUSTOMERS" & Chr(10)
StrSQL = StrSQL & "WHERE CUSTNAME LIKE 'A%'" & Chr(10)
StrSQL = StrSQL & "ORDER BY CUSTNAME"

cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=OracleServer.world;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

' Open the forward-only,
' read-only recordset
rst.Open StrSQL, _
cnn, adOpenForwardOnly, adLockReadOnly

' dump result into sheet1:
Sheets(1).Cells(2, 1).CopyFromRecordset rst

' Close the recordset
rst.Close

End Sub

OK ? Modify SQL, servername, UID and Pwd and just run it.

See (beware linebreaks)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/
daotoadoupdate_topic5.asp

and topic1 to 8 in the same url on more on recordsets and ADO methods.

See also
http://www.able-consulting.com/tech.htm
for connection strings and tech detail.

Finally, new versions of the objects and drivers (as selected in References
when we started) are called MDAC, free downloadable from
http://www.microsoft.com/data/
You should use at least MDAC 2.1, your end users need the same as you or
higher.

HTH. Best wishes Harald

"Radek Obrubko" <tchi...@it.com.pl> wrote in message
news:anjdsg$30lm$1...@news2.ipartners.pl...