If you have to write an ado db recordset into an Excel worksheet, you have two basic options.

Either you write a recordset object using Excel API function copyFromRecordset or you write the data implementing an own method looping through all records.

However calling the function copyFromRecordset is pretty fast compared to looping through all records, it opposes the risk of incorrectly formatted data in a target worksheet. Root cause for this is the often failing attempt of ADO to guess a data type of a column in a source worksheet based on the first few records.

You can switch some optional parameter in a connection string called IMAX in order to have ADO take all records of a field in a recordset into account before guessing a field type, but this will lead only to incredible bad performance.

On top of that ADO is still likely to fail in guessing a field type of an Excel column even, if you use IMAX.

Excel is not a database and therefore it is not possible to declare a datatype for a column. Out of own bad experiences with different formats causing SQL queries using Joins to fail or deliver invalid results I strongly recommend to use copyFromRecordset only in a very controlled environment or rarely, if at all.

If you think about trying to set the data type of a field before writing its records into an Excel sheet or before querying data in a source sheet, forget it. This won't work either, since ADO will not allow you to set the correct data type explicitly in Excel although its API delivers functions for this purpose.

Instead you could use the following method implemented in Excel VBA.

Source Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
' @Author - Alexander Bolte
' @ChangeDate - 2014-12-31
' @Description - writes a provided ado db recordset into a provided excel worksheet.
' @Param rs - an ado db recordset holding data.
' @Param trgWs - an Excel worksheet.
' @Param row - a Long providing the start row for writing data into a target worksheet.
' @Returns a long providing the number of rows written into a target worksheet.
' If an error occurs, minus one is returned.
Private Function writeRecordset(ByRef rs As Object, ByRef trgWs As Worksheet, ByVal row As Long) As Long
    Dim f As Integer
    
    On Error GoTo errHandle:
    
    If Not (rs.EOF And rs.BOF) Then
        While Not (rs.EOF)
            For f = 0 To rs.fields.count - 1
                ' write a value into a cell the target worksheet.
                ' rs(f) returns the value of a current field.
                trgWs.Cells(row, f + 1).value = rs(f)
            Next
            row = row + 1
            rs.moveNext
        Wend
    End If
    
errHandle:
    If Err.Number <> 0 Then
        Err.Clear
        row = -1
    End If
    
    writeRecordset = row
End Function

Referenced API

http://msdn.microsoft.com/en-us/library/windows/desktop/ms675532%28v=vs.85%29.aspx

http://msdn.microsoft.com/en-us/library/windows/desktop/ms677497%28v=vs.85%29.aspx