Any way for VBA to change file modified property

[dnickelson]

Is there any way for VBA to modify file properties directly, as in changing the DateLastModified for a specific file?

I am running a macro that copy contents from a file (one at a time from a large list), modifies the contents and writes it back to the file (actually just deletes the original and creates a file with the same name). The problem is that the files were each originally created on days specific to an incident occurring. The file name contains the date and time of creation, but before modifying the files, I could sort them in descending order by date, but now all the files are modified when the macro runs. I'd like to take the date information I have and directly adjust the Last Modified Date for each file. Possible?

 

[zilpher]

Hi

Here's some code that will do what you ask. It uses the SetFileTime API. Place this code in your work book and call it as I have shown in the example sub foo:

Option Explicit

Private Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

Private 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

Private Const GENERIC_WRITE = &H40000000
Private Const OPEN_EXISTING = 3
Private Const FILE_SHARE_READ = &H1
Private Const FILE_SHARE_WRITE = &H2
Private Declare Function CreateFile Lib "kernel32" Alias "CreateFileA" (ByVal lpFileName As String, ByVal dwDesiredAccess As Long, ByVal dwShareMode As Long, ByVal lpSecurityAttributes As Long, ByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As Long, ByVal hTemplateFile As Long) As Long
Private Declare Function SetFileTime Lib "kernel32" (ByVal hFile As Long, lpCreationTime As FILETIME, lpLastAccessTime As FILETIME, lpLastWriteTime As FILETIME) As Long
Private Declare Function SystemTimeToFileTime Lib "kernel32" (lpSystemTime As SYSTEMTIME, lpFileTime As FILETIME) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function LocalFileTimeToFileTime Lib "kernel32" (lpLocalFileTime As FILETIME, lpFileTime As FILETIME) As Long
Private Declare Function GetLastError Lib "kernel32" () As Long

Private Function AdjustFileTime(strFilePath As String, WriteFileDate As Date, CreateFileDate As Date, AccessFileDate As Date) As Long
Dim NewWriteDate As Date, NewCreateDate As Date, NewAccessDate As Date, lngHandle As Long

Dim udtWriteTime As FILETIME
Dim udtCreateTime As FILETIME
Dim udtAccessTime As FILETIME

Dim udtSysCreateTime As SYSTEMTIME
Dim udtSysAccessTime As SYSTEMTIME
Dim udtSysWriteTime As SYSTEMTIME

Dim udtLocalCreateTime As FILETIME
Dim udtLocalAccessTime As FILETIME
Dim udtLocalWriteTime As FILETIME

NewCreateDate = Format(CreateFileDate, "DD-MM-YY HH:mm:SS")
NewAccessDate = Format(AccessFileDate, "DD-MM-YY HH:mm:SS")
NewWriteDate = Format(WriteFileDate, "DD-MM-YY HH:mm:SS")

With udtSysCreateTime
    .wYear = Year(NewCreateDate)
    .wMonth = Month(NewCreateDate)
    .wDay = Day(NewCreateDate)
    .wDayOfWeek = Weekday(NewCreateDate) - 1
    .wHour = Hour(NewCreateDate)
    .wMinute = Minute(NewCreateDate)
    .wSecond = Second(NewCreateDate)
    .wMilliseconds = 0
End With

With udtSysAccessTime
    .wYear = Year(NewAccessDate)
    .wMonth = Month(NewAccessDate)
    .wDay = Day(NewAccessDate)
    .wDayOfWeek = Weekday(NewAccessDate) - 1
    .wHour = Hour(NewAccessDate)
    .wMinute = Minute(NewAccessDate)
    .wSecond = Second(NewAccessDate)
    .wMilliseconds = 0
End With

With udtSysWriteTime
    .wYear = Year(NewWriteDate)
    .wMonth = Month(NewWriteDate)
    .wDay = Day(NewWriteDate)
    .wDayOfWeek = Weekday(NewWriteDate) - 1
    .wHour = Hour(NewWriteDate)
    .wMinute = Minute(NewWriteDate)
    .wSecond = Second(NewWriteDate)
    .wMilliseconds = 0
End With
Dim ret As Long
ret = SystemTimeToFileTime(udtSysCreateTime, udtLocalCreateTime)
If ret <> 1 Then Err.Raise GetLastError
ret = LocalFileTimeToFileTime(udtLocalCreateTime, udtCreateTime)
If ret <> 1 Then Err.Raise GetLastError

ret = SystemTimeToFileTime(udtSysAccessTime, udtLocalAccessTime)
If ret <> 1 Then Err.Raise GetLastError
ret = LocalFileTimeToFileTime(udtLocalAccessTime, udtAccessTime)
If ret <> 1 Then Err.Raise GetLastError

ret = SystemTimeToFileTime(udtSysWriteTime, udtLocalWriteTime)
If ret <> 1 Then Err.Raise GetLastError
ret = LocalFileTimeToFileTime(udtLocalWriteTime, udtWriteTime)
If ret <> 1 Then Err.Raise GetLastError

lngHandle = CreateFile(strFilePath, GENERIC_WRITE, FILE_SHARE_READ Or FILE_SHARE_WRITE, ByVal 0&, OPEN_EXISTING, 0, 0)
If lngHandle = -1 Then Err.Raise 53

'                                 create,      access,      write
ret = SetFileTime(lngHandle, udtCreateTime, udtAccessTime, udtWriteTime)
CloseHandle lngHandle
AdjustFileTime = 1
If ret <> 1 Then Err.Raise GetLastError

End Function

Sub foo()
Dim i As Long
On Error GoTo ERR_HANDLER
i = AdjustFileTime("c:\test.txt", CDate("12/12/2004 20:09:26"), CDate("11/12/2004 20:09:26"), CDate("10/12/2004 20:09:26"))
Exit Sub
ERR_HANDLER:
MsgBox Err.Description
On Error GoTo 0
End Sub

There doesn't seem to be a lot of point in adjusting the LastAccess time as it always sets to the time you run the code (makes sense I s'pose). I don't experience this feature on my Win2k box at work, here at home it's XP so maybe it's that; you could always adjust the system clock first. Not sure but I'm off to bed :D

HTH

 

[tusharm]

{LOL} Assuming this works -- and I have no intention of trying it -- talk about a major breach of security!

Hi

Here's some code that will do what you ask. It uses the SetFileTime API. Place this code in your work book and call it as I have shown in the example sub foo:
{snip}

 

[zilpher]

What's your point Tushar? I'm not sure why you consider changing a files date/time a breach of security, particularly when it's the requirement.

The SetFileTime API is a documented function (google it), used by windows to set any and all files times. Sure you can fake when a file was created and written to, but no one should be using that information like it was bullet proof, just change your system clock if you want to get round it.

There is an app called touch.exe which you can download from many places on the web, it's a clone of the unix utility of the same name; it does the same as the code above.

The code does work dnickelson; try it, it's fine.

 

[Tom Urtis]

Is there any way for VBA to modify file properties directly, as in changing the DateLastModified for a specific file?

If you are interested in a utilty that can do this, see this thread:
http://216.92.17.166/board2/viewtopic.php?t=82182&highlight=propertiesplus

 

[tusharm]

Apparently, you read my post differently than it was intended. I wasn't challenging you on it. It was simply a comment on how fragile the Windows OS is.

How can it possibly be a 'requirement' to change the LastModified timestamp if the file wasn't really modified at that time?

Some years ago I worked on a system that the vendor touted as the civilian version of something that met US government security standards. A tiny portion of the security considerations: only the OS could change file timestamps such as CreationTimestamp, LastModifiedTimestamp, LastAccessedTimestamp, LastExecutedTimestamp, etc. Further, only a program granted 'compiler' status could change the CompiledTimestamp or, for that matter, mark a file as an 'executable'.

What's your point Tushar? I'm not sure why you consider changing a files date/time a breach of security, particularly when it's the requirement.

The SetFileTime API is a documented function (google it), used by windows to set any and all files times. Sure you can fake when a file was created and written to, but no one should be using that information like it was bullet proof, just change your system clock if you want to get round it.

There is an app called touch.exe which you can download from many places on the web, it's a clone of the unix utility of the same name; it does the same as the code above.

The code does work dnickelson; try it, it's fine.

 

[zilpher]

Tushar

I didn't take offence at all, I genuinely didn't understand what you meant, sorry for the confusion there.

The reason I use this code is to allow rollout of software to happen. My client is a very large company in the UK, I write code that gets rolled out to the client machines via their preferred electronic method, which at logon compares the filedatetime of a list of files on each machine against a delta file, if there is a newer file available, it will download and register it.

I personally think this is more than a bit crappy, particularly as the utility will give the file a created date of the moment it copied it to the machine. This causes lots of problems, eg:

I rollout version two of ProductA.dll on Jan 5th 2003, any machine that logs on gets the file because the filedatetime is newer.

In April 2003, ProductA.dll gets some new functionality, it goes into user acceptance test for 8 weeks, passes and waits for a sutiable time for a rollout to live.

In May 2003, a client is logged on for the first time since Jan 5th 2003, it gets ProductA.dll (with a created date of May 2003)

In July 2003, my new version of ProductA.dll is placed on the delivery machine, it has a date of April 2003. The client above would appear to have a newer version so it is ignored.

I use the code above to move the date of the client versions back by one year to ensure they will be replaced when I want them to.

In reality my client should use a different method of rollout, but their config management guy loves what they have and is not one for change so I am stuck with changing the dates.

If you take a look here http://www.helge.mynetcologne.de/touch/ there is another example where sys admins use this approach to force profiles to be refreshed.

Considering the utility you spoke of, I can understand your security concern, again sorry if you thought I was challenging you, that's not what I meant at all.

 

[tusharm]

Thanks for that explanation. Yes, you are right. If the utiity retained the original timestamps, it would easily identify the correct file as the latest. Of course, I suspect that had MS disallowed user changes to timestamps, your client would have developed a different rollout method.

On the other hand, I rely on the timestamps being legit to coordinate files across computers and across people. Luckily, when Windows copies a file (even across a network) it retains the timestamps. So, I can compare which person and what computer has the 'latest' version of a file and use it to sync all the others.

Tushar

I didn't take offence at all, I genuinely didn't understand what you meant, sorry for the confusion there.

The reason I use this code is to allow rollout of software to happen. My client is a very large company in the UK, I write code that gets rolled out to the client machines via their preferred electronic method, which at logon compares the filedatetime of a list of files on each machine against a delta file, if there is a newer file available, it will download and register it.

I personally think this is more than a bit crappy, particularly as the utility will give the file a created date of the moment it copied it to the machine. This causes lots of problems, eg:

{snip{

 

[dnickelson]

I sent zilpher a pm on this, but can anyone tell why the above code transposes the day and the year in the file modified time if used with a day greater than 12? ex 7/12/2004 gives a file modified date of 7/12/2004, but 7/13/2004 gives a file modified date of 7/4/2013 (or more appropriately, the dates 12/7/2004 and 13/7/2004, as the syntax translates to dd/mm/yyyy).

Thanks.

 

[zilpher]

I haven't been able to recreate the problem you speak of but I suspect it's down to the file date system on our two machines. Being a limey Brit I use dd/mm/yyyy whereas as you are in the states you probably use mm/dd/yyyy.

I tried using the 'wrong' file system for my machine but didn't get the same problem, but I would suggest you try changing the AdjustFileTime function thus:

'NewCreateDate = Format(CreateFileDate, "DD-MM-YY HH:mm:SS")
'NewAccessDate = Format(AccessFileDate, "DD-MM-YY HH:mm:SS")
'NewWriteDate = Format(WriteFileDate, "DD-MM-YY HH:mm:SS")
NewCreateDate = Format(CreateFileDate, "MM-DD-YY HH:mm:SS")
NewAccessDate = Format(AccessFileDate, "MM-DD-YY HH:mm:SS")
NewWriteDate = Format(WriteFileDate, "MM-DD-YY HH:mm:SS")

I am not confident this will work as a date is a date and then the OS sorts out the display.

Can you post your code?

If I could recreate it it'd be easier to fix, hope this helps

Z