PDA

View Full Version : Date Modified



geoffishere
02-01-2010, 08:00 AM
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



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

Many thanks

Geoff

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

lucas
02-01-2010, 08:52 AM
Option Explicit
Sub DocHeaders()
Dim szLastSaveTime As String
szLastSaveTime = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
Dim ws As Worksheet
MsgBox szLastSaveTime
End Sub

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

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

look up builtindocumentproperties in help for a list of properties.

geoffishere
02-01-2010, 09:08 AM
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
02-01-2010, 09:12 AM
I doubt it. You will probably have to us an API call.

Hopefully someone better versed in the problem will come along.

mdmackillop
02-01-2010, 11:39 AM
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 (http://word.mvps.org/FAQs/MacrosVBA/DSOFile.htm). It should be applicable to all office files.