如何修复Excel VBA导出TXT文件时的日期格式与单元格格式问题？

问题根源 #

你的代码直接将单元格区域赋值给数组c00时，数组获取的是单元格的原始值（日期会被转为系统默认格式的字符串、数值是精确计算值），而非单元格设置格式后的显示文本，因此导出结果不符合预期。

解决方案 #

遍历单元格读取DisplayText属性（这是Excel界面中实际显示的内容），替代直接读取单元格值的方式。同时将重复的导出逻辑封装为子过程，减少代码冗余。

修改后的完整代码 #

Sub PricingZVOLtoNotepad()
    Dim LastRowZVOL As Long
    LastRowZVOL = Worksheets("LSMW ZVOL MATERIAL").Range("A" & Rows.Count).End(xlUp).Row
    
    ' 创建9F End文件
    ExportRangeToTxt "C:\AAA\ZVOL_9F_End.txt", _
        Worksheets("LSMW ZVOL MATERIAL").Range("V3:AA" & LastRowZVOL), _
        Array("F:F") ' 需要设置日期格式的列
    
    ' 创建LSMW文件
    ExportRangeToTxt "C:\AAA\ZVOL 9F LSMW.txt", _
        Worksheets("LSMW ZVOL MATERIAL").Range("A3:T" & LastRowZVOL), _
        Array("C:D") ' 需要设置日期格式的列
    
    MsgBox "ZVOL files have been created."
End Sub

' 封装导出逻辑的子过程
Private Sub ExportRangeToTxt(txtPath As String, sourceRng As Range, dateColumns As Variant)
    Dim transferWs As Worksheet
    Dim FF As Integer
    Dim lngRow As Long, lngCol As Long
    Dim rowText As String
    
    ' 创建临时工作表
    Set transferWs = ThisWorkbook.Worksheets.Add
    transferWs.Name = "NotepadTransfer"
    
    ' 复制值和格式到临时表
    sourceRng.Copy
    transferWs.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    
    ' 设置指定列的日期格式
    If Not IsEmpty(dateColumns) Then
        Dim col As Variant
        For Each col In dateColumns
            transferWs.Range(col).NumberFormat = "DDMMYYYY"
        Next col
    End If
    
    ' 导出到TXT
    FF = FreeFile
    Open txtPath For Output As #FF
        For lngRow = 1 To transferWs.Cells(transferWs.Rows.Count, 1).End(xlUp).Row
            rowText = ""
            For lngCol = 1 To sourceRng.Columns.Count
                ' 读取单元格显示文本，而非原始值
                rowText = rowText & transferWs.Cells(lngRow, lngCol).DisplayText & vbTab
            Next lngCol
            ' 移除最后一个多余的Tab
            rowText = Left(rowText, Len(rowText) - 1)
            Print #FF, rowText
        Next lngRow
    Close #FF
    
    ' 删除临时工作表
    Application.DisplayAlerts = False
    transferWs.Delete
    Application.DisplayAlerts = True
End Sub

关键修改点 #

• 新增ExportRangeToTxt子过程，封装重复的导出逻辑，简化主代码
• 使用DisplayText属性读取单元格显示内容，确保日期格式、小数位数与Excel界面完全一致
• 处理日期列格式设置，确保临时表中日期显示为DDMMYYYY后再导出
• 移除每行末尾多余的Tab符号，避免导出的TXT包含无效分隔符

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