Getting file last modified date (explorer value not cmd value)

Question

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.

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.

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

 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.

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

    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:

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

Answer 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.

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.

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