Excel VBA:导入日期为dd / mm / yyyy的CSV [英] Excel VBA: importing CSV with dates as dd/mm/yyyy
问题描述
我知道这是一个相当普遍的问题,但我还没有找到可靠的解决方案。
I understand this is a fairly common problem, but I'm yet to find a reliable solution.
我有一个csv文件中的数据, dd / mm / yyyy。当我用Workbooks.OpenText打开它时,它默认为mm / dd / yyyy,直到它发现它认为是月份超过12,然后恢复为dd / mm / yyyy。
I have data in a csv file with the first column formatted dd/mm/yyyy. When I open it with Workbooks.OpenText it defaults to mm/dd/yyyy until it figures out that what it thinks is the month exceeds 12, then reverts to dd/mm/yyyy.
这是我的测试代码,它试图强制它作为xlDMYFormat,我也试过文本格式。我理解这个问题只适用于* .csv文件,而不是* .txt,但这不是一个可以接受的解决方案。
This is my test code, which tries to force it as xlDMYFormat, and I've also tried the text format. I understand this problem only applies to *.csv files, not *.txt, but that isn't an acceptable solution.
Option Base 1
Sub TestImport()
Filename = "test.csv"
Dim ColumnArray(1 To 1, 1 To 2)
ColumnsDesired = Array(1)
DataTypeArray = Array(xlDMYFormat)
' populate the array for fieldinfo
For x = LBound(ColumnsDesired) To UBound(ColumnsDesired)
ColumnArray(x, 1) = ColumnsDesired(x)
ColumnArray(x, 2) = DataTypeArray(x)
Next x
Workbooks.OpenText Filename:=Filename, DataType:=xlDelimited, Comma:=True, FieldInfo:=ColumnArray
End Sub
$ b b
test.csv包含:
test.csv contains:
Date
11/03/2010
12/03/2010
13/03/2010
14/03/2010
15/03/2010
16/03/2010
17/03/2010
推荐答案
我有与.OpenText方法相同的问题。我的区域设置是英语 - 澳大利亚而不是NZ。
我发现另一个线程,建议使用.open,
( http://www.pcreview.co.uk/forums/date-format-error-csv-file-t3960006.html )
所以我试过
Application.Workbooks.Open文件名:= strInPath,格式:= xlDelimited,Local:= True
它工作。
I had the same problem with the .OpenText method. My Regional Settings are English - AU rather than NZ. I found another thread that suggested using .open, ( http://www.pcreview.co.uk/forums/date-format-error-csv-file-t3960006.html ) so I tried that Application.Workbooks.Open Filename:=strInPath, Format:=xlDelimited, Local:=True and it worked.
这篇关于Excel VBA:导入日期为dd / mm / yyyy的CSV的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!