D
Dallman Ross
Is there a way for an Excel formula to know the date stamp of
a file?
Thanks,
Dallman
a file?
Thanks,
Dallman
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bob Phillips
'-----------------------------------------------------------------
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)
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)
D
Dallman Ross
Bob Phillips said:'-----------------------------------------------------------------
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\2006\SB 2006 Unrealized Gains, Trading.csv"
(which is also a data source for one of the open worksheets, in case that helps).
D
Dallman Ross
In <[email protected]>, 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\2006\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
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\2006\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
B
Bob Phillips
You need to create a sub, or a function and add it there. These go into a
standard code module, Insert>Module 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)
Gains, Trading.csv"
standard code module, Insert>Module 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)
Documents\Records\Finance\Securities\Statements\2006\SB 2006 UnrealizedDallman Ross said:In <[email protected]>, 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
Gains, Trading.csv"
D
Dallman Ross
Bob Phillips said:You need to create a sub, or a function and add it there. These
go into a standard code module, Insert>Module 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.) ;-)
B
Bob Phillips
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)
Open the Excel workbook, and then run the macro 'GetDateCreated'.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
T
Tom Ogilvy
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\2006\SB 2006
Unrealized Gains, Trading.csv")
Uncomment Application.Volatile if you think the CSV file will be changing
frequently.
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\2006\SB 2006
Unrealized Gains, Trading.csv")
Uncomment Application.Volatile if you think the CSV file will be changing
frequently.
D
Dallman Ross
Bob Phillips said: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
D
Dallman Ross
Tom Ogilvy said: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\2006\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
T
Tom Ogilvy
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
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
D
Dallman Ross
Tom Ogilvy said: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
S
Steve Yandl
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/scriptcenter/guide/sas_fil_lunl.mspx?mfr=true
Steve
object. Here is an example in vbs that can easily be incorporated in a VBA
routine:
http://www.microsoft.com/technet/scriptcenter/guide/sas_fil_lunl.mspx?mfr=true
Steve
D
Dallman Ross
Steve Yandl said: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/scriptcenter/guide/sas_fil_lunl.mspx?mfr=true
Very good! Thank you.
Dallman