Change Filename Date and Time thru Excel VBA

Sanjay Gandhi
Sanjay Gandhi used Ask the Experts™
on
Hello,

I want a function in Excel VBA, which I should call, and should be able to change passed filename's, date and time (also passed).

For example, if the function is Change_DateTime(NewFile as String, NewDate as Date, NewTime as Date) as Boolean.

Then this function should be able to change date and time for the parameter passed filename.

Hope the problem is clear.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
There are three date/times associated with a file (created, accessed, modified), which one are you wanting to update?


»bp
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
There is no easy way to do this in just pure VBA.  The easiest way to do this is to download a free "touch" utility for Windows, and then just execute the command line utility from VBA.  Grab FileTouch from here, and then run it form VBA with the Shell command passing it the parms needed.


»bp
You have to use Windows API SetFileTime function which you can declare as:

Declare PtrSafe Function SetFileTime Lib "kernel32" (ByVal hFile As LongPtr, lpCreationTime As FILETIME, lpAccessTime As FILETIME, lpLastWriteTime As FILETIME) As LongPtr

The FILETIME type is declared as:

Type FILETIME
   dwLowDateTime As Long
   dwHighDateTime As Long
End Type
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Const OF_READWRITE = &H2

' OpenFile() Structure
Const OFS_MAXPATHNAME = 128
Public Type OFSTRUCT
        cBytes As Byte
        fFixedDisk As Byte
        nErrCode As Integer
        Reserved1 As Integer
        Reserved2 As Integer
        szPathName(0 To OFS_MAXPATHNAME - 1) As Byte
End Type

Public Type FILETIME
   dwLowDateTime As Long
   dwHighDateTime As Long
End Type

Public 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

Public Type TIME_ZONE_INFORMATION
   Bias As Long
   StandardName(0 To 31) As Integer
   StandardDate As SYSTEMTIME
   StandardBias As Long
   DaylightName(0 To 31) As Integer
   DaylightDate As SYSTEMTIME
   DaylightBias As Long
End Type

Public Declare PtrSafe Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
Public Declare PtrSafe Function OpenFile Lib "kernel32" (ByVal lpFileName As String, lpReOpenBuff As OFSTRUCT, ByVal wStyle As Long) As LongPtr
Public Declare PtrSafe Function SetFileTime Lib "kernel32" (ByVal hFile As LongPtr, lpCreationTime As FILETIME, lpLastAccessTime As FILETIME, lpLastWriteTime As FILETIME) As Long
Public Declare PtrSafe Function TzSpecificLocalTimeToSystemTime Lib "kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION, lpLocalTime As SYSTEMTIME, lpUniversalTime As SYSTEMTIME) As Long
Public Declare PtrSafe Function SystemTimeToFileTime Lib "kernel32" (lpSystemTime As SYSTEMTIME, lpFileTime As FILETIME) As Long
Public Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As LongPtr) As Long

Public Function Change_DateTime(NewFile As String, NewDate As Date, NewTime As Date) As Boolean
   Dim FileData As OFSTRUCT
   Dim LocalFileTime As FILETIME
   
   Dim TimezoneInfo As TIME_ZONE_INFORMATION
   Dim SysTime As SYSTEMTIME, LocalSysTime As SYSTEMTIME
   
   Dim CreationTime As FILETIME
   Dim AccessTime As FILETIME
   Dim hFile As LongPtr
   Dim result As Long
   
   LocalSysTime.wYear = Year(NewDate)
   LocalSysTime.wMonth = Month(NewDate)
   LocalSysTime.wDay = Day(NewDate)
   LocalSysTime.wDayOfWeek = Weekday(NewDate) - 1
   LocalSysTime.wHour = Hour(NewTime)
   LocalSysTime.wMinute = Minute(NewTime)
   LocalSysTime.wSecond = Second(NewTime)
   LocalSysTime.wMilliseconds = 0
   
   GetTimeZoneInformation TimezoneInfo
   TzSpecificLocalTimeToSystemTime TimezoneInfo, LocalSysTime, SysTime
   
   SystemTimeToFileTime SysTime, LocalFileTime
   hFile = OpenFile(NewFile, FileData, OF_READWRITE)
   CreationTime.dwHighDateTime = 0
   CreationTime.dwLowDateTime = 0
   AccessTime.dwHighDateTime = 0
   AccessTime.dwLowDateTime = 0
   result = SetFileTime(hFile, CreationTime, AccessTime, LocalFileTime)
   CloseHandle hFile
   
   Change_DateTime = result
End Function

Open in new window

Sanjay GandhiFounder, Kenhal

Author

Commented:
Yes, missed it. It's the file modified I need to update.
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Okay, looks like you have some code to explore, let us know how you make out.  Watch out for any 32 bit versus 64 bit differences in some of the VBA code posted, sometimes that can be a stumbling block...


»bp
Let me explain my codes going backward:

Like I said above, you neet SetFileTime to change File Timestamp. But SetFileTime requires a FILETIME type, and we need a function to translate Date and Time to FILETIME.

SystemTimeToFileTime will do that for you. Unfortunately, that function requires your Date and Time in UTC time zone. So we need another function to convert Local Time to UTC Time.

TzSpecificLocalTimeToSystemTime is your function. And it requires Time Zone Informations (in a TIME_ZONE_INFORMATION type), which is stored in your Windows OS, and which you can retrieve by using function GetTimeZoneInformation.

Finally, function OpenFile is to open your file to change Timestamp and CloseHandle is to close your file.

*** One final note, the codes above should work with both 32-bit and 64-bit systems with the keywords PtrSafe and LongPtr in the Declare statements.
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Seemed to work here using the code above.  I did the following and it set the last changed date/time.

Sub Test()
    Change_DateTime "B:\EE\EE29100432\testfile.txt", Now(), Now()
End Sub

Open in new window


»bp
@Bill Prew… Wow, How do you have a drive B?!? Don't tell me you have a flopply disk!
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
@Bill Prew… Wow, How do you have a drive B?!? Don't tell me you have a flopply disk!

Mapped drive...


»bp
Sanjay GandhiFounder, Kenhal

Author

Commented:
Slight tweaking as per the requirement, and the code runs fine.

Thanks, great.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial