如何在VBA批量XLS转CSV时添加日期格式设置

需求概述 #

已有可批量将指定文件夹中XLS文件转为CSV的VBA脚本，需要新增功能：将每个文件的H列日期格式统一设置为MMM-YY（例如Jan-24、Feb-24），同时保留原脚本的用户选择源文件夹和目标文件夹的交互逻辑，无需额外用户输入。

原修改尝试的问题 #

你尝试先批量转CSV，再重新打开CSV文件修改H列格式，但CSV是纯文本文件，不存储任何格式信息——在Excel中设置的单元格格式无法保存到CSV里，重新打开后格式会丢失，因此这种方法无效。

正确解决方案 #

应该在打开原XLS文件后、保存为CSV之前，直接处理H列的日期格式，确保保存为CSV时，H列的日期已经是MMM-YY格式的文本内容。

修改后的完整VBA代码 #

Sub WorkbooksSaveAsCsvToFolder()
    Dim xObjWB As Workbook
    Dim xObjWS As Worksheet
    Dim xStrEFPath As String
    Dim xStrEFFile As String
    Dim xObjFD As FileDialog
    Dim xObjSFD As FileDialog
    Dim xStrSPath As String
    Dim xStrCSVFName As String
    Dim xS As String

    ' 关闭Excel的不必要功能，提升运行速度
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False

    On Error Resume Next

    ' 选择源文件夹（存放XLS文件）
    Set xObjFD = Application.FileDialog(msoFileDialogFolderPicker)
    xObjFD.AllowMultiSelect = False
    xObjFD.Title = "选择存放Excel文件的文件夹"
    If xObjFD.Show <> -1 Then Exit Sub
    xStrEFPath = xObjFD.SelectedItems(1) & "\"

    ' 选择目标文件夹（存放CSV文件）
    Set xObjSFD = Application.FileDialog(msoFileDialogFolderPicker)
    xObjSFD.AllowMultiSelect = False
    xObjSFD.Title = "选择存放CSV文件的文件夹"
    If xObjSFD.Show <> -1 Then Exit Sub
    xStrSPath = xObjSFD.SelectedItems(1) & "\"

    ' 遍历源文件夹中的所有XLS/XLSX文件
    xStrEFFile = Dir(xStrEFPath & "*.xls*")
    Do While xStrEFFile <> ""
        xS = xStrEFPath & xStrEFFile
        
        ' 打开原Excel文件
        Set xObjWB = Application.Workbooks.Open(xS)
        
        ' 处理每个工作表的H列（如果文件只有一个工作表，可直接用xObjWB.Worksheets(1)）
        For Each xObjWS In xObjWB.Worksheets
            ' 设置H列的日期显示格式为MMM-YY
            xObjWS.Columns("H:H").NumberFormat = "mmm-yy"
            ' 可选：将H列转为文本，确保CSV中是纯文本格式的日期（避免部分场景下Excel自动转换）
            ' xObjWS.Columns("H:H").Value = xObjWS.Columns("H:H").Text
        Next xObjWS
        
        ' 生成CSV文件名并保存
        xStrCSVFName = xStrSPath & Left(xStrEFFile, InStr(1, xStrEFFile, ".") - 1) & ".csv"
        xObjWB.SaveAs Filename:=xStrCSVFName, FileFormat:=xlCSV
        xObjWB.Close SaveChanges:=False
        
        xStrEFFile = Dir
    Loop

    ' 恢复Excel默认设置
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

关键修改说明 #

1. 在原XLS文件中处理格式：打开XLS文件后，直接遍历所有工作表（或指定单个工作表），设置H列的NumberFormat为mmm-yy，确保保存为CSV时，Excel会按照该格式输出文本内容。
2. 可选的文本转换：如果担心CSV文件在其他程序中被自动识别为日期序列号，可以取消注释代码中的xObjWS.Columns("H:H").Value = xObjWS.Columns("H:H").Text，将H列直接转为文本格式的日期字符串。
3. 保留原有交互逻辑：完全保留了原脚本的源文件夹和目标文件夹选择功能，没有新增额外用户输入步骤。

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