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.

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.

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.

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.

Mark,

Create a checkbox on the worksheet from the Forms toolbar, and add this
macro to a standard code module

Sub CheckBox1_Click()
If ActiveSheet.CheckBoxes(Application.Caller).Value = 1 Then
SaveOften
End If
End Sub


BTW, the line

Dim nTime As Double

that I gave you previously should be

Public nTime As Double

--
---
HTH

Bob

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



"Mark Dullingham" wrote in
message ...
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.

Bob,

Sorry to have to come back to you again but I'm now getting this error-

Run-time error '1004'
Unable to get the CheckBoxes property of the Worksheet Class


?????

I've looked at some other posts about specifying a file name and from them
put together the following code-

Sub CheckBox1_Click()

Dim wb As Workbook
Dim ws As Worksheet.Name
Dim x As Variant
Dim y As Variant
Dim z As Variant
Dim strFileName As String
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("idrmterm")
x = Format(Date, "dd-mmm-yyyy")
y = Format(Time, "hh-mm-ss")
z = Now + Worksheets("DDE Sheet").Range("E1").Value
Dim strPath As String
strPath = "C:\Documents and Settings\Mark\My Documents\Work"
strFileName = x & " " & y & ws & ".xls"
If CheckBox1.Value = 1 Then
Application.OnTime Now, ActiveWorkbook.SaveCopyAs Filename:=strPath &
strFileName, Now + Worksheets("DDE Sheet").Range("E1").Value, True

End If
End Sub

If I've understood the OnTime correctly this would save the file instantly
and allow the process to be repeated in the amount of time specified in cell
E1

Only problem is I get a compile error Expected: end of statement with
Filename Highlighted

In the post I got this from 'Save with dates' on 2/28/2007 CV323 suggests
this part works.

Have you any ideas as to why this is happening and would this bir of code
work?

Many thanks once again

Mark


"Bob Phillips" wrote:

Mark,

Create a checkbox on the worksheet from the Forms toolbar, and add this
macro to a standard code module

Sub CheckBox1_Click()
If ActiveSheet.CheckBoxes(Application.Caller).Value = 1 Then
SaveOften
End If
End Sub


BTW, the line

Dim nTime As Double

that I gave you previously should be

Public nTime As Double

--
---
HTH

Bob

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



"Mark Dullingham" wrote in
message ...
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.