获取文件最后修改日期(资源管理器值而不是cmd值)

I have written some Excel VBA code to add the filenames, versions, and last modified date/time to a worksheet. The code appears to work fine, except sometimes the time portion of the Last Modified Date for a file will either be exactly 1 hour forward or backward from what I see in an Explorer window.

我编写了一些Excel VBA代码，将文件名、版本和最后修改的日期/时间添加到工作表中。代码看起来运行得很好，除了有时文件最后修改日期的时间部分与我在Explorer窗口中看到的时间部分恰好相差1小时。

I have noticed the values that my code returns is the same as the modified date/time shown in a cmd window if I perform a dir command.

我注意到我的代码返回的值与cmd窗口中显示的修改后的日期/时间相同，如果我执行一个dir命令。

For example, if I look up the dbghelp.dll file in the system32 folder:

例如，如果我查找dbghelp。在system32文件夹中的dll文件:

 C:\Windows\System32>dir dbghelp.*
 Volume in drive C has no label.
 Volume Serial Number is 16E8-4159

 Directory of C:\Windows\System32

 21/11/2010  04:24         1,087,488 dbghelp.dll
               1 File(s)      1,087,488 bytes
               0 Dir(s)  60,439,101,440 bytes free

 C:\Windows\System32>

But the same file in an Explorer window shows a modified time of 03:24 on 21/11/2010 - 1 hour earlier.

但是在Explorer窗口中的同一个文件显示了修改后的时间03:24，即2010年11月21日- 1小时前。

The code I have written is returning the cmd window time, whereas I want the Explorer window time:

我写的代码是返回cmd窗口时间，而我想要资源管理器窗口时间:

    Sub GetFileDetails()
    Dim path As String
    Dim objFSO As Object
    Dim objFile As Object
    Dim objFolder As Object
    Dim loopCount As Integer
    Dim pathCheck As Boolean


    'Prompt for directory path
    path = InputBox(Prompt:="Enter file path", Title:="Enter file path", Default:="")
    If (path = "" Or path = vbNullString) Then
        MsgBox ("Invalid path - exiting")
        Exit Sub
    End If

    'Required for interacting with filesystem
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(path)

    '1st row for path title, 2nd row for column headings
    loopCount = 3
    For Each objFile In objFolder.Files
        Range("A" & loopCount).Value = objFile.Name
        Range("B" & loopCount).Value = objFSO.GetFileVersion(objFile)
        Range("C" & loopCount).Value = objFile.DateLastModified

        'Combine Version and Modified
        If Range("B" & loopCount).Value <> "" Then
            Range("D" & loopCount).Value = Range("B" & loopCount).Value & ", " & Range("C" & loopCount).Value
        Else
            Range("D" & loopCount).Value = Range("C" & loopCount).Value
        End If

        loopCount = loopCount + 1
    Next

    'Set up headings
    Range("A" & 1).Value = (loopCount - 3) & " files found in " & path
    Range("A" & 2).Value = "FileName"
    Range("B" & 2).Value = "Version"
    Range("C" & 2).Value = "Modified"
    Range("D" & 2).Value = "Version & Modified"
End Sub

If anyone can shed some light on this issue - it will be greatly appreciated.

如果有人能对这个问题有所了解，我们将不胜感激。

===EDIT=== This is the code I have come up with which always gives me the same time as displayed in an explorer window:

=== ==EDIT=== === =这是我想到的代码，它总是给我与浏览器窗口中显示的时间相同:

Sub GetFileDetails()
    Dim path As String
    Dim objFSO As Object
    Dim objFile As Object
    Dim objFolder As Object
    Dim loopCount As Integer
    Dim pathCheck As Boolean

    Dim modDate As Date
    Dim modHour As Integer
    Dim modMin As Integer

    'Prompt for directory path
    path = InputBox(Prompt:="Enter file path", Title:="Enter file path", Default:="")
    If (path = "" Or path = vbNullString) Then
        MsgBox ("Invalid path - exiting")
        Exit Sub
    End If

    'Required for interacting with filesystem
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(path)

    '1st row for path title, 2nd row for column headings
    loopCount = 3
    For Each objFile In objFolder.Files
        Range("A" & loopCount).Value = objFile.Name
        Range("B" & loopCount).Value = objFSO.GetFileVersion(objFile)
        Range("D" & loopCount).Value = objFile.Name


        'The date modified time for files made in Summer Time are correct, whereas Winter Time will be 1 hour forward
        If (IsItSummerTime(objFile.DateLastModified) = True) Then
            Range("C" & loopCount).Value = objFile.DateLastModified
        Else
            modDate = Format(objFile.DateLastModified, "DD-MM-YYYY")
            modHour = Hour(objFile.DateLastModified)
            modMin = Minute(objFile.DateLastModified)

            modHour = modHour - 1

            If (modHour < 10) Then
               If (modMin < 10) Then
                  Range("C" & loopCount).Value = modDate & " 0" & modHour & ":0" & modMin
               Else
                  Range("C" & loopCount).Value = modDate & " 0" & modHour & ":" & modMin
               End If
            Else
               If (modMin < 10) Then
                  Range("C" & loopCount).Value = modDate & " " & modHour & ":0" & modMin
               Else
                  Range("C" & loopCount).Value = modDate & " " & modHour & ":" & modMin
               End If
            End If
        End If

        'Combine Version and Modified
        If Range("B" & loopCount).Value <> "" Then
            Range("E" & loopCount).Value = Range("B" & loopCount).Value & ", " & Range("C" & loopCount).Value
        Else
            Range("E" & loopCount).Value = Range("C" & loopCount).Value
        End If

        loopCount = loopCount + 1
    Next

    'Set up headings
    Range("A" & 1).Value = (loopCount - 3) & " files found in " & path
    Range("A" & 2).Value = "FileName"
    Range("B" & 2).Value = "Version"
    Range("C" & 2).Value = "Modified"
    Range("D" & 2).Value = "FileName"
    Range("E" & 2).Value = "Version & Modified"

End Sub

Function IsItSummerTime(inDate As Date) As Boolean
    Dim inDateYear As Integer
    Dim findFirstSunday As Date
    Dim firstSundayDate As Date
    Dim startDays As Integer
    Dim endDays As Integer
    Dim summerStart As Date
    Dim summerEnd As Date

    'Summer Time starts on the 13th week
    'Summer Time ends on the 42nd week
    If (IsItALeapYear(inDate) = True) Then
        startDays = (12 * 7) + 1
        endDays = (42 * 7) + 1
    Else
        startDays = 12 * 7
        endDays = 42 * 7
    End If

    'Find the date of the first Sunday in the year
    inDateYear = Year(inDate)
    For i = 1 To 7
        findFirstSunday = DateSerial(inDateYear, 1, i)
        If (Weekday(findFirstSunday) = 1) Then
            firstSundayDate = findFirstSunday
        End If
    Next i

    'Calculate the start and end dates for Summer Time
    summerStart = firstSundayDate + startDays
    summerEnd = firstSundayDate + endDays

    'Compare inDate to Summer Time values and return boolean value
    If (inDate >= summerStart And inDate < summerEnd) Then
        IsItSummerTime = True
    Else
        IsItSummerTime = False
    End If
End Function
Function IsItALeapYear(inDate As Date) As Boolean
    If (Month(DateSerial(Year(inDate), 2, 29))) = 2 Then
        IsItALeapYear = True
    Else
        IsItALeapYear = False
    End If
End Function

1 个解决方案

#1

It looks like this is ultimately an OS issue that you'd have to work around, like has been shown, especially since you've edited your code to account for DST.

看起来这最终是一个操作系统的问题，您必须解决这个问题，就像已经显示的那样，特别是因为您已经为DST编辑了代码。

But you could also use the FileDateTime function. The help article for this points out that the result of this function is based on your system's locale settings. The help article for the DateLastModified property doesn't provide any such caveats, at least for Excel online help.

但是您也可以使用FileDateTime函数。本文的帮助文章指出，该函数的结果基于系统的语言环境设置。DateLastModified属性的帮助文章没有提供任何此类警告，至少对于Excel在线帮助是这样的。

To modify an exerpt from your edited code above:

要修改上面编辑过的代码中的一个选项:

'1st row for path title, 2nd row for column headings
loopCount = 3
For Each objFile In objFolder.Files
    Range("A" & loopCount).Value = objFile.Name
    'use the full path name
    Range("B" & loopCount).Value = FileDateTime(objFile_fullpathname)
    Range("D" & loopCount).Value = objFile.Name

#1