如何阻止VBA粘贴SQL查询结果时错误解析日期格式？

我之前也碰到过好几次这种情况——手动Ctrl+V粘贴TSV完全正常，但用VBA的Paste方法就把日期格式搞混了，本质是Excel自动解析日期的逻辑在手动操作和VBA执行时存在差异：手动粘贴时Excel会更智能地识别TSV里的日期格式，而VBA默认会严格遵循系统区域设置来解析，导致像09/01/2018这种格式被误判为美式的mm/dd/yyyy。下面给你几个可靠的解决办法：

方法1：先把目标区域设为文本格式再粘贴 #

先将粘贴目标区域设置为文本格式，让Excel完全保留原始字符串，之后你可以按需转换为正确的日期格式：

' 先把A1开始的区域设为文本格式
Range("A1").NumberFormat = "@"
' 执行粘贴
ActiveSheet.Paste
' （可选）把日期列转换为dd/mm/yyyy格式的日期值
Columns("D:E").NumberFormat = "dd/mm/yyyy"
' 如果需要将文本日期转为真正的日期值（确保系统区域是dd/mm/yyyy）
For Each cell In Columns("D:E").Cells
    If IsDate(cell.Value) Then
        cell.Value = DateValue(cell.Value)
    End If
Next

方法2：用PasteSpecical指定粘贴格式 #

通过PasteSpecial直接选择文本格式粘贴，彻底跳过Excel的自动日期解析：

' 粘贴值和格式（保留原始TSV的格式）
Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
                        Operation:=xlNone, SkipBlanks:=False, Transpose:=False

' 或者直接粘贴纯文本，之后再手动设置格式
' Range("A1").PasteSpecial Paste:=xlPasteText

方法3：直接导入TSV文件（最稳定） #

如果你的数据来自本地TSV文件，完全可以跳过剪贴板，用QueryTables直接导入，精准指定日期解析规则：

Dim qt As QueryTable
Dim filePath As String
filePath = "C:\你的TSV文件路径.tsv" ' 替换为实际文件路径

' 清除已有查询表（避免重复）
On Error Resume Next
ActiveSheet.QueryTables.Delete
On Error GoTo 0

Set qt = ActiveSheet.QueryTables.Add( _
    Connection:="TEXT;" & filePath, _
    Destination:=Range("A1"))

With qt
    .TextFileParseType = xlDelimited
    .TextFileTabDelimiter = True ' TSV是制表符分隔
    ' 指定每列的解析格式，第四、五列按dd/mm/yyyy解析
    .TextFileColumnDataTypes = Array( _
        xlGeneralFormat, xlGeneralFormat, xlGeneralFormat, _
        xlDMYFormat, xlDMYFormat)
    .Refresh
End With

这个方法直接告诉Excel按日/月/年的规则解析日期列，完全不会出现格式混乱的问题，适合批量处理场景。

额外小提示 #

• 检查系统区域设置：确保Windows的日期格式是dd/mm/yyyy，这会影响VBA的日期解析逻辑；
• 如果已经出现格式混乱的日期，也可以手动拆分字符串转换：

' 修复E列的错误日期
For Each cell In Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row)
    If InStr(cell.Value, "/") > 0 Then
        Dim parts() As String
        parts = Split(cell.Value, "/")
        ' 按dd/mm/yyyy拆分，重构正确日期
        cell.Value = DateSerial(CInt(parts(2)), CInt(parts(1)), CInt(parts(0)))
        cell.NumberFormat = "dd/mm/yyyy"
    End If
Next

内容的提问来源于stack exchange，提问作者ACrazyChemist