Populating cells from Oracle in Excel 2

[cranebill]

I found a snippet of code that will pull data from oracle and populate cells supposedly... however I cannot get it to work here is the code

'Defining variables
Dim cnOra As ADODB.Connection
Dim rsOra As ADODB.Recordset
Dim db_name As String
Dim UserName As String
Dim Password As String

Set cnOra = New ADODB.Connection
Set rsOra = New ADODB.Recordset

db_name = "DB"
UserName = "myname"
Password = "mypw"

'Making an ODBC connection according to ADO
cnOra.Open "DSN=" + db_name + ";UID=" + UserName + ";PWD=" _
& Password + ";"
rsOra.CursorLocation = adUseServer

'Running a query
rsOra.Open "select * from works.registered", cnOra, adOpenForwardOnly

'Passing on data from the recordset to a variable or cell.
'Notice that the column name or alias is used to address
'data in the recordset.
While Not rsOra.EOF
Worksheets("Sheet1").Range("B1").End(xlDown).Offset(1, 0) _
= rsOra![REGISTERED]
rsOra.MoveNext
Wend


'Forgetting to close your connection will sometimes result in
'Dr.Watsons
rsOra.Close
cnOra.Close
Set rsOra = Nothing

I have a sheet that is already formatted in the way of the report I need, so I am just trying to fill in the columns. There are multiple rows in the query so i just need to pick a spot and fill down til EOF. Any Ideas?

I have got it to work with one cell, just not a column so I know I am connecting to DB.

 

[PHV]

Typed, untested:

...
'Running a query
rsOra.Open "select REGISTERED from works.registered", cnOra, adOpenForwardOnly
'Passing on data from the recordset
Worksheets("Sheet1").Range("B1").End(xlDown).Offset(1, 0).CopyFromRecordset rsOra
rsOra.Close
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

I query Oracle every day.

Here's my Connect

    sServer = "DWPROD"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=;" & _
               "Pwd="

check

http://www.carlprothman.net/Default.aspx?tabid=81

Also check out the CopyFromRecordset method for dumping the data to your sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cranebill]

Tried using PHV's code and I get an application error (I know its untested), tried playing with it a bit and still could not get it to function.

Skip, I am not having an issue with connecting to the database, it seems now that using the code above it populates all data in the one cell and the last thing I see is the last value in the dataset.

 

[cranebill]

Ok something interesting... I put in PHV's code again and now it compiles and works, however it is not populating at B1, rather it starts at B7

B1 is a blank cell and B7 is the first cell after a heading, so I changed the cell reference and it is now populating under other headings in the same column...

Any ideas?

 

[SkipVought]

"...I changed the cell reference ..."

"Any ideas?"

Not without a crystal ball.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cranebill]

I was going to call Miss Cleo however I heard she was in Jail lol

 

[SkipVought]

ie, it would help an awful lot, to see the code, including what you changed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cranebill]

This is what I have

'Clear Contents
Range("B7:B10").Select
Selection.ClearContents

'Making an ODBC connection according to ADO
cnOra.Open "DSN=" + db_name + ";UID=" + UserName + ";PWD=" _
& Password + ";"
rsOra.CursorLocation = adUseServer

'Running a query
rsOra.Open "select REGISTERED from works.worcle_registered", cnOra, adOpenForwardOnly

'Passing on data from the recordset to a variable or cell.
'Notice that the column name or alias is used to address
'data in the recordset.

Worksheets("Sheet1").Range("B1").End(xlDown).Offset(1, 0).CopyFromRecordset rsOra


'Forgetting to close your connection will sometimes result in
'Dr.Watsons
rsOra.Close
cnOra.Close
Set rsOra = Nothing
End Sub

I thought about changing sheet1 to sheet 2 then I could reference across the 2, however when I change to sheet 2 than I get a run time error, Application-defined or object-defined error

 

[SkipVought]

ONE CELL RANGE...

Worksheets("Sheet1").Range("B1").CopyFromRecordset rsOra

and...

no need to use the Select Method...

'Clear Contents
Range("B7:B10").ClearContents

Actually, I'd recommend instead...
[

'Clear Contents
Range("B7").CurrentRegion.ClearContents

especially if the resultset returns an unknown number of rows.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Perhaps this ?

...
'Running a query
rsOra.Open "select REGISTERED from works.worcle_registered", cnOra, adOpenForwardOnly
'Passing on data from the recordset to a variable or cell.
Range("B7:B10").ClearContents
Range("B7").CopyFromRecordset rsOra
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cranebill]

The reason I put the clear contents in there was only because if I hit the button that drives this again instead of over writing the cell, it places the data in the cells directly below the ones needed.

PHV this worked like a charm... even populates the correct cells, changed cell locations to be sure and changes cells accordingly...

Thanks a ton.. star for you both