Fetch data from database and put it in exel sheet by macro

[PRIYARANJAN]

Hi,
I have a requirement like below:
There is a database with following details:
username:scott,
password:tiger,
host name:xx.yy.zz.ppp,
port:abcd,
SID:zpz.

There is a employee table in the database.So we have to fetch the data from the table and put it into the xls sheet.

Here is the code:

Sub AnalyzeDBATables()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim col As Integer
Dim row As Integer

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Open ( _
"User ID=scott" & _
";Password=tiger" & _
";Data Source= xxxxx" & _
";Provider=xxxx")

rs.Open "select * from employee", cn
col = 0

' First Row: names of columns
Do While col < rs.Fields.Count
Cells(1, col + 1) = rs.Fields(col).Name
col = col + 1
Loop

' Now actual data as fetched from select statement

row = 1
Do While Not rs.EOF
row = row + 1
col = 0

Do While col < rs.Fields.Count
Cells(row, col + 1) = rs.Fields(col).Value
col = col + 1
Loop
rs.MoveNext
Loop

End Sub



_______

I am using MAC OS and exel 2004.Here in the reference microsoft active x data object are not coming by defaults as in case of XP.

So while running the scripts the error is coming

Compilation Error:
User defined type not defined.

During debugging error is coming at :Set cn = New ADODB.Connection.



so please help me to sort out this..



Thanks &Regards,
priyaranjan

 

[Jibanendu]

HI,

I am also facing the same issue, could anyone please guide me through to solve the problem and help us.

Thanx in advance,
Jibanendu