Data Import & Export

How to Allow Multiple Users to Refresh Excel Data from a Shared Workbook

John Wilson Oct 22, 2026 868 views

Question details

Users need to configure a local macro-enabled Excel workbook so that multiple people can simultaneously refresh its data from a source workbook located on a network share.

How to Allow Multiple Users to Refresh Excel Data from a Shared Workbook

Product: Microsoft Excel
Device & OS: not provided
Scenario: Multiple team members are attempting to update data via OLE DB/ODBC connections in a macro-enabled workbook connected to a shared network file.
Observed behavior: Users encounter file locking errors or unexpected username and password prompts when trying to refresh data while the source workbook is open by another person.

Before you start

Verify that all users have the correct read and write permissions for the network share folder where the source workbook is hosted, and ensure your network connection is stable.

Solution 1Recommended

Adjust Excel Connection Properties and Share Permissions

Modifying your OLE DB or ODBC connection settings to prevent locking and ensuring proper network permissions can resolve concurrent data refresh errors.

When Excel queries a source file on a network share, it may place a lock on that file if the connection properties are not configured for shared read access. Adjusting the connection string and verifying folder security settings is the first step to resolving this.

Check network folder permissions

Navigate to the network share folder in File Explorer, right-click the folder, and select 'Properties'. Go to the 'Security' tab and verify that all users have at least 'Read & execute' permissions.

Modify Excel connection properties

Open your macro-enabled workbook. Go to the 'Data' tab, click 'Queries & Connections', right-click your specific connection, and select 'Properties'.

Configure OLE DB/ODBC settings

In the Connection Properties window, navigate to the 'Definition' tab. Ensure your connection string specifies read-only access where applicable, and check that authentication settings are correctly passing Windows credentials to avoid password prompts.

Adjust Excel Connection Properties and Share Permissions

Authentication Prompts: If you continue seeing username and password prompts, your network data provider may require integrated security to be explicitly defined in the connection string (e.g., Integrated Security=SSPI).

Solution 2

Migrate the Source Data to SharePoint

Moving the source workbook to a cloud-based platform designed for concurrent connections eliminates local network share file locking.

Solution 3

Troubleshoot VBA Macro Connections

If your data refresh is driven by custom VBA macros, optimizing the code to close connections immediately can reduce file locking.

Free Microsoft Office alternative

Seamlessly Collaborate and Share Spreadsheets with WPS Office

If network file locking and complex connection issues in Microsoft Office are slowing down your team, consider trying WPS Office. It provides a highly compatible, lightweight alternative to Microsoft Excel, offering built-in cloud collaboration tools that make multi-user data management much smoother.

1. Download and install: Download WPS Office for free from the official website and install it on your computer.
2. Open your shared files: Launch WPS Spreadsheets and open your existing .xlsx or .xlsm files seamlessly without worrying about format conversion.
3. Collaborate easily: Use WPS Cloud to share links with your team, allowing multiple users to view and co-edit data effortlessly.

100% compatible with Microsoft Excel (.xlsx, .xls, .xlsm) formatsBuilt-in cloud collaboration for real-time multi-user editing without file lockingLightweight application that runs smoothly on older hardware and systemsFree to download with a familiar, easy-to-use tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel prompt for a password when refreshing a shared workbook?

This typically occurs if the OLE DB or ODBC connection isn't configured to use integrated Windows authentication, or if the source file is currently locked by another user on the network share, causing the data provider to request alternative credentials.

Can multiple users run data query macros simultaneously?

Running macros that query the same backend Excel file simultaneously on a standard network share often leads to locking issues. Moving the backend data to a more robust database, or utilizing SharePoint, can alleviate these concurrency conflicts.

How do I fix the 'file is locked for editing' error during an external data refresh?

Ensure that your data connection string specifies read-only access (Mode=Read) and that your macro or query connection is designed to close immediately after fetching the data, rather than remaining persistently open.

Excel & Data Problems