CopyFromRecordSet文本为数字

2024-05-19 • 问答

我正在尝试使用recordset方法复制表，但是当我粘贴Excel时，数字数据变成了文本值。我该如何解决？

Function runReport(nome_relatorio As String)

SQL = sqlStatement(nome_relatorio)
titulo = nome_relatorio
Set wb = ThisWorkbook

If Not SQL = "" Then
    Set query_res = Connect_To_SQLServer_Obj(svName(),dbName(),SQL)

    If wb.Worksheets.Count > 10 Then
        MsgBox ("O limite de relatórios foi atingido. Por favor,exclua um relatório gerado para prosseguir.")
    Else
        Set ws = wb.Sheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
        ws.Name = wb.Worksheets.Count - 2
        Set rng = ws.Range("B5")
        For i = 0 To query_res.Fields.Count - 1
            rng.Cells(1,i + 1).Value = query_res.Fields(i).Name
        Next i
        rng.Offset(1,0).CopyFromRecordset query_res
    End If

    Call design
    Call Close_Objects

Else
    Exit Function
End If

End Function

awfvfhpHE 回答：CopyFromRecordSet文本为数字

如果您试图在字段为数字的情况下强制将Excel中的列设置为数字格式，则可以在记录集中查看字段的.Type属性：

    For i= 0 To query_res.Fields.Count - 1
        With objXLSheet
            .Cells(1,i+ 1) = query_res.Fields(i).name
            If query_res.Fields(i).Type = 4 Then    '   numeric field
                .Columns(i+ 1).NumberFormat = "0"
            Else
                .Columns(i+ 1).NumberFormat = "@"
            End If
        End With
    Next i

此致

excel recordset vba

本文链接：https://www.f2er.com/2419839.html

CopyFromRecordSet文本为数字

awfvfhpHE 回答：CopyFromRecordSet文本为数字

大家都在问