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

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


"Chrisso" wrote in message
oups.com...
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

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



--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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


"Chrisso" wrote in message
oups.com...
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

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

"Chrisso" wrote:

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

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

fPath = ThisWorkbook.Path & Application.PathSeparator

In article ,
"Gary Keramidas" <GKeramidasATmsn.com wrote:

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