Importing Data from Access

[lowestbass]

I am importing data from access (via ADO) into Excel. However I find that all my numerical fields are converted to text fields and I can not do any calculations on them. Is there any way that I can convert them back to numbers. I list my code below.

Thanks in advance for your help

Jeremy.


Sub Outpatients(PName As String)
Dim col As Integer

'Create RecordSet
Set Recordset = New adodb.Recordset
With Recordset
' Filter
Src = "SELECT * FROM GPs_PatientRecords_OP WHERE GPs_PatientRecords_OP.PRACTICE= '" & PName & " '"


'Src = "Select * From QryChapterbyHRG where Chapter = '" & HRGCode & " '"
.Open Src, Connection

' Write the field Names
For col = 0 To Recordset.Fields.Count - 1
Worksheets("Outpatient").Range("A1").Offset(0, col).Value = Recordset.Fields(col).Name
Next
' copy rows
Worksheets("Outpatient").Range("A1").Offset(1, 0).CopyFromRecordset Recordset

End With

'Close the recordset
Set Recordset = Nothing

[K Dales]

One suggestion to start: use a variable name other than Recordset. It is
never good to use a word used by the object model as a variable name in your
code.

Although tedious, you can iterate through the recordset and put the results
in your result range, converting any values as you go; to do so replace your
..CopyFromRecordset with code like this:
Dim Anchor as Range, RSField as ADODB.Field
Set Anchor = Worksheets("Outpatient").Range("A2")
With Recordset
While Not .EOF
For Each RSField in Recordset
Select Case RSField.Name
Case "ProblemField" ' you get the idea, I hope!
Anchor.Value = CInt(RSField.Value)
Case Else
Anchor.Value = RSField.Value
End Select
Set Anchor = Anchor.Cells(1,2)
Next RSField
Set Anchor = Anchor.Offset(1,0).EntireRow.Cells(1,1)
WEnd
End With
--
- K Dales


"lowestbass" wrote:

>
> I am importing data from access (via ADO) into Excel. However I find
> that all my numerical fields are converted to text fields and I can not
> do any calculations on them. Is there any way that I can convert them
> back to numbers. I list my code below.
>
> Thanks in advance for your help
>
> Jeremy.
>
>
> Sub Outpatients(PName As String)
> Dim col As Integer
>
> 'Create RecordSet
> Set Recordset = New adodb.Recordset
> With Recordset
> ' Filter
> Src = "SELECT * FROM GPs_PatientRecords_OP WHERE
> GPs_PatientRecords_OP.PRACTICE= '" & PName & " '"
>
>
> 'Src = "Select * From QryChapterbyHRG where Chapter = '" & HRGCode
> & " '"
> .Open Src, Connection
>
> ' Write the field Names
> For col = 0 To Recordset.Fields.Count - 1
> Worksheets("Outpatient").Range("A1").Offset(0, col).Value =
> Recordset.Fields(col).Name
> Next
> ' copy rows
> Worksheets("Outpatient").Range("A1").Offset(1, 0).CopyFromRecordset
> Recordset
>
> End With
>
> 'Close the recordset
> Set Recordset = Nothing
>
>
> --
> lowestbass
> ------------------------------------------------------------------------
> lowestbass's Profile: http://www.excelforum.com/member.php...o&userid=29255
> View this thread: http://www.excelforum.com/showthread...hreadid=489749
>
>