Power Pivot Table - can't refresh if source file open

[keker]

Hello,

I don't have much knowledge about Power Pivot, and I don't use it in any way that we could call advanced or even basic. I only discovered that I can easily link a PowerPivot data model to several MS Excell Workbooks by SQL Query, and then use the model as a source to Pivot Table in my Excel. And it's more efficient than linking it cell to cell or in any other way that I know.
My problem is: we work in a 6-7 people, each of us has his/her own Excel workbook, that I use as a source to one PowerPivot model (by very simple SQL Query), that I later use as a source to Pivot Table. If all source workbooks are closed, I can refresh the Pivot Table without any problem. But if anyone has his workbook open, the Pivot Table freezes, and I have to kill Ms Excel process. Is there any way to solve this problem?

The SQL Query i use looks more or less like that:

SELECT [DataModel$].* FROM [DataModel$] WHERE (NOT(([Exists] = 'No')) OR [Exists] IS NULL)
UNION ALL
SELECT * FROM `File`.[Sheet$] WHERE (NOT(([Exists] = 'No')) OR [Exists] IS NULL)
UNION ALL
SELECT * FROM `File`.[Sheet$] WHERE (NOT(([Exists] = 'No')) OR [Exist] IS NULL)

Can it be altered in any way to avoid freezing? Either by refreshing from open file, or if it's not possible, by skipping the open file with a MsgBox saying "File is open"?

Thank You
Maciek

 

[Matt Allington]

Have you looked into power query? I expect power query can handle an open workbook, but you will have to test it.

This is a link to a series of articles on my blog that covers importing workbook data.
Combine Excel Workbooks with Power Query

 

[keker]

Swoitching to PowerQuery indeed solved that problem.

Thank You.