VBA正在将我的日期改为mm / dd / yyyy hh：mm导入时 [英] VBA is changing my dates to mm/dd/yyyy hh:mm when importing

问题描述

所以我从计算机生成的CSV文件导入日期。在CSV文件中，日期为dd / mm / yyyy hh：mm格式。但是当我使用VBA导入它时，VBA将其读为mm / dd / yyyy hh：mm。所以VBA读为01/05/2015（可能的第一）作为jan的第五。

So I am importing dates from a computer generated CSV file. In the CSV file, the dates are in the dd/mm/yyyy hh:mm format. However when I import it using VBA, VBA reads it as mm/dd/yyyy hh:mm. so VBA reads 01/05/2015 (1st of may) as 5th of jan.

我检查过，CSV文件以dd / mm / yyyy hh：mm格式明确。

I checked, and the CSV file is definietly in the dd/mm/yyyy hh:mm format.

任何解决这个问题的帮助将不胜感激。

any help in fixing this would be greatly appreciated.

所以当我手动打开CSV文件时，日期是dd / mm / yyyy hh：mm格式。像2016年12月1日的01/12/2016 1:00。但是当VBA打开它时，它会变成12/01/2016 1:00。

So when I open the CSV file manually, the date is in the dd/mm/yyyy hh:mm format. Like 1st of december 2016 would be 01/12/2016 1:00. But when VBA opens it, it changes to 12/01/2016 1:00.

这是整个代码。它没有什么复杂的，我无法弄清楚它有什么问题。

This is the entire code in question. Its nothing complex, and I cant figure out whats wrong with it.

Sub import()
    Dim calbook As Workbook
    Dim newwb As Workbook
    Dim destiwb As Workbook
    Dim directory As String
    Dim Filename As String
    directory = "C:\Users\winterco\Desktop\"
    Set calbook = Workbooks("Data_totaliser1.xlsm")
    Filename = Dir(directory & "*.CSV")
    Set newwb = Workbooks.Add
    Set destiwb = ActiveWorkbook
    Do While Filename <> ""
        Workbooks.OpenText (directory & Filename)
        Call Sort_Data(Filename, destiwb, directory, calbook)
        Filename = Dir()  
    Loop  
End Sub

推荐答案

这是一个真正的噩梦...我从来没有找到简单的解决方案。

It's a real nightmare... I never found a "simple" solution.

我的解决办法是，我始终以VBA作为字符串打开CSV，以便自己进行转换，这样我就没有坏的惊喜。或者如果你想减少工作，做Thunderframe建议，打开文本，这样没有转换，并循环使用下面的代码：

My workaround is that I always open CSV with VBA as strings to do the conversions myself, this way I don't have bad surprises. Or if you want to make it less work, do as Thunderframe suggested, open as text, this way no conversion, and do a loop with the code here under:

Dim z: z = VBA.Split(VBA.Replace(x, "-", "/"), "/") 'works with 01/01/2001 and 01-01-2001
answer = DateSerial(Left(z(2), 4), z(1), Left(z(0), 2)) 'left for the case DD/MM/YY 00:00:00, because unlike you I don't give a f* for time of day. If you want the time of the day, you'll need to use :

z=split(x,":")
answer =answer + TimeSerial(right(z(0),2)  , z(1), z(2) ) 'untested code

这篇关于VBA正在将我的日期改为mm / dd / yyyy hh：mm导入时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！