从其他工作簿复制工作表时，如何阻止Excel设置日期格式

Question

我在工作簿中编写了一个宏，打开指定的.csv文件并将内容复制到原始工作簿中的新工作表中。.csv中的一列包含非美国格式的日期，Excel将其中一些日期读取为美国日期并将其转换(例如，2020年2月12日[12/02/2020非美国日期]读取为2020年12月2日[12/04/2020美国日期]。

大多数日期都在第12天之后，因此无法转换为美国格式，这些日期被解读为文本。其余部分存储为日期整数。

如何通过程序阻止这种行为？

工作簿将由不同计算机上的多个用户使用，因此更改Windows日期默认值不是一个解决方案。

谢谢！

编辑：源工作表由另一个系统生成，文件归档在网络位置，B列数据为"dd/mm/yyyy"格式。

Dim Source_Sheet As Worksheet
Set Source_Sheet = Workbooks("example").Worksheets("example")
Dim Target_Sheet As Worksheet
Set Target_Sheet = Workbooks("example2").Worksheets("example2")
Source_Sheet.Copy After:=Target_Sheet

我认为格式的变化发生在复印工作表的过程中。

csv打开为：

Workbooks.Open ("file")

Answer 1

如果显示日期数据的格式为"dd/mm/yyyy"，则将应用下面的代码。

Sub Test()
Dim vR()
Dim rngDB As Range, rng As Range
Dim Source_Sheet As Worksheet
Dim Target_Sheet As Worksheet
Dim Ws As Worksheet
Dim r As Long, n As Long
Dim s As String, vS As Variant
Set Source_Sheet = Workbooks("example.xlsx").Worksheets("example")
Set Target_Sheet = Workbooks("example2.xlsx").Worksheets("example2")
Source_Sheet.Copy After:=Target_Sheet
Set Ws = Target_Sheet.Next
With Source_Sheet
Set rngDB = .Range("b1", .Range("b" & Rows.Count).End(xlUp))
End With
r = rngDB.Rows.Count
ReDim vR(1 To r, 1 To 1)
For Each rng In rngDB
n = n + 1
s = rng.Text
If InStr(s, "/") Then
vS = Split(s, "/")
vR(n, 1) = DateSerial(vS(2), vS(1), vS(0))
Else
vR(n, 1) = s
End If
Next rng
With Ws.Range("b1").Resize(r)
.Value = vR
.NumberFormat = "mm/dd/yyyy"
End With
End Sub

Answer 2

无论如何"导入"数据，我绝对建议在导入字符串时(在将其写入单元格之前(立即将其转换为实际日期值。否则你可能很容易陷入混乱。

例如，使用Split()函数将字符串按其分隔符/划分为日、月和年，然后使用DateSerial(year, month, day)函数

Dim RealDate As Date
RealDate = DateSerial(2020, 2, 12)
Range("A1").Value = RealDate

要创建一个真实的日期值，然后可以将其格式化为您喜欢的任何外观：

Range("A1").NumberFormat = "yyyy-mm-dd"

此日期是一个真实的日期值，可以使用进行排序和计算。

Answer 3

假设所有值的格式相同，即dd/mm/yyyy。

当复制到excel时，使用等于运算符="12/02/2020"。这将强制excel在不更改日期格式的情况下将数据保存为文本。

复制后，您可以将文本转换为dd/mm/yyyy格式的日期。