Saving a file with time and date at a set time period

[Mark Dullingham]

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 Phillips]

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]

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

 

[Mark Dullingham]

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!

 

[Bob Phillips]

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]

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]

I showed you how to do it in the previous posting.

--
---
HTH

Bob

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

 

[Mark Dullingham]

Bob,

I appologise I didn't mean to cause offence or seem ungrateful.

I followed you answers carefully and still got the error I descibed before (
Unable to get the CheckBoxes property of the Worksheet Class) and I have no
idea what that means, but to show that, although I am a novice at VBA, I do
read alot of previous post and do have a go myself I tried the code I posted.

Once again, sorry if I've caused offence and thank you for you help on this
issue and on previous ones you've helped me with.

Mark

 

[Bob Phillips]

Mark, I wasn't chiding you mate, just pointing out that those instructions
were in my previous post.

Do you still have the problem?

--
---
HTH

Bob

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

 

[Mark Dullingham]

Sorry Bob I was having a hard time getting my head around the OnTime thingy
and in particular stopping it.
I reposted to get a better understanding of the time variant needed and Chip
Pearson pointed me to his site, which I'd read a number of times before but
didn't quite get 'calling' a procedure. then the penny dropped so all is
rosey now.

Just battling with a userform which I want to run 2 Ontime events from, I'm
sure I'll need to post on something to do with it!

Thanks for you reply.

Mark

 

[Bob Phillips]

two ontime is quite straight-forward as long as you keep two very separate
time variables.

--
---
HTH

Bob

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