Comment connaître la date de création d'1 classeur dans 1 macro SVP ?
Merci
anonymousA
bonjour,
tiré du site d'un éminent Exceleur (John Walkenbach).
Determining When a File Was Created You probably know that you find out when a file was created by right-clicking the file name in the Windows Explorer, clicking Properties on the context menu.
If you're in Excel, you can determine the creation date of the active workbook by selecting Properties from the File menu. The file creation date appears twice in the Properties dialog box: on the General tab, and on the Statistics tab. In many cases these two dates are different!
The file creation date and time on the Statistics tab is when the file was originally created. The file creation date and time on the General tab is when the file was first saved on your computer. You can use the VBA statement below to examine the actual file creation date and time (the date and time shown in the Statistics tab):
MsgBox ActiveWorkbook.BuiltinDocumentProperties.Item _ ("Creation date").Value If you'd like to determine the date and time that the file was saved on your computer, you can use the routines listed below. The result is the same date and time that is displayed in the General tab of the Properties dialog box.
You'll need to copy all of the code below to a module. The ShowFile subroutine displays the file creation date and time for the active workbook. You can easily customize this subroutine to show the creation date for any file.
VBA Code '32 bit Windows declarations Public Declare Function FindFirstFile Lib "kernel32" Alias "FindFirstFileA" _ (ByVal lpFileName As String, lpFindFileData As WIN32_FIND_DATA) As Long Public Declare Function FileTimeToSystemTime Lib "kernel32" _ (lpFileTime As FILETIME, lpSystemTime As SYSTEMTIME) As Long Declare Function FileTimeToLocalFileTime Lib "kernel32" _ (lpFileTime As FILETIME, lpLocalFileTime As FILETIME) As Long
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 Long End Type
Public Type WIN32_FIND_DATA dwFileAttributes As Long ftCreationTime As FILETIME ftLastAccessTime As FILETIME ftLastWriteTime As FILETIME nFileSizeHigh As Long nFileSizeLow As Long dwReserved0 As Long dwReserved1 As Long cFileName As String * 260 cAlternate As String * 14 End Type
Private Function FileDate(FT As FILETIME) As String ' convert the FILETIME to LOCALTIME, then to SYSTEMTIME type Dim ST As SYSTEMTIME Dim LT As FILETIME Dim t As Long Dim ds As Double Dim ts As Double t = FileTimeToLocalFileTime(FT, LT) t = FileTimeToSystemTime(LT, ST) If t Then ds = DateSerial(ST.wYear, ST.wMonth, ST.wDay) ts = TimeSerial(ST.wHour, ST.wMinute, ST.wSecond) ds = ds + ts If ds > 0 Then FileDate = Format$(ds, "mm/dd/yy hh:mm:ss") Else FileDate = "(no date)" End If End If End Function
Private Sub ShowFileInfo() ' This subroutine demonstrates the technique Dim hFile As Long Dim WFD As WIN32_FIND_DATA Dim FullName As String Dim Created As String Dim LastWrite As String
' FullName is the path and filename ' Substitute any valid file and path FullName = ActiveWorkbook.FullName hFile = FindFirstFile(FullName, WFD)
If hFile > 0 Then Created = FileDate(WFD.ftCreationTime) MsgBox "File Created: " & Created, vbInformation, FullName Else MsgBox "File not found.", vbCritical, FullName End If End Sub
A+
Bonjour à tous,
Comment connaître la date de création d'1 classeur dans 1 macro SVP ?
Merci
bonjour,
tiré du site d'un éminent Exceleur (John Walkenbach).
Determining When a File Was Created
You probably know that you find out when a file was created by
right-clicking the file name in the Windows Explorer, clicking Properties on
the context menu.
If you're in Excel, you can determine the creation date of the active
workbook by selecting Properties from the File menu. The file creation date
appears twice in the Properties dialog box: on the General tab, and on the
Statistics tab. In many cases these two dates are different!
The file creation date and time on the Statistics tab is when the file was
originally created.
The file creation date and time on the General tab is when the file was
first saved on your computer.
You can use the VBA statement below to examine the actual file creation date
and time (the date and time shown in the Statistics tab):
MsgBox ActiveWorkbook.BuiltinDocumentProperties.Item _
("Creation date").Value
If you'd like to determine the date and time that the file was saved on your
computer, you can use the routines listed below. The result is the same date
and time that is displayed in the General tab of the Properties dialog box.
You'll need to copy all of the code below to a module. The ShowFile
subroutine displays the file creation date and time for the active workbook.
You can easily customize this subroutine to show the creation date for any
file.
VBA Code
'32 bit Windows declarations
Public Declare Function FindFirstFile Lib "kernel32" Alias "FindFirstFileA" _
(ByVal lpFileName As String, lpFindFileData As WIN32_FIND_DATA) As Long
Public Declare Function FileTimeToSystemTime Lib "kernel32" _
(lpFileTime As FILETIME, lpSystemTime As SYSTEMTIME) As Long
Declare Function FileTimeToLocalFileTime Lib "kernel32" _
(lpFileTime As FILETIME, lpLocalFileTime As FILETIME) As Long
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 Long
End Type
Public Type WIN32_FIND_DATA
dwFileAttributes As Long
ftCreationTime As FILETIME
ftLastAccessTime As FILETIME
ftLastWriteTime As FILETIME
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * 260
cAlternate As String * 14
End Type
Private Function FileDate(FT As FILETIME) As String
' convert the FILETIME to LOCALTIME, then to SYSTEMTIME type
Dim ST As SYSTEMTIME
Dim LT As FILETIME
Dim t As Long
Dim ds As Double
Dim ts As Double
t = FileTimeToLocalFileTime(FT, LT)
t = FileTimeToSystemTime(LT, ST)
If t Then
ds = DateSerial(ST.wYear, ST.wMonth, ST.wDay)
ts = TimeSerial(ST.wHour, ST.wMinute, ST.wSecond)
ds = ds + ts
If ds > 0 Then
FileDate = Format$(ds, "mm/dd/yy hh:mm:ss")
Else
FileDate = "(no date)"
End If
End If
End Function
Private Sub ShowFileInfo()
' This subroutine demonstrates the technique
Dim hFile As Long
Dim WFD As WIN32_FIND_DATA
Dim FullName As String
Dim Created As String
Dim LastWrite As String
' FullName is the path and filename
' Substitute any valid file and path
FullName = ActiveWorkbook.FullName
hFile = FindFirstFile(FullName, WFD)
If hFile > 0 Then
Created = FileDate(WFD.ftCreationTime)
MsgBox "File Created: " & Created, vbInformation, FullName
Else
MsgBox "File not found.", vbCritical, FullName
End If
End Sub
A+
Bonjour à tous,
Comment connaître la date de création d'1 classeur dans 1 macro SVP ?
tiré du site d'un éminent Exceleur (John Walkenbach).
Determining When a File Was Created You probably know that you find out when a file was created by right-clicking the file name in the Windows Explorer, clicking Properties on the context menu.
If you're in Excel, you can determine the creation date of the active workbook by selecting Properties from the File menu. The file creation date appears twice in the Properties dialog box: on the General tab, and on the Statistics tab. In many cases these two dates are different!
The file creation date and time on the Statistics tab is when the file was originally created. The file creation date and time on the General tab is when the file was first saved on your computer. You can use the VBA statement below to examine the actual file creation date and time (the date and time shown in the Statistics tab):
MsgBox ActiveWorkbook.BuiltinDocumentProperties.Item _ ("Creation date").Value If you'd like to determine the date and time that the file was saved on your computer, you can use the routines listed below. The result is the same date and time that is displayed in the General tab of the Properties dialog box.
You'll need to copy all of the code below to a module. The ShowFile subroutine displays the file creation date and time for the active workbook. You can easily customize this subroutine to show the creation date for any file.
VBA Code '32 bit Windows declarations Public Declare Function FindFirstFile Lib "kernel32" Alias "FindFirstFileA" _ (ByVal lpFileName As String, lpFindFileData As WIN32_FIND_DATA) As Long Public Declare Function FileTimeToSystemTime Lib "kernel32" _ (lpFileTime As FILETIME, lpSystemTime As SYSTEMTIME) As Long Declare Function FileTimeToLocalFileTime Lib "kernel32" _ (lpFileTime As FILETIME, lpLocalFileTime As FILETIME) As Long
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 Long End Type
Public Type WIN32_FIND_DATA dwFileAttributes As Long ftCreationTime As FILETIME ftLastAccessTime As FILETIME ftLastWriteTime As FILETIME nFileSizeHigh As Long nFileSizeLow As Long dwReserved0 As Long dwReserved1 As Long cFileName As String * 260 cAlternate As String * 14 End Type
Private Function FileDate(FT As FILETIME) As String ' convert the FILETIME to LOCALTIME, then to SYSTEMTIME type Dim ST As SYSTEMTIME Dim LT As FILETIME Dim t As Long Dim ds As Double Dim ts As Double t = FileTimeToLocalFileTime(FT, LT) t = FileTimeToSystemTime(LT, ST) If t Then ds = DateSerial(ST.wYear, ST.wMonth, ST.wDay) ts = TimeSerial(ST.wHour, ST.wMinute, ST.wSecond) ds = ds + ts If ds > 0 Then FileDate = Format$(ds, "mm/dd/yy hh:mm:ss") Else FileDate = "(no date)" End If End If End Function
Private Sub ShowFileInfo() ' This subroutine demonstrates the technique Dim hFile As Long Dim WFD As WIN32_FIND_DATA Dim FullName As String Dim Created As String Dim LastWrite As String
' FullName is the path and filename ' Substitute any valid file and path FullName = ActiveWorkbook.FullName hFile = FindFirstFile(FullName, WFD)
If hFile > 0 Then Created = FileDate(WFD.ftCreationTime) MsgBox "File Created: " & Created, vbInformation, FullName Else MsgBox "File not found.", vbCritical, FullName End If End Sub
A+
Bonjour à tous,
Comment connaître la date de création d'1 classeur dans 1 macro SVP ?
Merci
capkurk
Merci pour votre aide ! "capkurk" a écrit dans le message de news:
Bonjour à tous,
Comment connaître la date de création d'1 classeur dans 1 macro SVP ?
Merci
Merci pour votre aide !
"capkurk" <nospam-patrick.chretien9@wanadoo.fr> a écrit dans le message de
news: OQlXAeyGFHA.3628@TK2MSFTNGP15.phx.gbl...
Bonjour à tous,
Comment connaître la date de création d'1 classeur dans 1 macro SVP ?
