Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creation date in cell
Hi
I posted this last week and now I can't find the replies in Outlook - only online! The file that I am working on is an imported .txt and the macro that I am using crashes with a Run-time error 5 - Invalid procedure call or argument. The line I'm trying to use is this: Range("A2").Value = ActiveWorkbook.BuiltinDocumentProperties("Creation Date") and I'm wondering if it's because the sheet is not technically a workbook. The macro imports a .txt file, deletes junk from it and formats some of the columns and I need to get the creation date of the txt file. The response I had was to try ("Creation Date"). I have tried this on a few worksheets, using the immediate window. I get the same date and time for 2 of the sheets (even though they weren't created at the same time) and when I try it on my txt file I get Runtime error '-2147467259 (80004005)'. Automation error. Unspecified error. A search for this on the net doesn't help. Thanks.. Andy. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creation date in cell
Try:
Sub garbit() Dim fs, f Dim st, s As String Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFile("C:\dummy.txt") s = "Created: " & f.DateCreated MsgBox s st = Split(s, " ") MsgBox (st(1)) End Sub This should get you the creation date of any file ( here dummy.txt). Just change the GetFile argument. -- Gary''s Student "AndyB" wrote: Hi I posted this last week and now I can't find the replies in Outlook - only online! The file that I am working on is an imported .txt and the macro that I am using crashes with a Run-time error 5 - Invalid procedure call or argument. The line I'm trying to use is this: Range("A2").Value = ActiveWorkbook.BuiltinDocumentProperties("Creation Date") and I'm wondering if it's because the sheet is not technically a workbook. The macro imports a .txt file, deletes junk from it and formats some of the columns and I need to get the creation date of the txt file. The response I had was to try ("Creation Date"). I have tried this on a few worksheets, using the immediate window. I get the same date and time for 2 of the sheets (even though they weren't created at the same time) and when I try it on my txt file I get Runtime error '-2147467259 (80004005)'. Automation error. Unspecified error. A search for this on the net doesn't help. Thanks.. Andy. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creation date in cell
Thanks for that. The slight snag is that I need this macro to run
automatically on a number of different files - and I don't want to have to edit the macro for each one. Cheers. "Gary''s Student" wrote in message ... Try: Sub garbit() Dim fs, f Dim st, s As String Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFile("C:\dummy.txt") s = "Created: " & f.DateCreated MsgBox s st = Split(s, " ") MsgBox (st(1)) End Sub This should get you the creation date of any file ( here dummy.txt). Just change the GetFile argument. -- Gary''s Student "AndyB" wrote: Hi I posted this last week and now I can't find the replies in Outlook - only online! The file that I am working on is an imported .txt and the macro that I am using crashes with a Run-time error 5 - Invalid procedure call or argument. The line I'm trying to use is this: Range("A2").Value = ActiveWorkbook.BuiltinDocumentProperties("Creation Date") and I'm wondering if it's because the sheet is not technically a workbook. The macro imports a .txt file, deletes junk from it and formats some of the columns and I need to get the creation date of the txt file. The response I had was to try ("Creation Date"). I have tried this on a few worksheets, using the immediate window. I get the same date and time for 2 of the sheets (even though they weren't created at the same time) and when I try it on my txt file I get Runtime error '-2147467259 (80004005)'. Automation error. Unspecified error. A search for this on the net doesn't help. Thanks.. Andy. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creation date in cell
O.K. then...
Sub garbit() Dim fs, f Dim st, s As String Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFile("C:\dummy.txt") s = "Created: " & f.DateCreated st = Split(s, " ") Range("A1").Value = st(1) End Sub -- Gary''s Student "AndyB" wrote: Thanks for that. The slight snag is that I need this macro to run automatically on a number of different files - and I don't want to have to edit the macro for each one. Cheers. "Gary''s Student" wrote in message ... Try: Sub garbit() Dim fs, f Dim st, s As String Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFile("C:\dummy.txt") s = "Created: " & f.DateCreated MsgBox s st = Split(s, " ") MsgBox (st(1)) End Sub This should get you the creation date of any file ( here dummy.txt). Just change the GetFile argument. -- Gary''s Student "AndyB" wrote: Hi I posted this last week and now I can't find the replies in Outlook - only online! The file that I am working on is an imported .txt and the macro that I am using crashes with a Run-time error 5 - Invalid procedure call or argument. The line I'm trying to use is this: Range("A2").Value = ActiveWorkbook.BuiltinDocumentProperties("Creation Date") and I'm wondering if it's because the sheet is not technically a workbook. The macro imports a .txt file, deletes junk from it and formats some of the columns and I need to get the creation date of the txt file. The response I had was to try ("Creation Date"). I have tried this on a few worksheets, using the immediate window. I get the same date and time for 2 of the sheets (even though they weren't created at the same time) and when I try it on my txt file I get Runtime error '-2147467259 (80004005)'. Automation error. Unspecified error. A search for this on the net doesn't help. Thanks.. Andy. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creation date in cell
If you have more than one file whose date you want then:
Sub garbit() Dim fs, f Dim st, s, fname(3) As String ' list your files here Set fs = CreateObject("Scripting.FileSystemObject") For i = 1 To 3 Set f = fs.GetFile(fname(i)) s = "Created: " & f.DateCreated st = Split(s, " ") Cells(i, 1).Value = st(1) Next End Sub Haven't tested this. You need to initialize fname with you file names. -- Gary''s Student "Gary''s Student" wrote: O.K. then... Sub garbit() Dim fs, f Dim st, s As String Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFile("C:\dummy.txt") s = "Created: " & f.DateCreated st = Split(s, " ") Range("A1").Value = st(1) End Sub -- Gary''s Student "AndyB" wrote: Thanks for that. The slight snag is that I need this macro to run automatically on a number of different files - and I don't want to have to edit the macro for each one. Cheers. "Gary''s Student" wrote in message ... Try: Sub garbit() Dim fs, f Dim st, s As String Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFile("C:\dummy.txt") s = "Created: " & f.DateCreated MsgBox s st = Split(s, " ") MsgBox (st(1)) End Sub This should get you the creation date of any file ( here dummy.txt). Just change the GetFile argument. -- Gary''s Student "AndyB" wrote: Hi I posted this last week and now I can't find the replies in Outlook - only online! The file that I am working on is an imported .txt and the macro that I am using crashes with a Run-time error 5 - Invalid procedure call or argument. The line I'm trying to use is this: Range("A2").Value = ActiveWorkbook.BuiltinDocumentProperties("Creation Date") and I'm wondering if it's because the sheet is not technically a workbook. The macro imports a .txt file, deletes junk from it and formats some of the columns and I need to get the creation date of the txt file. The response I had was to try ("Creation Date"). I have tried this on a few worksheets, using the immediate window. I get the same date and time for 2 of the sheets (even though they weren't created at the same time) and when I try it on my txt file I get Runtime error '-2147467259 (80004005)'. Automation error. Unspecified error. A search for this on the net doesn't help. Thanks.. Andy. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creation date in cell
Hi
Thanks for your help Gary - I appreciate it. I was hoping to get a routine that would just give me the creation date of the active spreadsheet, but it's obviously not going to be simple as it just throws up the error I posted! Thanks again. Andy. "Gary''s Student" wrote in message ... If you have more than one file whose date you want then: Sub garbit() Dim fs, f Dim st, s, fname(3) As String ' list your files here Set fs = CreateObject("Scripting.FileSystemObject") For i = 1 To 3 Set f = fs.GetFile(fname(i)) s = "Created: " & f.DateCreated st = Split(s, " ") Cells(i, 1).Value = st(1) Next End Sub Haven't tested this. You need to initialize fname with you file names. -- Gary''s Student "Gary''s Student" wrote: O.K. then... Sub garbit() Dim fs, f Dim st, s As String Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFile("C:\dummy.txt") s = "Created: " & f.DateCreated st = Split(s, " ") Range("A1").Value = st(1) End Sub -- Gary''s Student "AndyB" wrote: Thanks for that. The slight snag is that I need this macro to run automatically on a number of different files - and I don't want to have to edit the macro for each one. Cheers. "Gary''s Student" wrote in message ... Try: Sub garbit() Dim fs, f Dim st, s As String Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFile("C:\dummy.txt") s = "Created: " & f.DateCreated MsgBox s st = Split(s, " ") MsgBox (st(1)) End Sub This should get you the creation date of any file ( here dummy.txt). Just change the GetFile argument. -- Gary''s Student "AndyB" wrote: Hi I posted this last week and now I can't find the replies in Outlook - only online! The file that I am working on is an imported .txt and the macro that I am using crashes with a Run-time error 5 - Invalid procedure call or argument. The line I'm trying to use is this: Range("A2").Value = ActiveWorkbook.BuiltinDocumentProperties("Creation Date") and I'm wondering if it's because the sheet is not technically a workbook. The macro imports a .txt file, deletes junk from it and formats some of the columns and I need to get the creation date of the txt file. The response I had was to try ("Creation Date"). I have tried this on a few worksheets, using the immediate window. I get the same date and time for 2 of the sheets (even though they weren't created at the same time) and when I try it on my txt file I get Runtime error '-2147467259 (80004005)'. Automation error. Unspecified error. A search for this on the net doesn't help. Thanks.. Andy. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I get date of file creation to XLS cell in date format? | Excel Worksheet Functions | |||
Date Creation in Cell | Excel Worksheet Functions | |||
Day before creation date in cell or as sheet name | Excel Programming | |||
Creation date in cell | Excel Programming | |||
How do I show file creation date in a cell in Excel? | Excel Worksheet Functions |