VBA导入上的美国日期格式

Question

又是一次令人讨厌的美国约会，这次我在很多搜索中都没有看到解决方案。

我发现了一些VBA，它可以从底特律的商业合作伙伴生成的一些htm文件中提取数据：

Dim DirPath As String
Dim I_Row As Integer
Dim FilePath As String
Dim xCount As Long
DirPath = ipsosFolder
If DirPath = "" Then Exit Sub
Application.ScreenUpdating = False
FilePath = Dir(DirPath & "*.htm")
Do While FilePath <> ""
With ActiveSheet.QueryTables.Add(Connection:="URL;" _
& DirPath & "" & FilePath, Destination:=Range("A1"))
.Name = "a"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
FilePath = Dir
End With
Loop

(注意.WebDisableDateRecognition = True，这是我解决这个问题的第一个希望，让Excel忽略美国化日期，直到我能做点什么来解决它们。(

因此，这里的日期以美国的方式格式化——例如，2022年9月21日是今天。现在，这个特定的日期无关紧要，因为在澳大利亚，它是无效的，没有第21个月，我可以用快速的=RIGHT(LEFT(B2,5),2)&"/"&LEFT(B2,2)&"/"&RIGHT(B2,10)很容易地解决这个问题，但当我们到2022年12月9日这样的日期时，我的问题就来了，因为有12月9号和9月12日，Excel非常友好，将其转换为日期整数，该日期整数打破了上述代码，因为左/右不再引用2022年12月9日，而是引用44904。

任何对我能做什么有建议的人都可以停止Excel"；帮助；并将日期转换为美式翻译-我已经检查了机器，仔细检查了所有窗口设置是否为正确的日期格式。

最大的问题是，我需要将这些日期与我们从另一个数据集中提取的一些日期进行比较；适当的"；d/m/y而不是m/d/y的格式，就像只有美国人坚持使用的那样，所以需要将其保留为日期和准确=\

Answer 1

如果(美国(月份小于或等于12的日期被转换为按月交换日期，而其他日期仍为字符串/text，请使用下一个函数：

Function convertUSStr_DateToAU(arr As Variant) As Variant
Dim arrConv, arrStrD, i As Long
ReDim arrConv(1 To UBound(arr), 1 To 1)
For i = 1 To UBound(arrConv)
If IsNumeric(arr(i, 1)) Then
arrConv(i, 1) = DateSerial(Year(arr(i, 1)), Day(arr(i, 1)), Month(arr(i, 1)))
Else
arrStrD = Split(arr(i, 1), "/")
arrConv(i, 1) = DateSerial(CLng(arrStrD(2)), CLng(arrStrD(0)), CLng(arrStrD(1)))
End If
Next i
convertUSStr_DateToAU = arrConv
End Function

它可以用于下一种方式：

Sub testConvertUSStr_DateToAU()
Dim sh As Worksheet, lastR As Long, arrD
Const Col As String = "B" 'column letter where the data to be correctly converted exists

Set sh = ActiveSheet
lastR = sh.Range(Col & sh.rows.count).End(xlUp).row

arrD = sh.Range(Col & 2 & ":" & Col & lastR).Value2 'Date is taken as number...
arrD = convertUSStr_DateToAU(arrD)
'drop the converted array content and format it appropriately:
With sh.cells(2, Col).Offset(, 1).Resize(UBound(arrD), 1)
.Value2 = arrD
.NumberFormat = "dd/mm/yyy"
End With
End Sub

必须在要转换/处理的列之后立即有一个空列。实际的代码在该空列中返回转换后的数组。但是，只是为了让您检查它是否返回了您所需要的。。。

如果是这样，则应通过仅删除.Offset(, 1)来修改With语句范围。通过这种方式，它将准确地返回到需要转换Date的原始列中。