检索元数据时在VBA Excel中强制执行Cdate

Question

我有一段代码，它成功地返回了0-5项元数据。然而，日期以美国和英国混合格式返回。。。我需要强制执行Cdate或类似的东西，以便将日期全部读取为英国日期。(DD/MM/YY(我通常用Cdate做其他事情，但不确定如何让它发挥作用。。。。

代码：

Dim sFile As Object, obja
'Create Shell Object & NameSpace
Set oShell = CreateObject("Shell.Application")
Set oDir = oShell.Namespace("FILEPATH")
ActiveSheet.Cells.ClearContents
'Loop thru each File/Folder inside Root Directory
iRow = 1
For Each sFile In oDir.Items
iRow = iRow + 1

'Loop thru Each Property
For i = 0 To 5

'Get File Property Name & Value
obja = oDir.GetDetailsOf(sFile, i)
If obja <> "" Then
iRow = iRow + 1
'Enter File Property to Sheet
ActiveSheet.Range("A" & iRow) = oDir.GetDetailsOf(oDir, i)
ActiveSheet.Range("B" & iRow) = obja
End If
Next
Next
MsgBox "Process Completed"
End Sub

Answer 1

对于日期属性，将字符串拆分为天、月、年、小时、分钟，然后用DateSerial()和TimeSerial()重新创建日期。

Option Explicit
Sub files()
Dim sFile As Object, obja, oShell, oDir
Dim iRow As Long, i As Long
Dim sValue, sName As String
Dim arDT, arDMY, arHMS, dt As Date

'Create Shell Object & NameSpace
Set oShell = CreateObject("Shell.Application")
Set oDir = oShell.Namespace("C:tempsodata")
ActiveSheet.Cells.ClearContents

'Loop thru each File/Folder inside Root Directory
iRow = 1

For Each sFile In oDir.Items
iRow = iRow + 1

'Loop thru Each Property
For i = 0 To 5
sName = oDir.GetDetailsOf(oDir, i)
sValue = oDir.GetDetailsOf(sFile, i)

If sValue <> "" Then
iRow = iRow + 1
Range("A" & iRow) = sName
If sName Like "Date*" Then
' sValue is dd/mm/yyyy hh:mm
arDT = Split(sValue, " ")
arDMY = Split(arDT(0), "/")
arHMS = Split(arDT(1), ":")

dt = DateSerial(arDMY(2), arDMY(1), arDMY(0)) _
+ TimeSerial(arHMS(0), arHMS(1), 0)
Range("B" & iRow).NumberFormat = "dd/mm/yy hh:mm"
Range("B" & iRow) = dt
Else
Range("B" & iRow) = sValue
End If

End If
Next
Next

MsgBox "Process Completed"
End Sub