编程打开文件时，Excel VBA日期格式/值会更改 [英] Excel VBA date formats/values change when file is opened programatically

问题描述

我有一个问题，以编程方式打开.csv文件。该文件是每日数据的时间序列，其中多个数据点与每个日期相关联。

I've got something of a problem opening a .csv file programmatically. The file is a time series of daily data, with several data points associated with each date.

当我手动打开文件时，日期正确打开，日期格式为 dd / mm / yyyy 。但是，当我以编程方式打开文件时，直到每月12号的日期都会打开为 mm / dd / yyyy ，尽管格式保留 dd / mm / yyyy （例如1983年7月1日（1/7/1983），将于1983年1月7日（7/1/1983）开通 - 这不仅仅是格式问题，与这些日期相关联的Julian日期（自1901年1月1日以来的日期也会发生变化），并且每个月12日之后的日期会正确打开，但是作为文本而不是日期。

When I manually open the file, the dates open correctly, as the date format dd/mm/yyyy. However, 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.

以文本形式输入的数据不是问题，但是，打开文件后更改的日期有问题。我可以尝试导入整个.csv文件作为逗号分隔的文本，而不是实际打开文件，但是，它会更容易和更快，如果我可以停止更改日期，当我打开该文件。

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 actually opening the file, however, it would be easier and faster if I could stop the dates from changing when I open the file.

有没有人在过去有类似的问题？任何建议，这将是非常感谢。

干杯，本。

Has anyone had a similar problem to this in the past? Any advice on this would be much appreciated.
Cheers, Ben.

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

问题似乎发生在Workbooks.Open（File）行。

The problem seems to occur at the line Workbooks.Open(File). Sorry for not throwing this up to begin with.

推荐答案

由于问题已经由OP在评论中回答了，

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.

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

通过设置Local = true使用本地机器日期格式，假设mdy所以在语言环境如澳大利亚（dmy）它会改变Open（）的行为

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()

这篇关于编程打开文件时，Excel VBA日期格式/值会更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！