Colleagues! There is a code:

Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.DisplayAlerts = False Call ThisWorkbook.SaveAs("C:\_Temp\Test2\Version\Test2_" & Format(Now(), "DDMMYY") & "_" & Format(Time(), "hhmmss"), xlWorkbookDefault) Application.DisplayAlerts = True End Sub

I would very much like instead of using the hard-coded path "C: _Temp \ Test2 \ Version \ Test2_" to do this automatically (maybe somehow with ThisWorkbook.Path), so that this code would be universal for any new book that requires saving a duplicate in the specified folder when closing the file (copied this universal code + created the folder ".. \ Version .." where this file lies and that's it). That is, in the folder ".. \ Version .." you will get something like a log ...

More interesting is that if you want the code -)

And another thing: now the files with the .xlsx permission are saved to the folder ".. \ Version ..", but it is necessary that with the permission of .xlsm

  • For any book in which the code will be written? If there is not one such book in one folder - how to recognize folders for saving them? Do you need to save all versions (with active work with the book there will be a lot of back-up) or replace the file created earlier (one back-up file)? - vikttur
  • Yes. For any book in the folder. I see the process like this: 1. At the root, where there are many files (which contain this script and which need to be backed up) there is a folder ".. \ Version .." into which the versions of all these files will fall; 2. Yes, there will be a lot of them there, but this is nothing (there is enough space, the names of all the files are different, so they will be sorted by name and there will be no conflict); 3. If necessary, we will clean the folder ".. \ Version .."; 4. If possible, it is necessary in the code to provide the ability to replace the previously created file (one back-up file) [look at the dynamics of filling the folder]. Something like that - 2b4fITin 05:04

1 answer 1

In the "EtaBook" module

 Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim sFldr As String, sFName As String sFldr = ThisWorkbook.Path & "\Version\" ' путь к папке для сохранения' If Dir(sFldr, vbDirectory) = "" Then MkDir sFldr ' создаем, если нет' sFName = Replace(ThisWorkbook.Name, ".xlsm", "") & _ "_" & Format(Now(), "ddMM_hhmmss") ' имя back-up' Application.DisplayAlerts = False ThisWorkbook.Save ' если нужно сохранить и рабочую версию' ThisWorkbook.SaveAs Filename:=sFldr & sFName & ".xlsm", _ FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False Application.DisplayAlerts = True End Sub
  • Thank you very much! - 2b4fITin
  • @ 2b4fITin, the macro remains in copies. They (back-up) will be viewed. And after closing a new folder with back-ups of back-ups will be created in the folder ... To remove the code, use ThisWorkbook.VBProject.VBComponents - vikttur