Date Modified

[geoffishere]

I am using the following code to see if certain named files are present in a folder.

I am now struggling to get a script which will report some of the document properties especially the last date modified.

Any help would be appreciated


[vba]
Dim LRow As Integer
Dim LPath As String
Dim LExtension As String
Dim LContinue As Boolean
'Initialize variables
LContinue = True
LRow = 2
LPath = "K:\School of Community, Health Sciences and Social Care\Central school\ADMIN STAFF FOLDERS\Matthew Holden\Excel\"
LExtension = "*"

'Loop through all column A values until a blank cell is found
While LContinue
'Found a blank cell, do not continue
If Len(Range("A" & CStr(LRow)).Value) = 0 Then
LContinue = False
'Check if file exists for part number
Else
'Place "No" in column B if the file does NOT exist
If Len(Dir(LPath & Range("A" & CStr(LRow)).Value & LExtension)) = 0 Then
Range("B" & CStr(LRow)).Value = "No"
'Place "Yes" in column B if the file does exist
Else
Range("B" & CStr(LRow)).Value = "Yes"
End If
End If
LRow = LRow + 1
Wend
[/vba]
Many thanks

Geoff

Geoff, Please select your code and hit the vba button when posting code to format it for the forum.

[lucas]

[vba]Option Explicit
Sub DocHeaders()
Dim szLastSaveTime As String
szLastSaveTime = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
Dim ws As Worksheet
MsgBox szLastSaveTime
End Sub[/vba]

Each sheet has properties too. You can get them with a for each statement:

[vba]Sub DocHeaders()
Dim szAuthor As String
szAuthor = ThisWorkbook.BuiltinDocumentProperties("Author")

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.LeftHeader = szAuthor
Next ws
End Sub [/vba]

look up builtindocumentproperties in help for a list of properties.

[geoffishere]

I'm not sure if i explained myself clearly.

The list of files are a mixture of office documents. The idea of the spreadsheet is to ensure that all the necessary documents are in the folder and the date they were last modified.

Will this code work for that??

Many Thanks

Geoff

[lucas]

I doubt it. You will probably have to us an API call.

Hopefully someone better versed in the problem will come along.

[mdmackillop]

From Help

DateLastModified Property

Description
Returns the date and time that the specified file or folder was last modified. Read-only.
Syntax
object.DateLastModified
The object is always a File or Folder object.
Remarks
The following code illustrates the use of the DateLastModified property with a file:
Sub ShowFileAccessInfo(filespec)
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = UCase(filespec) & vbCrLf
s = s & "Created: " & f.DateCreated & vbCrLf
s = s & "Last Accessed: " & f.DateLastAccessed & vbCrLf
s = s & "Last Modified: " & f.DateLastModified
MsgBox s, 0, "File Access Info"
End Sub

Also, have a look at this. It should be applicable to all office files.