Retrieve the file create date

[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?

[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


--
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
>
>

[AdamBrighton]

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

[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.
--
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
>
>