Share via

Update of another workbook with a vba macro

Jacques Abel 20 Reputation points
2026-05-05T23:13:01.4266667+00:00

Since last version of excel it is impossible from a workbook to open another one ,make changes and save them and then close the workbook. The process works, but when the workbook is closed the version before changes is back again (file with the initial timestamp and no changes recorded).
Curiously it works when the macro is launched in debug mode.
But for my daily use the master workbook have to run his macro silently, and in thesse conditions the updates are not done.
This is a very serious problem.
Thanks for repairing that quickly.
Ja

Developer technologies | Visual Basic for Applications

Answer accepted by question author

Jack Dang (WICLOUD CORPORATION) 18,970 Reputation points Microsoft External Staff Moderator
2026-05-11T04:27:47.1866667+00:00

Hi @Jacques Abel ,

Thanks for testing that.

This does narrow it down well. Since wbTarget.Save followed by wbTarget.Close SaveChanges:=False works, and the same code also works on the local PC, the VBA update logic is not the part failing.

At this point, the likely problem is what happens when Excel closes or shuts down while the workbook is on the network location. In other words, this now looks more like an Excel close/shutdown plus remote file-handling issue than a workbook-update VBA issue.

For production, I recommend keeping the sequence that worked:

wbTarget.Save
DoEvents
wbTarget.Close SaveChanges:=False
Set wbTarget = Nothing

If possible, avoid Application.Quit in the same automated run.

I cannot open a development escalation directly from this Q&A thread, but your latest test results do provide the right evidence for the correct support teams to investigate it.

The two teams that make the most sense here are:

  • Microsoft 365 / Excel support, because the failure appears during Excel close or shutdown.
  • The IT team that manages the network file location, because the issue only appears on the hosted file and not on the local PC.

If you have a Microsoft 365 business subscription, please ask your Microsoft 365 admin to open a support request here:

https://learn.microsoft.com/en-us/microsoft-365/admin/get-help-support

If you do not have admin access, use:

https://support.microsoft.com/contactus

Please include these points in the case:

  • wbTarget.Save + wbTarget.Close SaveChanges:=False works.
  • The same code works on a local PC.
  • The failure appears when Excel is closed or when Application.Quit is used against the network-hosted file.
  • The issue started after a recent Excel update.

Hope this helps! If my explanation and the information I provided were helpful to you so far, I would greatly appreciate it if you could follow the instructions here so others with the same problem can benefit as well.

Was this answer helpful?

1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jack Dang (WICLOUD CORPORATION) 18,970 Reputation points Microsoft External Staff Moderator
    2026-05-06T07:14:13.4833333+00:00

    Hi @Jacques Abel ,

    Thanks for reaching out.

    The fact that this works in debug mode but not when the macro runs normally does suggest the issue is more likely related to Excel state or timing than to the general VBA approach itself.

    Since you already said you are explicitly saving and closing the target workbook, I would not treat the basic Save / Close pattern itself as the real issue here. I would look more closely at what Excel may still be doing in the background at the moment the close happens.

    The adjustment I would try is to keep the same workbook-object approach, avoid ActiveWorkbook, and add a small processing gap between the save and the close so Excel can finish any pending events, calculation, or async activity.

    wbTarget.Save
DoEvents
Application.CalculateUntilAsyncQueriesDone
wbTarget.Close SaveChanges:=True

    If the workbook depends on calculation, you can also force calculation before the save. It is also worth confirming that the target workbook is not opening as read-only, because that can make the macro appear to run correctly while nothing is actually written back to disk.

    If events, queries, or other automation are involved, temporarily disabling events during the operation can also help stabilize it:

    Application.EnableEvents = False
' your logic here
Application.EnableEvents = True

    I would also keep an eye on where the file is stored. If it is on OneDrive, SharePoint, or a network location, sync or background file handling can sometimes interfere with this kind of automation. The same goes for workbooks using Power Query, external links, RTD, or anything asynchronous.

    If Save still does not persist the changes, a useful diagnostic test is to try a same-name SaveAs, because that forces Excel to write the file again more aggressively.

    Overall, your approach is valid. Based on the behavior you described, this looks more like Excel is not fully completing the save process when the macro runs at full speed than like something fundamentally wrong in your VBA.

    If this started right after a specific Excel update and the above does not stabilize it, I would check the exact build under File > Account > About Excel and compare it with Microsoft's official update history for Microsoft 365 Apps:

    https://learn.microsoft.com/en-us/officeupdates/update-history-microsoft365-apps-by-date

    You can also use Microsoft's overview of update channels to confirm which release channel the installation is on:

    https://learn.microsoft.com/en-us/DeployOffice/overview-of-update-channels-for-office-365-proplus

    That gives you a way to confirm whether the behavior started with a specific build and whether a newer build is already available for your update channel.

    If you can reproduce it consistently on the same build, it would also be worth sending feedback from Excel through File > Feedback > I don't like something, so the product team gets the exact build details along with the report.

    Hope this helps! If my explanation and the information I provided were helpful to you, I would greatly appreciate it if you could follow the instructions here so others with the same problem can benefit as well.

    Was this answer helpful?

  2. AI answer

    2026-05-05T23:13:15.9966667+00:00

    The described behavior matches recent Excel issues where workbook changes made by VBA do not persist unless execution is interrupted (for example, when stepping through in debug). These issues are typically caused by changes in recent Excel builds and often relate to calculation, events, or macro/ActiveX handling.

    From the available information, the practical workarounds and checks are:

    1. Ensure macros and ActiveX are fully enabled
      • If the target workbook uses macros or ActiveX controls, verify that macro and ActiveX content is enabled. Similar issues (for example, Run-time error 438 after Office 365 updates) have been resolved by enabling ActiveX/macro content in Trust Center settings.
    2. Avoid relying on automatically running procedures behind worksheets/ThisWorkbook
      • Automatically running procedures (such as Auto_Open, Auto_Close) or event procedures stored “behind” a worksheet or ThisWorkbook may not behave correctly after updates.
      • Move any automatically running code that opens/edits/saves the other workbook into a standard VBA module, and call it from there. This is the documented workaround for reliability of auto-run code.
    3. Use explicit save and close on the edited workbook
      • After making changes to the secondary workbook, explicitly call:
         wbTarget.Save
   wbTarget.Close SaveChanges:=True
      • Avoid relying on implicit save prompts or on the application closing to trigger saving.
    4. Check for code that resets the Saved property
      • Code that sets ThisWorkbook.Saved = True or ActiveWorkbook.Saved = True before saving or closing can cause changes not to be written.
      • Ensure no part of the macro sets the Saved property to True on the target workbook before Save is called.
    5. Temporarily disable automatic recalculation or volatile behavior
      • There are known cases where large/complex workbooks with heavy calculation or volatile functions interact badly with recent Excel builds, affecting open/save behavior.
      • As a diagnostic step, set calculation to manual before opening and editing the secondary workbook, then save and close it, and only then restore the previous calculation mode.

    If the problem started only after a specific Excel update and the same macro and workbooks worked previously, and the above adjustments do not resolve it, the remaining options are:

    • Install the latest Excel build (if not already) because similar regression bugs (for example, with pivot tables in version 2509) have been fixed in subsequent builds.
    • If the issue persists on the latest build, report it through official Microsoft support channels with a minimal reproducible workbook and macro, as this matches a regression pattern that requires a product fix.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.