Consolidating excel files
Maybe you’ve had success with shared workbooks, and I’d love to hear about it in the comments.For me, the limitations far outweigh the benefits, and there’s usually another way to accommodate multiple users.
If the external data source that you want to access is not on your local computer, you may need to contact the administrator of the database for a password, user permission, or other connection information.
Once you edit the connection information (with the exception of the Connection Name and Connection Description properties), the link to the connection file is removed and the Connection File property is cleared.
To ensure that the connection file is always used when a data source is refreshed, click Always attempt to use this file to refresh this data on the Definition tab.
Many data sources also require an ODBC driver or OLE DB provider to coordinate the flow of data between Excel, the connection file, and the data source.
The following diagram summarizes the key points about data connections. There are a variety of data sources that you can connect to: Analysis Services, SQL Server, Microsoft Office Access, other OLAP and relational databases, spreadsheets, and text files. Many data sources have an associated ODBC driver or OLE DB provider. A connection file defines all of the information that is needed to access and retrieve data from a data source. Connection information is copied from a connection file into a workbook and can be edited. The data is copied into a workbook so that you can use it just as you use data stored directly in the workbook.