Importing Data from Access
 

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

Importing Data from Access
 

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