Returning a Value into Excel from SQL ADO connection

[Stopher]

Hi All first post.

I am currently having a problem returning a value into a cell in excel
2000

I understand the ADO connection, and have checked my sqlSTR in query
analyser.

I use the following to dump to a cell.

Set rcset = New ADODB.Recordset
rcset.Open sqlstr, dbmain, adOpenKeyset, adLockOptimistic
Sheets("Sizing").Range("b19").CopyFromRecordset rcset

What I have used this to dump the whole record set out, but the sqlstr
I am using only has a specific value in it. I am currently getting
#value in my cell, and am wondering if the value coming out of the ADO
dump is actually text.

Is there anyway to specify the value coming out of the rcset is a value
and not text?

[Bob Phillips]

I would doubt that getting a text value would show as #VALUE in a cell,. it
would just show the value.

Why don't you add some logic in the code to step through the recordset
rather than copy it, and then debug it

If Not oRS.EOF Then
For i = 0 To oRS.fields.Count - 1
Debug.Print oRS.fields.Item(i).Value
Next i

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stopher" <[email protected]> wrote in message
news:[email protected]...
> Hi All first post.
>
> I am currently having a problem returning a value into a cell in excel
> 2000
>
> I understand the ADO connection, and have checked my sqlSTR in query
> analyser.
>
> I use the following to dump to a cell.
>
> Set rcset = New ADODB.Recordset
> rcset.Open sqlstr, dbmain, adOpenKeyset, adLockOptimistic
> Sheets("Sizing").Range("b19").CopyFromRecordset rcset
>
> What I have used this to dump the whole record set out, but the sqlstr
> I am using only has a specific value in it. I am currently getting
> #value in my cell, and am wondering if the value coming out of the ADO
> dump is actually text.
>
> Is there anyway to specify the value coming out of the rcset is a value
> and not text?
>

[Stopher]

I sorted it out. Instead of referencing a cell in the CopyRecordset I
recalled the value for the function, so-

Set rcset = New ADODB.Recordset
rcset.Open sqlstr, dbmain, adOpenKeyset, adLockOptimistic
[INSERT FUNCTION HERE].CopyFromRecordset rcset