Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to retrieve orignial file excel that is renamed | Excel Worksheet Functions | |||
Open an Excel file with a date as today in a macro | Excel Discussion (Misc queries) | |||
Insert date modified of external file | Excel Discussion (Misc queries) | |||
auto create a file | Excel Discussion (Misc queries) | |||
Unable to create any link/hyperlinks in a particular excel file | Excel Discussion (Misc queries) |