Excel 2007 : Data connection between two workbooks locks source workbook when refreshed.

OK, so this will be my 3rd post on a forum about this problem. I for the life of me cannot figure this out. I encountered only a few people who have had this same issue, and I found a post on this forum of someone who had a similar problem, but based on the post time - they were using excel 2003 not 2007, as I am, and the solution here didn't help me.

http://www.excelforum.com/excel-prog...-workbook.html

I have 4 workbooks, all of them contain data that is managed by other teams at my company. I need to take all of this data, organize, and concatenate it into one sheet for myself, that I will then reference in other workbooks to various OTHER departments in our company that need the data for certain projects. I have the organization laid out in a lot of very complex formulas so that most of this is automated, and not manual. I need a solution that can pull data from the 4 workbooks without locking them for use, to allow me to keep my document open all the time, and they can update theirs at their leisure. Everything works fine, until I hit the refresh button. Once this is hit, all of the linked documents are locked and cannot be opened, even in "Read Only" mode. Once I close the master document, the files are free to be opened. I know a workaround for now is that i can not "refresh" the document, but rather close and re open it for changes. Although this is a workaround, I am sure that there is a cleaner method.

Some steps I have already tried :
1. Create an intermediary file that no one uses, and can be locked all day without a problem

This doesn't work for me since the intermediary file needs to be open to refresh, which locks the original source doc, leaving me in the same place as before.

2.Modify the connection string to display "Mode=Read;" instead of "Mode=Share Deny Read"
3.Create a new connection with "Read" only selected in the Advanced Tab

Any assistance would be great.

I assume your connection type is: Excel File. I had a similar problem in Excel 2010. When you open your Excel Workbook all of the other books become locked allowing users to only open them as Read Only. The lock does not release until you close your Workbook.

I eliminated the problem by setting up connections as MS Query, getting a connection type of Database Query. I'm still connecting to the same Excel Workbooks (both 2003, 2007 & 2010) and obtaining the content of specific sheets, but the lock does not occur.

Create the connection using the "From Other Sources" button on the Data ribbon. Select “Using Microsoft Query”. If the Excel driver is not in the “Choose Data Source” list select "New Data Source" and scroll down and pick the Excel driver you want. The next dialogs will let you select the file and tab(s) you need. (Remember: Tabs are considered System Tables so make sure your options in the "Query Wizard - Choose Columns" dialog has System Tables checked.)

Also, when MS Query returns the link it will have selected the existing columns in the workbook tab. So future refreshes will only return data from those columns. If you add columns to those other workbooks in the future you won't get them unless you update all of your connections. To avoid that, change the query in the "Command Text" (Connection Properties | Definition tab) to: Select * from .....

The other nice part of using Microsoft Query: one connection can obtain and consolidate data from multiple workbook tables into one sheet with just one connection definition. All of the tables being consolidated must have the same number of columns with the same column names. You have to enter the query manually into the "Command Text" (Connection Properties | Definition tab). The from clause must have the fully qualified path and name of the Excel file followed by a period (.) and the sheet name ending with a “$”.

Ex:

SELECT *
FROM "C:\Users\someone\Documents\Stuff\Excel Stuff\file1.xls".`'raw data$'`
UNION ALL
SELECT *
FROM "C:\Users\someone\Documents\Stuff\Excel Stuff\file2.xls".`'raw data$'`
UNION ALL
SELECT *
FROM "C:\Users\someone\Documents\Stuff\Excel Stuff\file3.xls".`'raw data$'`
UNION ALL
SELECT *
FROM "C:\Users\someone\Documents\Stuff\Excel Stuff\file4.xls".`'raw data$'`

This will query and consolidate into one table (or pivottable) all of the data from the four files at one time.

This is a great solution that worked for me, too!

Originally Posted by AZ78

I assume your connection type is: Excel File. I had a similar problem in Excel 2010. When you open your Excel Workbook all of the other books become locked allowing users to only open them as Read Only. The lock does not release until you close your Workbook.

I eliminated the problem by setting up connections as MS Query, getting a connection type of Database Query. I'm still connecting to the same Excel Workbooks (both 2003, 2007 & 2010) and obtaining the content of specific sheets, but the lock does not occur.

Create the connection using the "From Other Sources" button on the Data ribbon. Select “Using Microsoft Query”. If the Excel driver is not in the “Choose Data Source” list select "New Data Source" and scroll down and pick the Excel driver you want. The next dialogs will let you select the file and tab(s) you need. (Remember: Tabs are considered System Tables so make sure your options in the "Query Wizard - Choose Columns" dialog has System Tables checked.)

Also, when MS Query returns the link it will have selected the existing columns in the workbook tab. So future refreshes will only return data from those columns. If you add columns to those other workbooks in the future you won't get them unless you update all of your connections. To avoid that, change the query in the "Command Text" (Connection Properties | Definition tab) to: Select * from .....

The other nice part of using Microsoft Query: one connection can obtain and consolidate data from multiple workbook tables into one sheet with just one connection definition. All of the tables being consolidated must have the same number of columns with the same column names. You have to enter the query manually into the "Command Text" (Connection Properties | Definition tab). The from clause must have the fully qualified path and name of the Excel file followed by a period (.) and the sheet name ending with a “$”.

Ex:

SELECT *
FROM "C:\Users\someone\Documents\Stuff\Excel Stuff\file1.xls".`'raw data$'`
UNION ALL
SELECT *
FROM "C:\Users\someone\Documents\Stuff\Excel Stuff\file2.xls".`'raw data$'`
UNION ALL
SELECT *
FROM "C:\Users\someone\Documents\Stuff\Excel Stuff\file3.xls".`'raw data$'`
UNION ALL
SELECT *
FROM "C:\Users\someone\Documents\Stuff\Excel Stuff\file4.xls".`'raw data$'`

This will query and consolidate into one table (or pivottable) all of the data from the four files at one time.

Thank you, AZ78! The MS Query solution solved a lot of problems, that occured when using a usual Excel Connection to transfer data between Excel workbooks.