Excel 2010 VBA 工作表副本重新格式化日期（非美国格式：即 dd/mm/yyyy）

Excel 2010 VBA sheet copy reformats dates (non US format: i.e. dd/mm/yyyy)

提问by Tony

I am trying to copy a either an entire worksheet or a range of data from one workbook to another. I can do this successfully manually, by selecting all the cells with data in them and pasting into a newly created worksheet (Date format is maintained). However when I try to do this via VBA any date with a day greater than 12 gets converted to text [US (m/dd/yyyy)/Australian (dd/mm/yyyy) format change]. I've tried formating the cells first to US format dates but this doesn't help. Any sugestions?

我正在尝试将整个工作表或一系列数据从一个工作簿复制到另一个工作簿。我可以手动成功完成此操作，方法是选择其中包含数据的所有单元格并粘贴到新创建的工作表中（保留日期格式）。但是，当我尝试通过 VBA 执行此操作时，任何一天大于 12 的日期都会转换为文本 [美国 (m/dd/yyyy)/澳大利亚 (dd/mm/yyyy) 格式更改]。我已经尝试先将单元格格式化为美国格式日期，但这无济于事。有什么建议吗？

FYI - source data is from a CSV file that is opened by VBA - date values are text (eg "2/08/2013" or "31/07/2013") that are automatically converted to dates on open (IsNumber = True and IsText = False).

仅供参考 - 源数据来自 VBA 打开的 CSV 文件 - 日期值是文本（例如“2/08/2013”​​或“31/07/2013”​​），在打开时自动转换为日期（IsNumber = True 和IsText = 假）。

CSV on open looks like this (sample of 3000 odd lines): Note that "Date" and "Last update" fields are in dd/mm/yyyy hh:mm format

打开时的 CSV 看起来像这样（3000 条奇数行的示例）：请注意，“日期”和“上次更新”字段采用 dd/mm/yyyy hh:mm 格式

Id          Title     Division  Status  Date                Last update
REQ:7619    Job Details ICTIS   InProg  31/07/2013 13:03    6/08/2013 15:51
REQ:7617    Job Details ICTIS   InProg  31/07/2013 12:06    2/08/2013 11:34
REQ:6994    Job Details ICTIS   Open    31/07/2013 12:05    31/07/2013 12:05
REQ:7613    Job Details MNHD    User    31/07/2013 12:01    1/08/2013 15:59
INC:79210   Job Details ICTIS   Open    31/07/2013 12:00    31/07/2013 12:00

Simple excerpt of Code:

代码的简单摘录：

'Select all Data Cells
Sheets(1).Cells(1, 1).Activate
ActiveCell.SpecialCells(xlLastCell).Select
LastRow = ActiveCell.Row

'Format Date Fields to avoid automatic translation issue
'This does not help problem... so commented out
'Columns("M:M").Select
'Selection.NumberFormat = "m/d/yyyy"

'Select all data in sheet and copy
range("A2:" & LastCol & LastRow).Select
selection.Copy

'Rest of code moves to previous sheet and copies into cell A8

Same file after running the above code. Note how dates with day>12 are treated differently to those with day<=12. 31/7/2013 has "PM" added, while 6/8/2013 and 2/8/2013 have day and month swapped!

运行上述代码后相同的文件。请注意 day>12 的日期与 day<=12 的日期的处理方式不同。31/7/2013 添加了“PM”，而 6/8/2013 和 2/8/2013 交换了日期和月份！

Id          Title     Division  Status  Date                Last update
REQ:7619    Job Details ICTIS   In Progress 31/07/2013 1:03:49 PM   8/06/2013 15:51
REQ:7617    Job Details ICTIS   In Progress 31/07/2013 12:06:31 PM  8/02/2013 11:34
REQ:6994    Job Details ICTIS   Open    31/07/2013 12:05:50 PM  31/07/2013 12:05:50 PM
REQ:7613    Job Details MNHD    With User   31/07/2013 12:01:46 PM  8/01/2013 15:59
INC:79210   Job Details ICTIS   Open    31/07/2013 12:00:52 PM  31/07/2013 12:00:52 PM

My quick fix has been to split the code into 2 parts - with a manual copy/paste prompted by a MsgBox. Not very elegant!! and prone to error.

我的快速修复是将代码分成两部分 - 由 MsgBox 提示手动复制/粘贴。不是很优雅！！并且容易出错。

回答by Cameron Bradley

I have had the exact same problem in the past. I can confirm that the problem occurs when the CSV is opened using a macro - when running vba excel defaults to opening using US date formats, whereas when opening a CSV manually it uses your local date formats. The easy fix not requiring a workaround of any sort is to instruct VBA to open the CSV using your local date formats as follows.

我过去遇到过完全相同的问题。我可以确认问题是在使用宏打开 CSV 时发生的 - 运行 vba excel 时默认使用美国日期格式打开，而手动打开 CSV 时它使用您的本地日期格式。不需要任何类型的解决方法的简单修复是指示 VBA 使用您的本地日期格式打开 CSV，如下所示。

The relevant line of my broken code:

我损坏的代码的相关行：

Set wbkData = Workbooks.Open(strDataDir & strDataFName)

And the fixed line:

和固定线路：

Set wbkData = Workbooks.Open(strDataDir & strDataFName, local:=True)

回答by D_Bester

Excel uses your default system format to determine how to interpret a date when you type it in a cell. In USA if you type in "3/2/2013" it will interpret it as Mar 2. In Australia it will interpret the same keystrokes as Feb 3. Changing the cell formatting doesn't make any difference as you discovered.

Excel 使用默认系统格式来确定在单元格中键入日期时如何解释日期。在美国，如果您输入“3/2/2013”​​，它将解释为 Mar 2。在澳大利亚，它将解释为与 2 月 3 日相同的击键。更改单元格格式与您发现的没有任何区别。

The conversion to text will occur when you copy a date that is not compatible with your system format (eg. has 20 as the month). I really think the problem is actually happening at the time that you open the CSV file.

当您复制与系统格式不兼容的日期（例如，月份为 20）时，将转换为文本。我真的认为问题实际上是在您打开 CSV 文件时发生的。

If the problem were in the copy operation you could convert the date to text before working with it. =Text(a1, "m/d/yyyy")

如果问题出在复制操作中，您可以在使用之前将日期转换为文本。 =Text(a1, "m/d/yyyy")

Edit: It must be that one workbook is in US format and that the other is in Aussie format. I have some ideas about what is happening but I cannot say for sure. Make sure both workbooks are opened in the same instance of Excel. If both workbooks are opened by your code then it should not be a problem. Check your task manager process list to make sure that Excel.exe only appears once.

编辑：必须是一个工作簿是美国格式，另一个是澳大利亚格式。我对正在发生的事情有一些想法，但我不能肯定地说。确保两个工作簿都在同一个 Excel 实例中打开。如果两个工作簿都由您的代码打开，那么应该没有问题。检查您的任务管理器进程列表以确保 Excel.exe 只出现一次。

Also I'm not sure if your code is accidentally causing a recalculation and that could somehow cause a problem. This code should fix that:

此外，我不确定您的代码是否意外导致重新计算，并且可能会以某种方式导致问题。这段代码应该解决这个问题：

'get source worksheet
Dim ws As Worksheet
Set ws = Sheets(1)
ws.Activate

'copy all but first row
Call ws.UsedRange.Offset(1, 0).Resize(rowsize:=ws.UsedRange.Rows.Count - 1).Copy

'activate second sheet
Dim wsDest As Worksheet
Set wsDest = Sheets(2)
wsDest.Activate
wsDest.Paste

'de-activate copy mode
Application.CutCopyMode = False

Also you can use the code editor window to step through the code (press F8) to see exactly which line is causing the data to change.

此外，您还可以使用代码编辑器窗口单步执行代码（按 F8）以准确查看哪一行导致数据更改。

回答by Tony Dallimore

In this answer I describe two methods of achieving the error you describe. If one of those methods matches what you are doing, you may be able to amend your code to avoid the error. If that is not enough then I need to see more of your code before I can suggest a remedy. You could add the complete code to your answer or you could use the address in my profile to email it to me.

在这个答案中，我描述了实现您描述的错误的两种方法。如果其中一种方法与您正在执行的操作相匹配，则您可以修改代码以避免该错误。如果这还不够，那么我需要先查看您的更多代码，然后才能提出补救措施。您可以将完整代码添加到您的答案中，或者您可以使用我个人资料中的地址将其通过电子邮件发送给我。

It has taken me a lot of experimentation to duplicating your error but I can now achieve the corruption of a date to middle endian format in two ways. (Middle endian seems to be the preferred academic description for m/d/y date format with d/m/y and y/m/d formats referred to as small and big endian.)

我花了很多实验来复制您的错误，但我现在可以通过两种方式实现日期到中间端格式的损坏。（中端似乎是 m/d/y 日期格式的首选学术描述，d/m/y 和 y/m/d 格式被称为小端和大端。）

At heart the problem is that Excel always has several ways of achieving the same effect. The effect here is the conversion of a string to a date.

问题的核心在于 Excel 总是有多种方法可以实现相同的效果。这里的效果是将字符串转换为日期。

Approach 1 is to use the country code to determine the local date format. If you live in a country where the local date format is d/m/y then with this approach “06/08/2013” will be converted to “6 August, 2013”.

方法一是使用国家代码来确定本地日期格式。如果您居住在当地日期格式为 d/m/y 的国家/地区，那么使用这种方法，“06/08/2013”​​将转换为“2013 年 8 月 6 日”。

Approach 2 is to use Excel default date format which in Britain, Australia and many other countries is “*dd/mm/yyyy” but if you ask VBA for the NumberFormat corresponding to “*dd/mm/yyyy” you are given “m/d/yyyy”. Approach 2 will convert “06/08/2013” to “June 8, 2013”. This is explained in more detail in my question: .NumberFormat sometimes returns the wrong value with dates and times.

方法二是使用Excel默认的日期格式，在英国、澳大利亚和许多其他国家是“*dd/mm/yyyy”，但是如果你向VBA询问对应于“*dd/mm/yyyy”的NumberFormat，你会得到“m /d/yyyy”。方法 2 会将“06/08/2013”​​转换为“2013 年 6 月 8 日”。这在我的问题中有更详细的解释：.NumberFormat 有时会返回错误的日期和时间值。

If you import a CSV file and specify the format of a date/time column as “General”, it will use Approach 1. To corrupt that date, you need to move it with code like this:

如果您导入一个 CSV 文件并将日期/时间列的格式指定为“常规”，它将使用方法 1。要破坏该日期，您需要使用如下代码移动它：

Dim CopyDate As String
CopyDate = Cell1.Value
Cell2.Value = CopyDate          ' This uses Approach 2

If you import a CSV file and specify the format of a date/time column as “Text”, it will leave the value as text. If you move that text value using a method that causes Excel to evaluate the text, it will use Approach 2 to convert it to a date.

如果您导入一个 CSV 文件并将日期/时间列的格式指定为“文本”，它会将值保留为文本。如果使用导致 Excel 评估文本的方法移动该文本值，它将使用方法 2 将其转换为日期。

Hope this helps.

希望这可以帮助。