从ADODB记录集复制数据时，Excel表丢失数字格式 [英] Excel table loses number formats when data is copied from ADODB recordset

问题描述

我正在使用 CopyFromRecordset 方法从 ADODB 记录集更新excel表。

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.

我以前使用的解决方法是通过 VBA 将列格式化为数字，但这不是一个好的解决方案，因为需要更多的时间报告完成。此外，我必须编写代码以容纳很多表。

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 - 指的是保存与第一个数据相同的格式/数据类型的数据的现有表

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

推荐答案

尝试这样 - 将结果集复制到一个数组中，将其重新设置，然后将其复制到excel中

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

这篇关于从ADODB记录集复制数据时，Excel表丢失数字格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！