Retrieve the file create date
 

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?


--
AdamBrighton
------------------------------------------------------------------------
AdamBrighton's Profile: http://www.excelforum.com/member.php...o&userid=34838
View this thread: http://www.excelforum.com/showthread...hreadid=545856

Retrieve the file create date
 

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


--
Gary''s Student


"AdamBrighton" wrote:


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?


--
AdamBrighton
------------------------------------------------------------------------
AdamBrighton's Profile: http://www.excelforum.com/member.php...o&userid=34838
View this thread: http://www.excelforum.com/showthread...hreadid=545856

Retrieve the file create date
 

Thanks for that Gary''s Student

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

However I have found the solution I need, kind of, I mentioned that the
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 it
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 anyway


--
AdamBrighton
------------------------------------------------------------------------
AdamBrighton's Profile: http://www.excelforum.com/member.php...o&userid=34838
View this thread: http://www.excelforum.com/showthread...hreadid=545856

Retrieve the file create date
 

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.
--
Gary''s Student


"AdamBrighton" wrote:


Thanks for that Gary''s Student

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

However I have found the solution I need, kind of, I mentioned that the
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 it
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 anyway


--
AdamBrighton
------------------------------------------------------------------------
AdamBrighton's Profile: http://www.excelforum.com/member.php...o&userid=34838
View this thread: http://www.excelforum.com/showthread...hreadid=545856