1

Question: How to save ALL Excel files every given time period - say every minute or every 10 seconds ?

Related: here How to save Excel file every say minute? a way to save given file is described. But if I have many files it is a problem to process like that.

Remark: In case if I need to save every minute - I can use Excel's autosave, but autosave is is in *.xlsb format which I have a problem reading by Python, also several files are created and it is not clear what file is saved in what moment. Also that would not work if I need to save every 10 seconds.

Community
  • 1
  • 1
Alexander Chervov
  • 616
  • 3
  • 7
  • 23
  • You would need to put the autosave macro in the Personal Workbook. Check this [link](https://support.office.com/en-us/article/Create-and-save-all-your-macros-in-a-single-workbook-66c97ab3-11c2-44db-b021-ae005a9bc790) and this [link](https://www.rondebruin.nl/win/personal.htm) for information on how to access it and what it is. Then in the actual code replace all instances of `ThisWorkbook` with `ActiveWorkbook` instead. – tigeravatar Oct 03 '17 at 18:38

1 Answers1

4

To save all open excel files every 10 seconds, you can use this code. You can assign it to shape and run it from one of the excel files.

Sub Save1()
Dim xWb As Workbook
Application.DisplayAlerts = False
For Each xWb In Application.Workbooks
        If Not xWb.ReadOnly And Windows(xWb.Name).Visible Then
            xWb.Save
        End If
    Next
Application.DisplayAlerts = True

Application.OnTime Now + TimeValue("00:00:10"), "Save1"
End Sub
Kresimir L.
  • 2,301
  • 2
  • 10
  • 22
  • 1
    Just some comments for myself to remember: 1) that code can be inserted in some new "module" ( created by project==>Insert==>Module ) 2) create some Button and write code : Call Save1 ---------- it works fine, and pressing button starts the whole process – Alexander Chervov Oct 04 '17 at 07:49