从Excel导出到txt，保留特殊的日期格式

Question

我一直在使用代码将数据从 excel 复制到.txt删除任何不需要的字符，以便输入到另一个应用程序：

Public DirRoot As String
Public CasNam As String
Public Sub Export()
    Dim intUnit As Integer
    Dim rngRow As Range
    Dim rngCell As Range
    Dim strText As String
    intUnit = FreeFile
    DirRoot = "C:temp"
    CasNam = "Test"
        Open DirRoot & CasNam & ".txt" For Output As intUnit
    'End If
    With Worksheets("Export").UsedRange
        For Each rngRow In .Rows
            strText = ""
            For Each rngCell In rngRow.Cells
                If Len(rngCell.Value) = 0 Then Exit For
                strText = strText & " " & rngCell.Value
            Next
            Print #intUnit, Trim(strText)
        Next
    End With
    Close intUnit
End Sub

现在我需要在不同的数据集下使用它，其中一列是格式为：yyyymmdd，当我应用上述代码时，我得到的列格式为 dd/mm/yyyy .

有什么方法可以将原始日期格式从 excel 保留到 .txt ？

非常感谢您的帮助！

Answer 1

这是你正在尝试的吗？

'~~> Change this to the column which has dates
Col = 2
For Each rngCell In rngRow.Cells
    If Len(rngCell.Value) = 0 Then Exit For
    If rngCell.Column = Col Then
        strText = strText & " " & Format(rngCell.Value, "yyyymmdd")
    Else
        strText = strText & " " & rngCell.Value
    End If
Next

Answer 2

更改此行

      strText = strText & " " & rngCell.Value

自

      strText = strText & " " &  Format(rngCell.Value,"yyyymmdd")

祝你好运

编辑

请检查一下。 colCounter是标识"已查询列的格式"。

Dim colCounter as Integer
colCounter = 1
With Worksheets("Export").UsedRange
   For Each rngRow In .Rows
      strText = ""
      colCounter = 1
      For Each rngCell In rngRow.Cells
        If Len(rngCell.Value) = 0 Then Exit For
        If colCounter = 2  Then ' change 2 to whatever column you want to format
           strText = strText & " " &  Format(rngCell.Value,"yyyymmdd")
        Else
           strText = strText & " " & rngCell.Value
        End if
        colCounter = colCounter + 1
      Next
      Print #intUnit, Trim(strText)
   Next
End With

Answer 3

我不太清楚哪一部分用日期声明变量，但这个解释可能会有所帮助：

Sub Date_Convert 
dim dtDate_Orig as date
dim dtDate_Convert as date
dim sDate as String

dtDate_Orig = "22/01/2012"
dtDate_Convert = format(dtDate_Orig, "yyyymmdd")
sDate = Cstr(dtDate_Convert)
'Now you can use the string for a filename 
End Sub