vba - 无法将 CopyFromRecordset 结果设置为日期或数字

Question

我正在尝试使用 Range.CopyFromRecordset 将数据集从 Access 2010 导入 Excel .查询中有一些字段，其中一些值已转换为日期或数字，例如:

iif(isdate([Install Date]),cdate([Install Date]),[Install Date]) 
AS [Install Date (cast)],
iif(isnumeric([Feature Number]),cdbl([Feature Number]),[Feature Number]) 
AS [Feature Number (cast)]

这是我用来生成包含这些数据的工作表的代码:

Private Sub createRSSheet(db As DAO.Database, rsName As String, sheetName As String)

Dim rs As DAO.Recordset
Dim sht As Worksheet
Dim fldCtr As Long
Dim columnRng As Range


'get recordset
Set rs = db.OpenRecordset(rsName)

'add worksheet
Set sht = bk.Sheets.Add
sht.Name = sheetName

'import recordset to worksheet
sht.Range("a2").CopyFromRecordset rs

'iterate through fields
For fldCtr = 0 To rs.Fields.Count - 1
    sht.Cells(1, fldCtr + 1) = rs.Fields(fldCtr).Name

    'check if field is a date field
    If InStr(UCase(rs.Fields(fldCtr).Name), "DATE") > 0 Then

        'format column as date
        Set columnRng = sht.Cells(1, fldCtr + 1)
        columnRng.EntireColumn.NumberFormat = "mm/dd/yyyy"

    'check if field is number cast
    ElseIf InStr(UCase(rs.Fields(fldCtr).Name), "(CAST)") > 0 Then

        'format column as number
        Set columnRng = sht.Cells(1, fldCtr + 1)
        columnRng.EntireColumn.NumberFormat = 0
    End If
Next

End Sub

但是，当这个子程序运行时，数字列仍然被格式化为文本，日期列仍然被视为字符串——当我应用自动过滤器时，过滤器会列出每个单独的日期，而不是按月和年对它们进行分组。

我怎样才能让这些列以所需的格式结束？

Answer 1

解决了:

'check if field is a date field
If InStr(UCase(rs.Fields(fldCtr).Name), "DATE") > 0 Then

    'format column as date
    Set columnRng = sht.Cells(1, fldCtr + 1)
    columnRng.EntireColumn.NumberFormat = "mm/dd/yyyy"

    'refresh column values with new formatting
    columnrng.EntireColumn.Value=columnrng.EntireColumn.Value


'check if field is number cast
ElseIf InStr(UCase(rs.Fields(fldCtr).Name), "(CAST)") > 0 Then

    'format column as number
    Set columnRng = sht.Cells(1, fldCtr + 1)
    columnRng.EntireColumn.NumberFormat = 0

    'refresh column values with new formatting
    columnrng.EntireColumn.Value=columnrng.EntireColumn.Value
End If