Excel API

Change File Date Time

Not too long ago I brought a new computer with all the bells and whistles.

Pentium IV 2.8 Mghz, 512 Meg ram, 60 Gig HD, 40X12X48 CDRW, 64 Meg Graphics card etc (By the time you read this it will be out dated !! :-) )

In my excitement to get working with it, I forgot to set the system date/Time,

so for a while I had it set about 12 months ahead !! As you can imagine some of my files had the incorrect dates. This set me thinking about a way to set the date/time for a file (any file) to change these back, so i did a little

reseach and reading and gathering of some code (amended).

Here is what I came up with.

(Yes I know, I could have just opened and resaved, but the research and testing teaches you a lot, something you can't get by just reading)

APIs used were;

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

You will note that the above 3 functions require a UDT called SYSTEMTIME

and FILETIME. These are defined as follows and are gained from the C Header files structure.If you read my take on what an API is, you will know that they are function calls to dynamic link librarys made in C/C++ language.

The C header files structure for SYSTEMTIME looks like this;

typedef struct _SYSTEMTIME { // st

WORD wYear;

WORD wMonth;

WORD wDayOfWeek;

WORD wDay;

WORD wHour;

WORD wMinute;

WORD wSecond;

WORD wMilliseconds;

}SYSTEMTIME;

The WORD parameter in C is a 16 bit unsigned value ie 65536.

Although in VBA an Integer variable is stored as 16-bit (2-byte) numbers

ranging in value from -32,768 to 32,767 ie a signed value, we can use this.

So the VBA Type defintion looks like this;

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

Obviously all this is done for you in the Win32API text, but this was just to show you where the type declarations come from. Having an insight into how the low level language is constructed gives you a better understanding on how the functions are called, what the paremeters are and what data types it is expecting. Don't forget you can't debug API calls and getting it wrong could cause problems. One of the habits I have got into now is to press the SAVE icon after a few changes and before any testing. This has saved me a lot of frustration :-)