Is there a way for an Excel formula to know the date stamp of
a file?

Thanks,
Dallman

'-----------------------------------------------------------------
Function DocProps(prop As String)
'-----------------------------------------------------------------
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function


and enter in a cell such as
=DocProps ("last author")
or
=DocProps ("last save time")



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dallman Ross" <dman@localhost. wrote in message
...
Is there a way for an Excel formula to know the date stamp of
a file?

Thanks,
Dallman

In , Bob Phillips
spake thusly:

'-----------------------------------------------------------------
Function DocProps(prop As String)
'-----------------------------------------------------------------
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function


and enter in a cell such as
=DocProps ("last author")
or
=DocProps ("last save time")

That's very nice! However, I want it to be for a different file,
not the active workbook. E.g.,
"E:\My Documents\Records\Finance\Securities\Statements\20 06\SB 2006 Unrealized Gains, Trading.csv"
(which is also a data source for one of the open worksheets, in case that helps).

--
dman

In , Dallman Ross <dman@localhost. spake thusly:

Okay, I did find this:
http://www.cpearson.com/excel/docprop.htm

And I downloaded DSO Version 2.0 from Microsoft as indicated on the
page and installed it. But I'm a bit lost as to how to get the
code snippet to work. Is it still a function? I have this
now --

Dim FileName As String
Dim DSO As DSOFile.OleDocumentProperties
Set DSO = New DSOFile.OleDocumentProperties
FileName = "E:\My Documents\Records\Finance\Securities\Statements\20 06\SB 2006 Unrealized Gains, Trading.csv"
DSO.Open sfilename:=FileName
Debug.Print DSO.SummaryProperties.DateCreated
DSO.Close

I'm not sure how to put that into the VBA area, i.e., what to call it,
how to access it, etc. Basically, I'm pretty confused about where
to put the above and how to get the result into a cell in Excel.

Dallman

In , Bob Phillips
spake thusly:

'-----------------------------------------------------------------
Function DocProps(prop As String)
'-----------------------------------------------------------------
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function


and enter in a cell such as
=DocProps ("last author")
or
=DocProps ("last save time")

That's very nice! However, I want it to be for a different file,
not the active workbook. E.g.,
"E:\My Documents\Records\Finance\Securities\Statements\20 06\SB 2006 Unrealized Gains, Trading.csv"
(which is also a data source for one of the open worksheets, in case that helps).

You need to create a sub, or a function and add it there. These go into a
standard code module, InsertModule in the VBIDE.

Whether it is a sub or a function depends upon how you want to get the
results.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dallman Ross" <dman@localhost. wrote in message
...
In , Dallman Ross <dman@localhost. spake
thusly:

Okay, I did find this:
http://www.cpearson.com/excel/docprop.htm

And I downloaded DSO Version 2.0 from Microsoft as indicated on the
page and installed it. But I'm a bit lost as to how to get the
code snippet to work. Is it still a function? I have this
now --

Dim FileName As String
Dim DSO As DSOFile.OleDocumentProperties
Set DSO = New DSOFile.OleDocumentProperties
FileName = "E:\My
Documents\Records\Finance\Securities\Statements\20 06\SB 2006 Unrealized
Gains, Trading.csv"
DSO.Open sfilename:=FileName
Debug.Print DSO.SummaryProperties.DateCreated
DSO.Close

I'm not sure how to put that into the VBA area, i.e., what to call it,
how to access it, etc. Basically, I'm pretty confused about where
to put the above and how to get the result into a cell in Excel.

Dallman

In , Bob Phillips
spake thusly:

'-----------------------------------------------------------------
Function DocProps(prop As String)
'-----------------------------------------------------------------
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function


and enter in a cell such as
=DocProps ("last author")
or
=DocProps ("last save time")

That's very nice! However, I want it to be for a different file,
not the active workbook. E.g.,
"E:\My Documents\Records\Finance\Securities\Statements\20 06\SB 2006
Unrealized Gains, Trading.csv"
(which is also a data source for one of the open worksheets, in case
that helps).

In , Bob Phillips
spake thusly:

You need to create a sub, or a function and add it there. These
go into a standard code module, InsertModule in the VBIDE.

Whether it is a sub or a function depends upon how you want to
get the results.

Bob, I know we're close and I really appreciate the help. I just
don't know much of any VBA stuff and can't get this to work without
a full example. I've tried for quite some time now, including
with various websearch hints, but can't quite make it work.
Can you nudge me just a little bit more in the right direction?

You're putting the quotation of the previous article after your
..sig block, which is making my newsreader disregard that text when
I follow up -- sorry. But of course the prior article references
are all still here right now.

(I'm used to reading news in a Unix world, which I've been
doing for about 16 years now. And try as I might, I cannot
get used to the top-posting in this group. Oh, well. Not
to worry, I also own a fair bit of Microsoft stock.) ;-)

I have created an example file and posted it http://cjoint.com/?jrwjpeuDeb

Open the Excel workbook, and then run the macro 'GetDateCreated'.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dallman Ross" <dman@localhost. wrote in message
...
In , Bob Phillips
spake thusly:

You need to create a sub, or a function and add it there. These
go into a standard code module, InsertModule in the VBIDE.

Whether it is a sub or a function depends upon how you want to
get the results.

Bob, I know we're close and I really appreciate the help. I just
don't know much of any VBA stuff and can't get this to work without
a full example. I've tried for quite some time now, including
with various websearch hints, but can't quite make it work.
Can you nudge me just a little bit more in the right direction?

You're putting the quotation of the previous article after your
.sig block, which is making my newsreader disregard that text when
I follow up -- sorry. But of course the prior article references
are all still here right now.

(I'm used to reading news in a Unix world, which I've been
doing for about 16 years now. And try as I might, I cannot
get used to the top-posting in this group. Oh, well. Not
to worry, I also own a fair bit of Microsoft stock.) ;-)

Since it is for a CSV file, don't you just want the file Data and time you
would see in Windows Explorer?

Function DocProp(s as String)
'Application.Volatile
if Dir(s) < "" then
DocProp = FileDateTime(s)
else
DocProp = "File Doesn't exist"
End Function

go to the vbe, Alt+F11, do Insert module, put in the above


usage
=DocProp("E:\My Documents\Records\Finance\Securities\Statements\20 06\SB 2006
Unrealized Gains, Trading.csv")


Uncomment Application.Volatile if you think the CSV file will be changing
frequently.

--
Regards,
Tom Ogilvy



"Dallman Ross" <dman@localhost. wrote in message
...
In , Bob Phillips
spake thusly:

You need to create a sub, or a function and add it there. These
go into a standard code module, InsertModule in the VBIDE.

Whether it is a sub or a function depends upon how you want to
get the results.

Bob, I know we're close and I really appreciate the help. I just
don't know much of any VBA stuff and can't get this to work without
a full example. I've tried for quite some time now, including
with various websearch hints, but can't quite make it work.
Can you nudge me just a little bit more in the right direction?

You're putting the quotation of the previous article after your
.sig block, which is making my newsreader disregard that text when
I follow up -- sorry. But of course the prior article references
are all still here right now.

(I'm used to reading news in a Unix world, which I've been
doing for about 16 years now. And try as I might, I cannot
get used to the top-posting in this group. Oh, well. Not
to worry, I also own a fair bit of Microsoft stock.) ;-)

In , Bob Phillips
spake thusly:

I have created an example file and posted it
http://cjoint.com/?jrwjpeuDeb

Open the Excel workbook, and then run the macro 'GetDateCreated'.

Thank you very much!

Okay, I'm not quite over the hump even with this. Now I'm
getting a "permission denied" error ("Runtime error '70'").

I verified that I can open the file and the path and filename
are correct. In fact, if I copy and paste the path and
filename from the macro into the Run bar in Windows, the
file opens right up in Excel. The file is not read-only
and is not open elsewhere, though it's registered as a
data source in my other workbook. I closed the other workbook,
though, and the error did not go away.

Dallman

In , Tom Ogilvy
spake thusly:

Since it is for a CSV file, don't you just want the file Data and
time you would see in Windows Explorer?

Yes, sir!

Function DocProp(s as String)
'Application.Volatile
if Dir(s) < "" then
DocProp = FileDateTime(s)
else
DocProp = "File Doesn't exist"
End Function

go to the vbe, Alt+F11, do Insert module, put in the above


usage
=DocProp("E:\My Documents\Records\Finance\Securities\Statements\20 06\SB 2006
Unrealized Gains, Trading.csv")

Cool beans! Works like a champ -- after I added the apparently
missing "End If" statement just before the end of the function.
Thank you so much!

Uncomment Application.Volatile if you think the CSV file will be changing
frequently.

Okay. It changes multiple times a week, typically.

Dankeschoen! Thanks very much also to Bob Phillips. I'm
intrigued with the bit of knowledge wrapped up in that
approach, also, and still would like to find out why
I'm having a file-perms problem trying to use it.

Dallman

Last minute addition on the If Then construct. Guess I didn't get it
finished.

I would guess that non-office files don't have the extended properties
offered by the compound document format.

You can also play with the scripting runtime: This isn't set up as a
worksheet function.

Public Function DocProps(s As String)
Dim fs As Object 'Scripting.FileSystemObject
Dim fl As Object 'Scripting.File
Dim s1 As String
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(s) Then
Set fl = fs.GetFile(s)
s1 = fl.Name & vbNewLine & vbNewLine
s1 = "Created Date: " & Format(fl.DateCreated, "mm/dd/yyyy hh:mm:ss") _
& vbNewLine & "Last Accessed Date: " & _
Format(fl.DateLastAccessed, "mm/dd/yyyy hh:mm:ss") & _
vbNewLine & "Last Modified Date: " & _
Format(fl.DateLastModified, "mm/dd/yyyy hh:mm:ss")
Else
s1 = s & vbNewLine & "Doesn't exist"
End If
MsgBox s1

End Function


' modify this to test

Sub Testit()
Dim s As String
s = "E:\data\KZ081-Default Survey1.xls"
DocProps s
End Sub

--
Regards,
Tom Ogilvy


"Dallman Ross" <dman@localhost. wrote in message
...
In , Tom Ogilvy
spake thusly:

Since it is for a CSV file, don't you just want the file Data and
time you would see in Windows Explorer?

Yes, sir!

Function DocProp(s as String)
'Application.Volatile
if Dir(s) < "" then
DocProp = FileDateTime(s)
else
DocProp = "File Doesn't exist"
End Function

go to the vbe, Alt+F11, do Insert module, put in the above


usage
=DocProp("E:\My Documents\Records\Finance\Securities\Statements\20 06\SB
2006
Unrealized Gains, Trading.csv")

Cool beans! Works like a champ -- after I added the apparently
missing "End If" statement just before the end of the function.
Thank you so much!

Uncomment Application.Volatile if you think the CSV file will be changing
frequently.

Okay. It changes multiple times a week, typically.

Dankeschoen! Thanks very much also to Bob Phillips. I'm
intrigued with the bit of knowledge wrapped up in that
approach, also, and still would like to find out why
I'm having a file-perms problem trying to use it.

Dallman

In , Tom Ogilvy
spake thusly:

I would guess that non-office files don't have the extended
properties offered by the compound document format.

Ah. That would explain why I got the permissions error using the
macro Bob provided, I guess.

You can also play with the scripting runtime: This isn't set up as a
worksheet function.

Cool beans. I'll keep it around for pedagogical reasons and play.
Thanks again!

Dallman

For even more extended file properties, you can use the "Shell.Application"
object. Here is an example in vbs that can easily be incorporated in a VBA
routine:
http://www.microsoft.com/technet/scr....mspx?mfr=true

Steve



"Dallman Ross" <dman@localhost. wrote in message
...
In , Tom Ogilvy
spake thusly:

I would guess that non-office files don't have the extended
properties offered by the compound document format.

Ah. That would explain why I got the permissions error using the
macro Bob provided, I guess.

You can also play with the scripting runtime: This isn't set up as a
worksheet function.

Cool beans. I'll keep it around for pedagogical reasons and play.
Thanks again!

Dallman

In , Steve Yandl
spake thusly:

For even more extended file properties, you can use the
"Shell.Application" object. Here is an example in vbs that can
easily be incorporated in a VBA routine:
http://www.microsoft.com/technet/scr....mspx?mfr=true

Very good! Thank you.

Dallman