以编程方式打开文件时，日期格式/值会更改 [英] Date formats/values change when file is opened programmatically

问题描述

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

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

当我手动打开文件时，日期会以日期格式dd/mm/yyyy正确打开.

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

当我以编程方式打开文件时，直到12月的每个日期都以mm/dd/yyyy格式打开，尽管格式仍为dd/mm/yyyy(例如1983年7月1日(1/7/1983))将是于1983年1月7日(1983年1月7日)开始开放-这不仅是格式问题，与这些日期相关的儒略日期(自1901年1月1日以来的天数)也发生了变化，并且每个日期的第12日之后可以正确打开月份，尽管是文本而不是日期.

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

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)行.

推荐答案

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

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