Retrieving File Details including: Filename, Date Created/M

[Tom Rey]

Dear anyone, I am trying to populate the Detials of all my files in to an spread sheet so that I can easily keep track of them. Currently I am able to populate the file name and path for all the files in any directory using the following code

Sub ListFiles()
Dim myRow As Integer
Dim myFile As String

myRow = 1

myFile = Dir("*.xls")
Do Until myFile = ""

Cells(myRow, 1) = myFile

myRow = myRow + 1
myFile = Dir
Cells(myRow, 1) = myFile
Loop


End Sub

But I would also like to have the date modified and/or Created and any narritive appearing in the summary section o f the file properties. I would greatly appreciate any help with the above.

 

[Richie(UK)]

Hi Tom,

Welcome to the board.

This link should help:

http://j-walk.com/ss/excel/tips/tip57.htm

 

[Tom Rey]

Thanks Richie, that site is very helpful. I used the following code from the site which gives the date created:
MsgBox ActiveWorkbook.BuiltinDocumentProperties.Item _
("Creation date").Value
I was looking to get the date modified and the Summary details which would be a bonus. I have tried a number of keywords such as Author and Title which bring back information but the "date Modified" eludes me also I have tried looking in the object browser but have not been able to get information on the BuildinDocumentProperty properties. I would appreciate your help in getting this sorted as it seem so near but yet so far.

 

[Richie(UK)]

Hi Tom,

Try this sub:

<pre>Sub ReadBuiltInDocumentProperties()
Dim myProperty
Dim intI As Integer, myType
On Error Resume Next
myType = Array("", "Numeric", "Boolean", "Date", "Text")
Sheets.Add Before:=Worksheets(1)
intI = 1
For Each myProperty In ActiveWorkbook.BuiltInDocumentProperties
ActiveSheet.Cells(intI, 1).Value = myProperty.Name
ActiveSheet.Cells(intI, 2).Value = myProperty.Value
ActiveSheet.Cells(intI, 3).Value = myType(myProperty.Type)
intI = intI + 1
Next
ActiveSheet.Columns("A:C").EntireColumn.AutoFit
End Sub</pre>
This link may also be useful:

http://www.cpearson.com/excel/docprop.htm

HTH

 

[Tom Rey]

Thanks Richie, I have tried this code and it identifies the correct key word being "Last saved time" but it will only work for open workbooks. The Web site you pointed me to in your last response suggested the following solution for getting the properties of closed files:

"Your code will need a reference to the "DS: OLE Document Properties 1.2 Object Library" . Go to the Tools menu, choose References, and select this library. If you do not have this library installed, you can download it for free from Microsoft at http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224351" .

I have clicked on this link and have looked aroud the site and have had no luck in getting the "DS: OLE Document Properties 1.2 Object Library" and it is not contained in our libraries. Do you know we can get the required file? Thanks.

 

[Tom Rey]

Hi Richie, please disregard the last message I sent to you. I have since been able to download the required directory.The first time I tried to do it the Web page was in French, this managed to wrong foot me. We have been able to use the DSO.BuiltinProperties to get values for the Author, Date Created, Date Printed for closed files but we still get null values for date last saved or date last modified. "Date last modified" would suffice my needs and when I look at the file properties it is visibly on display which is different than "date last saved" which is not visible in Properties and can only be viewed to in Windows Explorer or in the open dialog box in excel. As I am dealing with files on a LAN would this impact on the ability to get such information? As I said the date last modified would do fine assuming that it is modifications made before last saving. I dont know have i hit a brick wall as I can not get these values for open or closed files, thanks.

 

[Richie(UK)]

Hi Tom,

I must confess I haven't actually needed to do this before so I'm a little in the dark here! :wink:

However, a quick Google search has revealed that, for some reason, Excel doesn't maintain the the "Last Save Time" property - or at least earlier versions don't (what version are you using?). See the link here: http://groups.google.com/groups?q=l...TF-8&selm=#MnuyQB0AHA.1400@tkmsftngp05&rnum=1

If anybody else has any useful info for Tom, please step in.

HTH

 

[artslave]

I found the "FileDateTime(pathname)"
function worked for me in this lil utility macro:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
       Cancel As Boolean)

'puts date of current save onto cover page

   Dim FileName As String

   SaveAsUI = False
   Cancel = False
   FileName = ActiveWorkbook.FullName
   Sheets("Cover Page").Range("A1").Value = FileDateTime(FileName)

End Sub

Here, I've worked it into your code & tested it on closed workbooks -- looks fine:

Sub ListFiles()
Dim myRow As Integer
Dim myFile As String

myRow = 1

'get all .xls files in current directory
myFile = Dir("*.xls")

Do Until myFile = ""

Cells(myRow, 1) = myFile                 'name of file
Cells(myRow, 2) = FileDateTime(myFile)   'date saved
    
    'go to next file in directory
    myFile = Dir
    
    'go to next row of output
    myRow = myRow + 1
Loop

'sort results by filename 
Range("A:B").Sort (Cells(1, 1))

End Sub

Does that help?
Catherine
This message was edited by artslave on 2002-10-25 01:45