通过VBA导入制表符分隔的文本文件到excel时，日期类型数据从"dd/mm"更改为"mm/dd"类型 [英] Date type data gets changed from “dd/mm” to “mm/dd” type while importing tab delimited text file to excel Through VBA

问题描述

在阅读SO特色文章时

这完全是出于学术目的，我换了系，人们对以前的工作很满意，没有人愿意修改古老的代码.但是我的直觉是解决方案必须是非常简单的单行调整，并让我们说:哦，好几年我们怎么能错过这个愚蠢的东西呢?"

Edit2 :关于Cells.text如何在各种日期和日期下工作的实验还很少.区域设置("DMY"和"MDY").在excel文件中输入的样本日期很少，并且执行了一个简单的循环以将B列单元格文本放入D列.
我得出的结论是无论VBA Cells.Text的区域设置是什么，其解释日期都以美国格式(mdy)显示.当将其放在另一个单元格上时(在区域设置为"DMY"且月份和日期可能可互换，即日期< 13的情况下)，它将被互换.我仍然欢迎您对该主题有其他更好的理解.

解决方案

VBA采用美国区域设置.如果您的设置使用 dd/mm 格式，请在 Opentext 命令中添加 Local:= True .

第二部分，当您将 XDate 放入单元格时，您忘记了使用 CDate .

While going through SO featured post VBA, Date formatting issue I think the problem could not be simply brushed aside by saying format issue. In my workplace we are living with similar type of problem for around last 12/13 years. However we worked around with some crude workaround approach. But the fundamental problem is still very much intact.

Since the real data we are processing are technical in nature and could not be divulged and contains more than 6000 rows and 50 columns. Also actual data processing in consequence of importing the data consists of pages VBA coding. I reproduced the problem with small factitious sample data only.

When a tab delimited text file was imported manually, the dates in a single column (also consists of Numbers Blanks & texts in the same column) gets imported normal in date format ("dd/MM/yyyy"). Macro was also recorded during the manual operation

But when the same task was performed through VBA.

1. The dates with day value more than 12 appears as text type.

2. The dates with day value less than 13 appears as date type but 01/04/2017 (i.e. 1-Apr-2017) gets changed to 04/0!/2017 (i.e. 4-jan-2017) and so on.

   Fname = "C:\Users\user\Desktop\SO Sample.TXT"
   
   Workbooks.OpenText Filename:=Fname, Origin:=xlMSDOS, _
       StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
       ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
       Space:=True, Other:=True, OtherChar:="*", FieldInfo:=Array(Array(1, 1), _
       Array(2, 1), Array(3, 1))
   

My first question is what is the catch between manual operation and VBA Coding?

1. For satisfaction, coding was replaced and tried with recorded macro
2. VBA macro tested for multiple option with File Origin:=xlMSDOS, Xl Windows , 437 etc.
3. Regional setting of all the machines of that particular department is in same date format "dd/MM/yyyy" only.
4. Since this is an age old process, Macros/VBA Codes are tried in Office 2007 and old versions only.
5. Normally all formats are kept General.

Now the age old workaround we are still working modified for sample data

Sub Macro1()
Fname = "C:\Users\user\Desktop\SO Sample.TXT"

    Workbooks.OpenText Filename:=Fname, Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
        Space:=True, Other:=True, OtherChar:="*", FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1))

    SrcName = ActiveWorkbook.Name
    Range("A1:C40").Select
    Selection.Copy
    Windows(ThisWorkbook.Name).Activate
    Sheet1.Range("A5").Select
    ActiveSheet.Paste
    Windows(SrcName).Activate
    Range("A1:A1").Select
    Selection.Copy                     ' To release vast clipboard date
    ActiveWorkbook.Close False
    ThisWorkbook.Activate


 For X = 5 To 40
 Xdate = Cells(X, 2).Text
 Cells(X, 5).Value = Xdate
 Ydate = Cells(X, 5).Value
    If IsDate(Xdate) And IsDate(Ydate) Then
    Zdate = CDate(Ydate)
    'If DateDiff("d", Ydate, #1/1/1900#) > 0 Then Zdate = ""     'to filter out numeric data interpreted as date/time
    Else
    Zdate = ""
    End If
 Cells(X, 6).Value = Zdate
 Next

'Next are the sections of processing the data beased on the non empty date values found in column 6.
End Sub

Here when Cells(X, 2).Text was put into Cells(X, 5)

1. The dates with day value more than 12 appears remains text type in column 5.

2. The dates with day value less than 13 appears as date type but 01/04/2017 (i.e. 1-Apr-2017) which got changed to 04/0!/2017 (i.e. 4-jan-2017) in Col 2 Again get changed to 01/04/2017 (i.e. 1-Apr-2017) in Col 5

Now my second question is, what is happening when we put Cells(X, 2).Text into Cells(X, 5) ?

This is strictly for academic interest, I changed department, people are happy with old work around and no one want to tinker with age old code. But my hunch the solution must be a very simple single line tweak and made us utter "Oh How can we missed this silly thing for years".

Edit2: Further little experiment was done on how Cells.text works with various dates & regional settings (both "DMY" & "MDY"). Few sample dates are entered in a excel file and a simple loop executed to put column B cells text to column D.
I come to the conclusion that irrespective of regional setting VBA Cells.Text interpret date as displayed in USA format (mdy) . When the same is put on another cell (in case, Regional setting is "DMY" and Month and Date is possibly interchangeable i.e. date < 13), it gets interchanged. I still welcome any other better understanding in the subject.

解决方案

VBA assumes US regional settings. If your settings use dd/mm format, add Local:=True to the Opentext command.

Re the second part, you forgot to use CDate when putting XDate in the cell.

这篇关于通过VBA导入制表符分隔的文本文件到excel时，日期类型数据从"dd/mm"更改为"mm/dd"类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！