Change Filename Date and Time thru Excel VBA


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.

Sanjay GandhiFounder, KenhalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Bill PrewIT / Software Engineering ConsultantCommented:
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.

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:

   dwLowDateTime As Long
   dwHighDateTime As Long
End Type
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.


' OpenFile() Structure
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

   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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sanjay GandhiFounder, KenhalAuthor Commented:
Yes, missed it. It's the file modified I need to update.
Bill PrewIT / Software Engineering ConsultantCommented:
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...

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 ConsultantCommented:
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

@Bill Prew… Wow, How do you have a drive B?!? Don't tell me you have a flopply disk!
Bill PrewIT / Software Engineering ConsultantCommented:
@Bill Prew… Wow, How do you have a drive B?!? Don't tell me you have a flopply disk!

Mapped drive...

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

Thanks, great.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.