EXCEL VBA ADO Recordset Problem

[GandalfTheWhite]

I have a table of ref data which I connect to using ADODB (Code is below)
Where .Fields(9) is Text it is returned, but when it's numeric it is not. I've tried removing null and making them all numeric but still have had no joy. I'm sure this is something to do with Types, but for the life of me I can't get this to work. Can anyone please help. The range is correct.
Thanks
GTW.

Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=\\irf50187\GOVERNMENT BANKING SERVICE PRIMARY\GBS Operations\Customer Signature Panels\Customer Signature Tool\SLT_Data.xls" & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;"";"
strqry = "SELECT * from SigData_Rng" & " " & "WHERE UID=" & UID
Set rsExcel = New ADODB.Recordset
rsExcel.Open strqry, cnn, adOpenStatic, adLockOptimistic
rownum = 10

With rsExcel
.MoveFirst
If Not (.BOF And Not .EOF) Then
Do While Not rsExcel.EOF
Worksheets("Output").Cells(rownum, 7).Value = .Fields(2)
Worksheets("Output").Cells(rownum, 8).Value = .Fields(3)
Worksheets("Output").Cells(rownum, 9).Value = .Fields(4)
Worksheets("Output").Cells(rownum, 10).Value = .Fields(5)
Worksheets("Output").Cells(rownum, 11).Value = .Fields(6)
Worksheets("Output").Cells(rownum, 12).Value = .Fields(7)
Worksheets("Output").Cells(rownum, 13).Value = .Fields(8)
Worksheets("Output").Cells(rownum, 14).Value = .Fields(9)

rownum = rownum + 1
.MoveNext
Loop
End If
End With

 

[doofusboy]

Just a shot in the dark, but have you tried:

Worksheets("Output").Cells(rownum, 14).Value = CStr(.Fields(9))

 

[GandalfTheWhite]

Tried it, still no joy. I think the problem is before it gets tio outputting (as .FIELDS(9) is not even NULL it's nothing).
It's probably(hopefully) something simple.

Thanks anyway.

 

[doofusboy]

Perhaps something like this would work for you then:

            If IsNull(.fields(9).Value) Then
                Worksheets("Output").Cells(rownum, 14).Value = ""
            Else
                Worksheets("Output").Cells(rownum, 14).Value = .Fields(9) 
           End If

 

[xenou]

Excel messes up a lot when there's mixed text and numeric data - if it text data it's usually best to have some text in the first row so excel doesn't start off thinking its numbers and then crash later on. You can verify this by adding ten rows to the beginning of the dataset with text data in the columns with text in them and and numbers in the columns which are all numbers.

You can change this via some registry tweaks - not something I've ever done myself.

http://support.microsoft.com/kb/194124/EN-US/
http://support.microsoft.com/kb/257819

 

[RoryA]

Try:

Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=\\irf50187\GOVERNMENT BANKING SERVICE PRIMARY\GBS Operations\Customer Signature Panels\Customer Signature Tool\SLT_Data.xls" & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"";"

Note: if you restricted your query to just the fields you actually want, you could just use CopyFromRecordset rather than having to loop.