Daily file archive with date - Assistance with VBA to not overwrite previously save same filename

[brianv]

I need to save a file on a daily based, archiving it, based on a filename in cell "Admin!C2" and yesterday's date.

I have the code auto running on the file open, so in the Workbook module i have this code:

Private Sub Workbook_Open()

    Call DailyArchive
   
End Sub

And in the module i have this code: (based on another post i found on this site)

Sub DailyArchive()
Dim newFile As String, fName As String
   
' Don't use "/" in date, invalid syntax
    fName = Range("C2").Value
   
    newFile = fName & " " & Format$(Date - 1, "yyyymmdd")
   
    On Error Resume Next
   
    ChDir _
    "C:\01 Admin\02 Test Templates\_Save File Daily\Archive"
    ActiveWorkbook.SaveAs Filename:=newFile
   
End Sub

Essentially, when the file is first opened for the day, it saves a archived copy. But what we need is only 1 save copy per day, the first one. So if the file is open again, and the a archive then already exists, then do create another archive (or overwrite the archive file).

Thanks for your assistance...
Brian

 

[Georgiboy]

How about the below:

Sub DailyArchive()
  Dim fName As String
  
  fName = Range("C2").Value & " " & Format$(Date - 1, "yyyymmdd") & ".xlsm"
  
  If Dir(fName) = "" Then
    ActiveWorkbook.SaveCopyAs fName
  End If
  
End Sub

 

[brianv]

Thanks,but this doesn't seem to do anything, no file archive file was created...

 

[Georgiboy]

What value do you have in range C2?
Is the sheet with the value in C2 active when you run the code?

 

[Sektor]

You need to check whether the file exists. It's done by using FileSystemObject. I don't know what is in C2 cell, so assume there's full path:

Sub DailyArchive()
  Dim fso As Object
  Dim newFile As String, fName As String
 
  ' Don't use "/" in date, invalid syntax
  Set fso = CreateObject("Scripting.FileSystemObject")
  fName = Range("C2").Value
 
  newFile = fName & " " & Format$(Date - 1, "yyyymmdd")
 
  If fso.FileExists(newFile) Then
    Exit Sub
  End If
 
  ChDir _
  "C:\01 Admin\02 Test Templates\_Save File Daily\Archive"
  ActiveWorkbook.SaveAs Filename:=newFile
  
End Sub

 

[Georgiboy]

The line below checks if the file exists or not:

If Dir(fName) = "" Then

 

[brianv]

C2 is the base file name, then the date is added to it via the code. The base file name is slightly different than the actual working file name

Working FIlename: PM FLow Log
Base file name:

 

[brianv]

Sorry i hadnt competed the post:
C2 is the base new file name, (then the date is added to it via the code). The base new file name is slightly different than the actual working file name:

Working Filename: PM FlowLog
Base new file name: PM FlowLog_Archive_(YYYYMMDD)

So to make sure, the intent is to only save the file once per day, the first time its opened for the day, essentially saving the previous day work as the archive file.
So if the file already exists for the day, then cancel the autosave.

Sektor,
I loaded your version, but on opening the same file a second time (meaning the archive backup file exists), i get a Run-time code error 1004: Cannot access "PM FlowLog_Archive_250625.xlsm"

Any ideas?

 

[Georgiboy]

Try making the value in C2:
C:\01 Admin\02 Test Templates\_Save File Daily\Archive\PM FlowLog_Archive

Then try my code again.

 

[brianv]

yes, that does create the archive file, then on a second opening, it does open the dialog that the file exists and if we wnat to override it, if i click No/Cancel, then i get a Runtime error "Method 'Save-As' of object '_Workbook' failed"