Autosave on scheduled time help

[Sickjive]

windows7, excel 2010

I have a workbook that' s always open and i want to make autosave it everyday on a scheduled time.
I wrote the following code:

Sub Autosave()
Application.OnTime TimeValue("18:10:00"), "Autosave"
ChDir "P:\Test"
ActiveWorkbook.SaveAs Filename:=Format(Date, "ddmmyy") & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

I have one problem with it, when i run the macro it will immediately create the file in the ddmmyy format. When the scheduled time arrives to autosave the file i get a conflict that says the file name already exist.
What is wrong in the code?

 

[JoeMo]

The problem is that you are using SaveAs and that happens when you first run the macro. Here's one way I think you can go, but I have not tested it:

Sub Autosave()
Dim fPath As String, fNam As String
Application.OnTime TimeValue("18:10:00"), "Autosave"
fPath = "P:\Test\"
fNam = Format(Date, "ddmmyy") & ".xlsm"
If Not WorkbookOpen(fNam) Then
    ActiveWorkbook.SaveAs Filename:=fPath & fNam, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Else
    ActiveWorkbook.Save
End If
End Sub
Function WorkbookOpen(WorkBookName As String) As Boolean
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

 

[Sickjive]

Thnx i gonna try this one