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("CreationDate")
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.

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("CreationDate")
> 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.
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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("CreationDate")
>> 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.
>>
>>
>>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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("CreationDate")
> >> 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.
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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("CreationDate")
> > >> 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.
> > >>
> > >>
> > >>
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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("CreationDate")
> >> 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.
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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("CreationDate")
> > >> 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.
> > >>
> > >>
> > >>
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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("CreationDate")
> >> 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.
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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("CreationDate")
> > >> 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.
> > >>
> > >>
> > >>
> >
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
>> > message
>> > news:[email protected]...
>> > > 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("CreationDate")
>> > >> 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.
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >