3

I am trying to get the date and time (last modified) of some files from a folder. I managed to get the date and hour/minutes/seconds but I can not get the milliseconds.

I already tried formatting the column in all the ways possible. I only get 0 for milliseconds.

My code so far does:

  • the user chooses a folder

  • the code displays in column A all the file names found and in column B the date, hour,minute and seconds (last modified date/time)

What should I do to the current code to obtain the milliseconds ?

This is my code: 

Private Function GetAllFiles(ByVal strPath As String, _
    ByVal intRow As Integer, ByRef objFSO As Object) As Integer
    Dim objFolder As Object
    Dim objFile As Object
    Dim i As Integer
    i = intRow - ROW_FIRST + 1
    Set objFolder = objFSO.GetFolder(strPath)
    For Each objFile In objFolder.Files
        'print file name
        Cells(i + ROW_FIRST + 2, 1) = objFile.Name
        'print file path
        Cells(i + ROW_FIRST + 2, 2) = objFile.DateLastModified
        i = i + 1
    Next objFile
    GetAllFiles = i + ROW_FIRST - 1
End Function
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
MisterA
  • 153
  • 5
  • 16
  • Is your filesystem FAT or NTFS? – ashleedawg Apr 04 '18 at 11:20
  • NTFS, windows 7, 64 bits – MisterA Apr 04 '18 at 11:26
  • good, me too. I'll put together an answer... but may I ask first, what are you planning on doing with this, and why do you require that level of granularity? (Also, VBA's `Date` type only has resolution of 1 second, so it will have to use a different data type.) How you're going to use it may affect the answer. – ashleedawg Apr 04 '18 at 11:31
  • In short, its about sorting scanned or splitted documents (pdf's). I already made the rename code and all. The problem is when i split a pdf in 100 pages it will be "part 1 2 3 4 5" and so on. Vba gets the date of last modified and it sorts after it. Only it can't sort them correctly if they have the same time and seconds of "creation". So i need miliseconds (explorer sorts them right). I woud of found other solutions (extracting the number from part 1 2 3 ) but this code will sort other kinds of pdf from the scanner and so on and showing the miliseconds woud be perfect. – MisterA Apr 04 '18 at 11:39
  • My english is far from perfect. So, vba reads the folder contents. Gets in excel the last modified date of the files from the folder. Sorts them after the date hh,mm,ss. From that point another piece of vba takes over inserting from another table "new names" for those files. The new names are already put in the order of the scanned documents or how they are displayed in a pdf that was splitted. The order has to match. – MisterA Apr 04 '18 at 11:45
  • uhh alright, kind of a strange requirement. You're right, extracting the number from the filename would be much easier (1 line of code versus 100). But have you **seen** the milliseconds? How do you know Windows is sorting by that? – ashleedawg Apr 04 '18 at 11:45
  • let's go to chat – ashleedawg Apr 04 '18 at 11:46
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/168209/discussion-between-ashleedawg-and-mistera). – ashleedawg Apr 04 '18 at 11:46
  • Sounds like an [XY problem](https://meta.stackexchange.com/q/66377/357835). I don't think that getting time on a millisecond scale is a robust solution to your problem. – John Coleman Apr 04 '18 at 12:20

1 Answers1

4

The following module will retrieve Windows file creation, modify or accessed datetime including milliseconds, using a Windows API call.

However it must be noted that there are a number of potential issues. A big one is that the VBA Date data type has a resolution of 1 second, so the datetime needs to be returned as a String, or stored in a different data type (Currency is the correct size.)

Option Explicit

Declare Function GetFileTime Lib "kernel32.dll" (ByVal hFile As Long, _
    lpCreationTime As FILETIME, lpLastAccessTime As FILETIME, _
    lpLastWriteTime As FILETIME) As Long

Declare Function CreateFile Lib "kernel32.dll" Alias "CreateFileA" _
    (ByVal lpFileName As String, ByVal dwDesiredAccess As Long, _
    ByVal dwShareMode As Long, lpSecurityAttributes As Any, _
    ByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As Long, _
    ByVal hTemplateFile As Long) As Long

Declare Function CloseHandle Lib "kernel32.dll" (ByVal hObject As Long) As Long

Declare Function FileTimeToSystemTime Lib "kernel32.dll" _
    (lpFileTime As FILETIME, lpSystemTime As SYSTEMTIME) As Long

Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type

Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type


Const GENERIC_READ = &H80000000
Const GENERIC_WRITE = &H40000000
Const FILE_SHARE_READ = &H1
Const FILE_SHARE_WRITE = &H2
Const CREATE_ALWAYS = 2
Const CREATE_NEW = 1
Const OPEN_ALWAYS = 4
Const OPEN_EXISTING = 3
Const TRUNCATE_EXISTING = 5
Const FILE_ATTRIBUTE_ARCHIVE = &H20
Const FILE_ATTRIBUTE_HIDDEN = &H2
Const FILE_ATTRIBUTE_NORMAL = &H80
Const FILE_ATTRIBUTE_READONLY = &H1
Const FILE_ATTRIBUTE_SYSTEM = &H4
Const FILE_FLAG_DELETE_ON_CLOSE = &H4000000
Const FILE_FLAG_NO_BUFFERING = &H20000000
Const FILE_FLAG_OVERLAPPED = &H40000000
Const FILE_FLAG_POSIX_SEMANTICS = &H1000000
Const FILE_FLAG_RANDOM_ACCESS = &H10000000
Const FILE_FLAG_SEQUENTIAL_SCAN = &H8000000
Const FILE_FLAG_WRITE_THROUGH = &H80000000

Function GetDateValue(fName As String) As String
'returns UTC (GMT) file time for specified file

    Dim hFile As Long ' handle to the opened file
    Dim ctime As FILETIME ' receives time of creation
    Dim atime As FILETIME ' receives time of last access
    Dim mtime As FILETIME ' receives time of last modification
    Dim Thetime As SYSTEMTIME ' used to manipulate the time
    Dim retval As Long ' return value

    hFile = CreateFile(fName, GENERIC_READ, FILE_SHARE_READ, _
        ByVal CLng(0), OPEN_EXISTING, FILE_ATTRIBUTE_ARCHIVE, 0)

    retval = GetFileTime(hFile, ctime, atime, mtime)

    'Choose which date to return: creation, modify or access date
    'retval = FileTimeToSystemTime(ctime, Thetime) 'extract creation datetime
    retval = FileTimeToSystemTime(mtime, Thetime) 'extract modified datetime
    'retval = FileTimeToSystemTime(atime, Thetime) 'extract accessed datetime

    retval = CloseHandle(hFile)

    With Thetime
        GetDateValue = .wYear & Format(.wMonth, "\-00") & _
            Format(.wDay, "\-00") & " " & Format(.wHour, "00") & _
            Format(.wMinute, "\:00") & Format(.wSecond, "\:00") & _
            Format(.wSecond, "\.000")
    End With
End Function

Sub test()
    MsgBox GetDateValue("c:\logfile.txt") 
    'returns a string like "2018-03-31 16:13:52.052"
End Sub

I'm just pasting this here, it isn't perfect but it works and can be adjusted to your individual needs. Note that you need to manually uncomment the line for which datetime you want the function to return.

Be sure to read up before you use this for anything important because there are limitations depending on your file system and more. For example, NTFS will often finish writing a file after you "think" it's finished... up to 1 hour later.

More Information:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105