Is it possible to get a date stamp from an Excel file?

[Chrisso]

Hi All

I am working on an automated backup scheme where my Excel
automatically makes a copy of itself every 7 days (although this is
configurable) in a seperate backup sub folder.

At the moment I record in the spreadsheet itself the date of the last
backup but this wont always work as sometimes a user will open the
spreadsheet in read-only - I still want to make the backup but I
cannot record the date of the backup in the spreadsheet.

So I think a better approach would be to look at the files in the
backup folder and find the one with the most recent date stamp - I can
then decide based on this date whether a new backup is required.

Is it possible in Excel VB to ask for the date stamp of all files in a
folder? Does anyone have some similar code?

Cheers and thanks in advance for any ideas,
Chrisso

 

[Gary Keramidas]

give this a try
filedate is a variable and filename.xls is your filename

filedate = FileDateTime("filename.xls")

if it's not in the default path then something like this

fpath = fPath = ThisWorkbook.Path & "\"
filedate = FileDateTime(fpath & "filename.xls")

 

[Gary Keramidas]

sorry, saw a typo
fpath = fPath = ThisWorkbook.Path & "\"
should be
fPath = ThisWorkbook.Path & "\"

 

[Guest]

You could try

Sub test4()
newfiledate = FileDateTime("o:\mypath\myfile")

x = DateDiff("d", newfiledate, Now() + 8)
If x > 7 Then
MsgBox ("Time for a backup") 'backup code here
End If

End Sub

Mike

 

[JE McGimpsey]

Just for completeness, this would work cross-platform too:

fPath = ThisWorkbook.Path & Application.PathSeparator