Troubleshooting “The workbook is currently in use” during Data Refresh

The error "The workbook is currently in use" appears during a data refresh when Power Query attempts to write updated records into an Excel table, Data Model, or PivotTable cache while another background thread or external service holds a file lock on the workbook. This issue commonly stems from background query collisions, conflicts with Microsoft 365 AutoSave, or queries that attempt to read from and write to the same workbook simultaneously.

Fast Fix

Open Data > Queries & Connections, right-click the query, and select Properties. Uncheck Enable background refresh to force queries to run sequentially instead of competing for workbook access. If your file is stored in OneDrive or SharePoint, temporarily disable AutoSave in the top-left corner of Excel before running a full refresh.

---

Diagnostic Snapshot

Property	Details
Error Message	The workbook is currently in use. / The operation failed because the workbook is locked by another process.
Trigger Event	Clicking “Refresh All”, running VBA ActiveWorkbook.RefreshAll, or auto-refreshing PivotTables
Primary System Cause	Concurrency conflict between background calculation threads, AutoSave sync, and Power Query write-back
High-Risk Environments	Workbooks stored in OneDrive/SharePoint with co-authoring active, or self-referencing queries

---

Why the File-Lock Collision Occurs

Excel uses a single-threaded interface to manage its worksheet grid and object model. However, Power Query uses multi-threaded background workers (Microsoft.Mashup.Container) to fetch and transform data.

[Mashup Background Container] ───(Tries to write fresh data)───┐
                                                                ▼
                                                      [Excel Workbook Grid]
                                                                ▲
[OneDrive AutoSave / Thread] ────(Tries to lock file to sync)───┘
                                         │
                                    LOCK CONTENTION:
                            "The workbook is currently in use"

When you click Refresh All, the following chain reaction can occur:

1. Background Refresh Collision: If Enable background refresh is turned on, Excel hands control back to you while the data is still downloading. If an automatic worksheet calculation, a user edit, or a second query tries to access the grid while the first query is writing back its rows, Excel flags a concurrency lock.
2. AutoSave Cloud Locks: For workbooks stored in OneDrive or SharePoint, Excel constantly synchronizes small changes. If AutoSave initiates an upload while Power Query is updating thousands of cells, the local file handle locks temporarily.
3. Self-Referencing Locks: If Query A reads data from Sheet1 using Excel.CurrentWorkbook() and writes the output back to a table on Sheet1 or another tab in the same file, it can trigger a circular read/write collision.

---

Step-by-Step Troubleshooting

Step 1: Disable Background Refresh on All Queries

Disabling background refresh forces queries to run one at a time, ensuring that each query completely finishes writing its data before the next operation begins.

1. Open the Data tab and click Queries & Connections.
2. Right-click the first query in the list and select Properties.
3. In the Usage tab, uncheck the box labeled Enable background refresh.
4. Repeat this step for all queries in the workbook.
5. Save the workbook, close Excel, reopen the file, and test Data > Refresh All.

Query Properties
  └── Usage Tab
        └── [ ] Enable background refresh  <-- Uncheck this!

If you are using PivotTables connected to Power Query, review Troubleshooting “Background Refresh” Conflicts with Pivot Tables.

---

Step 2: Manage AutoSave and Co-authoring Interruption

If the workbook is saved in OneDrive, SharePoint Online, or Teams, the cloud synchronization engine can interrupt long-running refreshes.

1. In the top-left corner of the Excel window, locate the AutoSave toggle switch.
2. Click the switch to toggle AutoSave Off during your reporting work.
3. Perform your data refresh (Data > Refresh All).
4. Once the refresh completes, save the file manually (Ctrl + S), then toggle AutoSave back on.

If AutoSave repeatedly interrupts your workflow, see “Calculation interrupted” by Auto-Save.

---

Step 3: Inspect Self-Referencing Queries

If a query extracts data from a table in the current workbook and outputs the transformed results into that same workbook, you may encounter read/write conflicts:

1. In the Power Query Editor, check the Source step.
2. If it uses Excel.CurrentWorkbook(), verify which table it is targeting.
3. Ensure the output table of the query is not the same table being used as the source.
4. If you are appending data to an existing table, separate your inputs and outputs:
   • Keep raw user inputs on an Input_Data worksheet.
   • Write Power Query outputs to a dedicated Transformed_Reporting worksheet.

For more details on file handles locked by background processes, see How to Fix “The process cannot access the file because it is being used” (PQ) and OneDrive “File in Use” Error (Even when the file is closed).

---

Step 4: Add Refresh Sequencing to VBA Macros

If your refresh is triggered via VBA (such as ActiveWorkbook.RefreshAll), the macro may be attempting to run subsequent code before the background queries have finished writing data.

Modify your VBA macro to enforce synchronous execution:

Sub SafeRefresh()
    Dim pc As PivotCache
    Dim q As WorkbookConnection

    ' Disable background refresh on all data connections
    For Each q In ThisWorkbook.Connections
        If q.Type = xlConnectionTypeOLEDB Then
            q.OLEDBConnection.BackgroundQuery = False
        ElseIf q.Type = xlConnectionTypeODBC Then
            q.ODBCConnection.BackgroundQuery = False
        End If
    Next q

    ' Refresh data safely
    ThisWorkbook.RefreshAll

    MsgBox "Refresh completed without lock conflicts.", vbInformation
End Sub

Setting BackgroundQuery = False forces VBA to halt execution until the data is fully loaded into the tables, preventing secondary processes from conflicting with active write locks.