CODEWITHSUNDEEP
vbaexcel
Shaik
Shaik

Reputation: 930

VBA stop refreshing excels on auto save

i am trying to auto save a csv open excel every second it is working fine but, the problem is on every save, if i have open 2 excel or the same file it appears and disappears i want to save it in a background not sure how to do it i made Application.DisplayAlerts = False ad false still not worked.

Sub SaveThis()
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = False

Application.OnTime Now + TimeValue("00:00:01"), "SaveThis"
End Sub

Updated Code

Sub SaveThis()
Application.DisplayAlerts = False
Application.EnableEvents = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Application.EnableEvents = True


Application.OnTime Now + TimeValue("00:00:01"), "SaveThis"
End Sub

Upvotes: 0

Views: 664

Answers (1)

Variatus
Variatus

Reputation: 14383

Try this code.

With ThisWorkbook
    If Not .Saved Then .Save
End With

The idea is that the hardware can't follow at the speed you demand. This code will slow the frequency of saves to the frequency of changes. Therefore, if there are changes at the rate of one per second the code will not cure the problem.

However, there isn't really a good reason for saving the workbook so often unless you expect the system to crash in which case Excel's own auto-recovery would create backup. Another possibility is that you might want separate copies of the changes every second. Your code doesn't try for that, and I doubt that your hardware can keep up.

Therefore even if you modify the workbook several times a second, it will be sufficient to save it at much longer intervals. Bear in mind that the purpose of saving is to secure the data. Stressing the hardware in the way you propose is more likely to endanger them.

Upvotes: 1

Related Questions