Retrieve the file create date

A

AdamBrighton

Hi
I get a lot of log files created each day that I take into Excel, tweak
the cells and then export to a database.
These file are all created at 00:01 each day and then at the end of the
week emailed to me.
I then create a new column in A and by hand type in the date the file
was created (they have the date in the title) into cell A2, I then have
a macro that copies the date to the rest of the A column depending on
how big the spreadsheet is.

There must be a way of getting the date from either the title or the
date the file was created so I can put the whole thing in a formula, it
would save me a bunch of time and mistakes. I get about 90 per day.

PS. I can't get the date added before they are sent.

Any ideas?
 
G

Gary''s Student

Try a variation of:

Sub garbit()
Dim fs, f
Dim st, s As String
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("C:\poly.xls")
s = "Created: " & f.DateCreated
MsgBox s
st = Split(s, " ")
MsgBox (st(1))
End Sub
 
A

AdamBrighton

Thanks for that Gary''s Student

With the tweaking it does give me the info I want, but I can't seem t
integrate it into macros, I can write VB for toffee.

However I have found the solution I need, kind of, I mentioned that th
date was in the filename so with the following command =CELL("filename"
I get the filename into a cell.
Then with the following command I strip out the date and convert i
from US to UK.

=CONCATENATE(LEFT(LEFT(RIGHT(CELL("filename"),33),8),2),"/",MID(LEFT(RIGHT(CELL("filename"),33),8),4,2),"/20",RIGHT(LEFT(RIGHT(CELL("filename"),33),8),2))

Thanks anywa
 
G

Gary''s Student

Thank you for the worksheet solution. I'll add it to my mental toolkit.



b.t.w
A little caution

CELL("filename") returns information from the active worksheet.
CELL("filename",A1) returns info from the sheet containing the formula.
 
Top