There is a function that opens in turn all the files in a given folder, updates the data, saves and closes. The file is really opened and saved, but the data does not seem to have time to update, because file closes. How the solution seems to check whether the data update has ended. But how to implement it? Perhaps there is another solution.

Sub Obnovlenie_Sokhranenie_failov_v_papke() Dim s As String, fldr As String, j As Integer, f As Integer Dim rc As Range fldr = "d:\" s = Dir(fldr & "*.xls*") j = 0 f = 0 Application.ScreenUpdating = False 'podschyot kolichestva failov v papke Do While s <> "" s = Dir f = f + 1 Loop 'obrabotka failov v papke s = Dir(fldr & "*.xls*") Do While s <> "" With Workbooks.Open(fldr & s) 'deistviia s knigoi .RefreshAll .Save .Close (True) End With s = Dir j = j + 1 Application.StatusBar = "Obrabotano: " & j & " iz " & f & " failov" & " -> " & s: DoEvents Loop Application.ScreenUpdating = True ActiveWorkbook.RefreshAll Application.StatusBar = "Obrabotano: " & j & " èç " & f & " failov" End Sub

    1 answer 1

    In VBA, there is no parallelization of computations - while the code is being executed, other actions cannot be performed (unless this is allowed in the code). In the case of updating links, logically: until the update is completed, the macro does not work any further. Perhaps they do not have time to recount the formulas or in the internal kitchen not everything has been put in place ...

    Tips on the level of guesswork.

    Try to work with the delay in saving and closing the book:

      .RefreshAll Application.Wait Time:=Now + TimeValue("0:00:04") ' задержка' .Save:.Close

    If it does not help, try using (well, very ridiculous guess) interrupts (transfer of control)

     .RefreshAll DoEvents

    Yes, it is not clear which links are in the book ... Instead of (or together with) .RefreshAll update all links

     .UpdateLink Name:=.LinkSources, Type:=xlExcelLinks

    No more thoughts on this.

    Complement the code by disabling / enabling Application.DisplayAlerts - Excel will not have any questions.

    • All the same, and even with large (20-30 sec.) Delays, the update does not occur. Opens, waits, saves, but does not update. - Vitaly Murach 1:53 pm
    • Still added the answer - vikttur
    • It still does not help. - Vitaly Murach
    • I'll try another way. For example, open files, as before, but run a macro in each of them. - Vitaly Murach
    • Each file in Module1 has an UpdateFile macro. How to run it using the Application.Run command? - Vitaly Murach