Excel table loses number formats when data is copied from ADODB recordset

Question

I'm updating an excel table from an ADODB recordset using the CopyFromRecordset method.

After the update, the numbers show up as dates wherever there are number columns.

The workaround I used until now is to format the columns back to numbers through VBA, but it's not a good solution as takes more time for the report to complete. Also I have to write code to accommodate a lot of tables.

Is there a quick fix? Any help is greatly appreciated.

'Delete old data and copy the recordset to the table
Me.ListObjects(tblName).DataBodyRange.ClearContents
Me.Range(tblName).CopyFromRecordset rst

tblName - refers to an existing table that held data of the same format/datatype as rst data

Answer 1

I know this is a late answer, but I was encountering this same error. I think I've found a workaround.

It seems Excel expects the range to be the top-left cell rather than a range of cells. So just modify your statement to Range(tblName).Cells(1,1).CopyFromRecordset rst

'Delete old data and copy the recordset to the table
Me.ListObjects(tblName).DataBodyRange.ClearContents
Me.Range(tblName).Cells(1,1).CopyFromRecordset rst

There also seems to be a requirement that the target sheet be active, so you might have to ensure the sheet is active first, and then change back to the previously active sheet. This might have been fixed in later version of Excel.

Answer 2

Try this - this copies resultset into a array, transposes it and then copies it into excel

Dim rs As New ADODB.Recordset

Dim targetRange As Excel.Range

Dim vDat As Variant

' Set rs

' Set targetRange   

rs.MoveFirst

vDat = Transpose(rs.GetRows)

targetRange.Value = vDat


Function Transpose(v As Variant) As Variant
    Dim X As Long, Y As Long
    Dim tempArray As Variant

    ReDim tempArray(LBound(v, 2) To UBound(v, 2), LBound(v, 1) To UBound(v, 1))
    For X = LBound(v, 2) To UBound(v, 2)
        For Y = LBound(v, 1) To UBound(v, 1)
            tempArray(X, Y) = v(Y, X)
        Next Y
    Next X

    Transpose = tempArray

End Function