宏将日期列导出为 csv 作为数值而不是 dd/mm/yyyy

Question

以下代码实现了我的要求，只是将日期[cell B2]导出为五位数字值而不是 dd/mm/yyyy。

我如何修改代码以强制B2导出dd/mm/yyyy？

Sub Export_CSV()

    Dim MyPath As String
    Dim MyFileName As String
    Dim WB1 As Workbook, WB2 As Workbook



    Set WB1 = ActiveWorkbook
    ActiveWorkbook.ActiveSheet.UsedRange.Copy

    Set WB2 = Application.Workbooks.Add(1)
    WB2.Sheets(1).Range("A1").PasteSpecial xlPasteValues



    MyFileName = "XeroImport"
    FullPath = WB1.Path & "\" & MyFileName

    Application.DisplayAlerts = False
    If MsgBox("Data copied to " & WB1.Path & "\" & MyFileName & vbCrLf & _
    "Warning: Files in directory with same name will be overwritten!!", vbQuestion + vbYesNo) <> vbYes Then
        Exit Sub
    End If

    If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
    With WB2
        .SaveAs Filename:=FullPath, FileFormat:=xlCSV, CreateBackup:=False
        .Close True
    End With
    Application.DisplayAlerts = True
End Sub

提前感谢您的帮助。

Answer 1

如果B2显示：

在 之前.SaveAs，添加以下几行：

Dim s As String
s = Range("B2").Text
Range("B2").Value = "'" & Format(s, "dd/mm/yyyy")