Getting timestamp from a file in Excel

  • Thread starter Thread starter Patrick
  • Start date Start date
P

Patrick

Hi,

I'm looking for a macro/function that will allow me to get the
timestamps properties (date created) for a file in Excel and paste this
into a particular cell.

I imagined the set up would be something like:

Cell A1 - File location
Cell B1 - File name
Cell C1 - =Today()
Cell D1 - Some formula/macro/function that says "If the datestamp of
A1&B1 is the same as C1 then put a tick"

Can anyone help?

Many Thanks
Patrick
 
You can get the creation date in VBA via:

activeworkbook.BuiltinDocumentProperties("creation date")
 
I am not all that familiar with VBA. Could anyone give me a step by ste
to getting this to work. I tried on my own first but was unsucessful.

Thanks
 
This should get you started:

Look in VBA help under BuiltinDocumentProperties for more details. You
might like to change what sh and wb are in order that you don't write
over existing data and you report the details of the correct file.

HTH,
Gareth

Sub GetFileDetails()

Dim wb As Workbook
Dim sh As Worksheet

Set sh = ActiveSheet
Set wb = ActiveWorkbook

With sh
.Cells(1, 1).Value = "File name:"
.Cells(1, 2).Value = wb.Name
.Cells(2, 1).Value = "Location:"
.Cells(2, 2).Value = wb.Path
.Cells(3, 1).Value = "Created:"
.Cells(3, 2).Value = Format( _
wb.BuiltinDocumentProperties("creation date"), _
"dd mmm yyyy")
.Cells(4, 1).Value = "Created Today?"
.Cells(4, 2).Value = Date = DateSerial( _
Year(wb.BuiltinDocumentProperties("creation date")), _
Month(wb.BuiltinDocumentProperties("creation date")), _
Day(wb.BuiltinDocumentProperties("creation date")))

End With

Set wb = Nothing
Set sh = Nothing

End Sub
 
Back
Top