Finding atribute LastAccessedDate of a file and make it a Excel range value

[blaksnm]

Hey guys
Almost Xmas and Santa will come! I hope ...
Have I been a good guy? Think so and can make a wish

I want to place the atribute (Date as value): "LastAccessDate" of a file called "!FileSessionStart.nwd" in Sheet1.Cell A2 in my workbook - without opening/accessing" this "C:/so-and-so/!FileSessionStart.nwd" - just want to read and use the last accessed date of it.

How will a macro go finding this atribute of this particular file and place this atribute as value as described?
The filepath of it is valued in celle A1

Anyone feel for giving me a little help here?
I would certainly appreciate it - cause I'm lost!

Best Regards
Snoopy

 

[MickG]

Hi, Your experience with the Double click is not the same as Mine!!, But if you use the format below you should be able to change the Date to a Number.
example:-

Range("E1") = CDbl(DateValue(File.DateLastAccessed))

Regards Mick

 

[blaksnm]

Hey
This helped me a lot
No I want to expand the info with "Owner" - will you guide me?

 

[blaksnm]

This should be better than a lump of coal:

'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 AM/PM")
        Else
            FileDate = "(no date)"
        End If
    End If
End Function

Function ShowFileInfo(FullName As String)
'   This subroutine demonstrates the technique
    Dim hFile As Long
    Dim WFD As WIN32_FIND_DATA
    Dim FullName2 As String
    Dim Modified 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
        Modified = FileDate(WFD.ftLastAccessTime)
        ShowFileInfo = Modified
'        MsgBox "File Created: " & Modified, vbInformation, FullName
    Else
        ShowFileInfo = vbNullString
        MsgBox "File not found.", vbCritical, FullName
    End If
End Function
 
Sub TestFileInfo()
    
    Range("D10") = ShowFileInfo("C:\Data\Temp\Book1.xls")
    
End Sub

Copy & paste the code above into a regular code module and then use the macro named TestFileInfo to test it out.

To btadams:
Your help was very useful
I'm a VBA-novice and construct my macros almost by lucky lumps
Based on your macro I want to supply the list with property: "Owner".
Many have tried to help me, but I dont understand quite The hints they are giving.
So I ask you again for a little help.
Where - and how - do I complete your macro to make the list including property: Owner?

 

[btadams]

Unfortunately, the Owner (or Author) property isn't part of the system properties, it is part of the workbook built-in properties and the workbook will need to be opened to get this info. The macro below will list all the built-in properties for the active workbook:

Sub WBProperties()
rw = 1
On Error Resume Next
For Each p In ActiveWorkbook.BuiltinDocumentProperties
    Cells(rw, 1).Value = p.Name
    Cells(rw, 2).Value = p.Value
    rw = rw + 1
Next

End Sub

 

[blaksnm]

Thanks a lot!
Have a nice weekend!