Bob

Sorted out the error, I'd missed "" form the date and time format.

This is sort of working now but as I have the mudule assigned to a hotspot
the save only works when I click it.

What I would really like to do is allow the user to select a check box to
run the macro at the interval entered into a cell, in this case E1.

I have tried putting all or part of the module code into a command button
code and tried 'Run xlgsnu' in a command button but nothing seems to work!

"Mark Dullingham" wrote:

Thanks Bob for your reply.

I've added the code as you said and added a hotspot to enable the module but
I have a choice of 2 macro's to choose from 1- gsnu and 2- SaveOften.

I have modified it to add the time, heres the code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Mark\My Documents\Work"
s2 = "DDE Sheet" & "." & Format(Date, dd - mm - yy) & "." & Format(Time, hh
- mm - ss)
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("216 Ground Floor").Range("E1").Value
Application.OnTime nTime, gsnu
End Sub

With gsnu assigned, when I click the hotspot I get a 'compile error variable
not defined' and the 'dd' of the date format is highlighted.

With SaveOften assigned I get a compile error of 'expected function or
variable'

I have appsolutely no idea what this means. I have tried the help files but
with no luck.

Your help and advice would be gratefully received

Mark

"Bob Phillips" wrote:

In a standard code module add this code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("Sheet1").Range("B5").Value
Application.OnTime nTime, gsnu
End Sub


and in thisworkbook, add

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, gsnu, , False
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mark Dullingham" wrote in
message ...
What I am aiming for is as follows:-
I have a file with DDE links to an external MDB database and I would like
to
save the file with the date and time in the file name.
I would like this option to have an 'on/off' command button or check box
and
reference a cell value,say B5, in minutes, as the period at which the file
is
saved.
I have found the following code (by Gary's Student) but I don't know how
to
trigger it automatically.

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Thnaks in advance for any help.