SaveAs Macro

[raccoon588]

I have a timer on a macro to save the file every 15 minutes. If i open another excel while this one is open it triggers the other excel to save. and if i open an excel while this is running and then close it the file will reopen at the 15 minute mark and try to save. Any ideas on how to prevent this?

Sub Save1()
Dim nom As String
Dim path As String
 path = ThisWorkbook.Sheets("setup-lamp types").Range("L2").Value
 nom = ThisWorkbook.Name
 
Application.DisplayAlerts = False
ThisWorkbook.SaveAs path & "\" & nom
Application.DisplayAlerts = False


ThisWorkbook.Application.OnTime Now + TimeValue("00:15:00"), "Save1"
End Sub

 

[Yongle]

(wbX is the workbook that is being saved every 15 minutes)

Something you could try would be to keep procedure Save1 running only when wbX is the active workbook. Add 2 procedures in wbX ThisWorkbook module

1 Workbook_DeActivate event
- stop procedure Save1 AND save wbX

2 Workbook _Activate event
- start Procedure Save1

 

[raccoon588]

if I did private sub workbook_close() how would i get the save1 to stop? what would be the syntax?

 

[Yongle]

Why do you want to use Workbook_Close ?
What do you want to put there ?

 

[raccoon588]

sorry i meant workbook_deactivate

 

[Yongle]

Will update thread when I get back to my PC later tonight \ early tomorrow

When the workbook is deactivated and saved do you want
- SaveAs (with details as in procedure Save1)
or
- Save without renaming?


thanks

 

[Yongle]

I spotted this method on this page some time ago and have used it ever since because it seems to be the simplest possible way to start and stop macros that are running continuously.

goes in ThisWorkbook Module

Option Explicit

Private Sub Workbook_Activate()
[COLOR=#ff0000]StartTimer[/COLOR]
End Sub

Private Sub Workbook_Deactivate()
[COLOR=#008080]StopTimer[/COLOR]
End Sub

Private Sub Workbook_Open()
[COLOR=#ff0000]StartTimer[/COLOR]
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
[COLOR=#008080]StopTimer[/COLOR]
End Sub

goes in Standard Module

Option Explicit
Public RunWhen As Double
Public Const cRunWhat = "[COLOR=#800080]Save1[/COLOR]"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, 5)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, schedule:=True
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, schedule:=False
End Sub

Sub [COLOR=#800080]Save1[/COLOR]()
Dim nom As String
Dim path As String
path = ThisWorkbook.Sheets("setup-lamp types").Range("L2").Value
nom = ThisWorkbook.Name
Application.DisplayAlerts = False
ThisWorkbook.SaveAs path & "\" & nom
Application.DisplayAlerts = False
[COLOR=#ff0000]Call StartTimer[/COLOR]
End Sub