vba 以编程方式打开文件时，日期格式/值会发生变化

Date formats/values change when file is opened programmatically

提问by Ben F

I have .csv file which is a time series of daily data, with several data points associated with each date.

我有 .csv 文件，它是每日数据的时间序列，每个日期都有几个数据点。

When I manually open the file, the dates open correctly, as the date format dd/mm/yyyy.

当我手动打开文件时，日期正确打开，作为日期格式dd/mm/yyyy。

When I open the file programmatically, the dates up to the 12th of each month are opened as mm/dd/yyyy, although the format remains dd/mm/yyyy(e.g. the 1st of July 1983 (1/7/1983), would be opened as the 7th of January 1983 (7/1/1983) - this isn't just a formatting issue, the Julian Date (days since 1 Jan 1901) associated with these dates also changes), and the dates after the 12th of each month are opened correctly, although as text rather than a date.

当我以编程方式打开文件时，每个月 12 日之前的日期都被打开为mm/dd/yyyy，尽管格式仍然存在dd/mm/yyyy（例如 1983 年 7 月 1 日（1/7/1983），将被打开为 1983 年 1 月 7 日（7 /1/1983) - 这不仅仅是格式问题，与这些日期相关的儒略日期（自 1901 年 1 月 1 日以来的天数）也会发生变化），并且每个月 12 日之后的日期都正确打开，尽管是文本而不是比约会。

The data coming in as text is not an issue, however, the dates changing as soon as the file is opened is problematic. I could try to import the entire .csv file as comma delimited text rather than opening the file, however, it would be easier and faster if I could stop the dates from changing when I open the file.

以文本形式输入的数据不是问题，但是，一旦打开文件就更改日期是有问题的。我可以尝试将整个 .csv 文件作为逗号分隔的文本导入，而不是打开文件，但是，如果我可以在打开文件时阻止日期更改，会更容易、更快。

Flder = InputBox("Copy and Paste Folder path here:")

Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourcePath = FSO.GetFolder(Flder)

For Each File In SourcePath.Files        
    Workbooks.Open (File)

    FlNm = File.Name

    StrtCol = Workbooks(FlNm).Worksheets(1).Range(Cells(4, 1), Cells(4, 30)).Find ("Mean").Column

    Workbooks(FlNm).Worksheets(1).Range(Cells(1, 1), Cells(60000, 1)).Copy (Workbooks("Find Water Years V2.xls").Worksheets(1).Range("A3"))
    Workbooks(FlNm).Worksheets(1).Range(Cells(1, StrtCol), Cells(60000, StrtCol + 1)).Copy (Workbooks("Find Water Years V2.xls").Worksheets(1).Range("B3"))

    Workbooks(FlNm).Close
Next

The problem seems to occur at the line Workbooks.Open(File).

问题似乎发生在线路上Workbooks.Open(File)。

回答by David Waterworth

Since the question has already been answered by the OP in the comments but not posted as an official answer I'll put it here in case someone else misses it like I did.

由于 OP 已经在评论中回答了该问题，但未作为正式答案发布，因此我会将其放在这里，以防其他人像我一样错过它。

workbook = workbooks.Open(filename, Local:= true)

By setting Local = true uses the local machines date format rather than assuming mdy so in locales such as Australia (dmy) it'll change the behavior of Open()

通过设置 Local = true 使用本地机器日期格式而不是假设 mdy 所以在诸如澳大利亚 (dmy) 这样的语言环境中它会改变 Open() 的行为

回答by Siddharth Rout

I could try to import the entire .csv file as comma delimited text rather than actually opening the file, however, it would be easier and faster if I could stop the dates from changing when I open the file.

我可以尝试将整个 .csv 文件作为逗号分隔的文本导入，而不是实际打开文件，但是，如果我可以在打开文件时阻止日期更改，会更容易和更快。

It is still fast if you open it as CDT. You simply use .OpenTextinstead of .Open. Rest of the code remains same :)

如果您以 CDT 的形式打开它，它仍然很快。您只需使用.OpenText而不是.Open. 其余代码保持不变:)

Try recording a macro and you will see that the code looks somewhat like this.

尝试录制一个宏，您会看到代码看起来有点像这样。

Workbooks.OpenText Filename:= File, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

回答by Rinion

I found that user2981639 had the right idea, but I got a syntax error with 'Local: true' so I used 'local:=True' instead

我发现 user2981639 的想法是正确的，但是我收到了“Local: true”的语法错误，所以我改用了“local:=True”

This problem was driving me nuts because I also had 2 memo fields in the csv file. If I tried to format the dates in any way the memo fields would not import correctly, they would either be truncated to 255 characters or any embedded CRLF characters would cut the record up into pieces depending on how many there were.

这个问题让我抓狂，因为我的 csv 文件中还有 2 个备注字段。如果我试图以任何方式格式化日期，备忘录字段将无法正确导入，它们要么被截断为 255 个字符，要么任何嵌入的 CRLF 字符将根据记录的数量将记录切成碎片。

Thanks guys for posting

谢谢各位发帖

回答by Pramod Yadav

Here, you see a working code with example. This problem frustrated me as well for a while. I was opening a .txt file in excel and the date I had use for was converting from dd-mm-yyyy format to mm-dd-yyyy format. Below you see a solution. It lay in the last command (,Local := True). See below. Hope it helps.

在这里，您会看到一个带有示例的工作代码。这个问题也让我沮丧了一段时间。我在 excel 中打开一个 .txt 文件，我使用的日期从 dd-mm-yyyy 格式转换为 mm-dd-yyyy 格式。下面你会看到一个解决方案。它位于最后一个命令 (,Local := True) 中。见下文。希望能帮助到你。

Note that while trasferring .txt file to excel use File Origin as "xlWindows:
    Workbooks.OpenText Filename:= _
    ThisWorkbook.Worksheets("Reporting").Cells(3, 6), Origin:=xlWindows, _
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(49 _
    , 1), Array(50, 1), Array(67, 1), Array(80, 1), Array(93, 1), Array(106, 1), Array(119, 1)) _
    , DecimalSeparator:=",", ThousandsSeparator:=".", TrailingMinusNumbers:= _
    True, Local:=True

NOTE: rest above settings for array and decimal separator are my work specific - may not apply to you.

注意：数组和小数点分隔符的其余设置是我的工作特定的 - 可能不适用于您。

回答by dinesh

I am also struggling to find an answer. However what resolves this problem is.:-

我也在努力寻找答案。但是，解决此问题的方法是：-

Local should be true. and workbook close save changes must be false. That should work.

本地应该是真的。并且工作簿关闭保存更改必须为假。那应该工作。

回答by VDeen

I experienced this problem with Office 365. With importing an csvfile with Excel some dates dd/mm/jjjj(European) where imported as American dates mm/dd/jjjj. The csvfile doesn't have any formatting of dates closed in.

我在Office 365 中遇到了这个问题。通过使用 Excel导入csv文件，一些日期dd/mm/jjjj（欧洲）被导入为美国日期mm/dd/jjjj。该CSV文件没有在休息日的任何格式。

By opening as follows:

通过打开如下：

Set xla = CreateObject("Excel.Application")
Set xlb = xla.Workbooks.Open(sFilePath, ReadOnly:=False, Local:=True)

The local settings where used, and problem solved :-)

使用的本地设置，问题解决了:-)

Other options with opening a file are:

打开文件的其他选项是：

expression.Open (FileName, UpdateLinks, ReadOnly, 
                 Format, Password, WriteResPassword, 
                 IgnoreReadOnlyRecommended, Origin, 
                 Delimiter, Editable, Notify, Converter, 
                 AddToMru, Local, CorruptLoad)