CODEWITHSUNDEEP
excelpowerqueryvba
Dinks123
Dinks123

Reputation: 79

PowerQuery: Refreshing a Table query whilst the Data Source file is open

I have an excel workbook containing a table that is linked to numerous Data Source files via PowerQuery. There is a high likelihood that the a user will be in the Data Source files updating them. Is it possible for me to refresh my table using powerquery whilst the Data Source Files are open by another user. I keep getting the message:

[DataSource.Error] The process cannot access the file '......' because it is being used by another process.

Upvotes: 1

Views: 3832

Answers (3)

Damir
Damir

Reputation: 21

After a lot of trial & error and a lot of Grace I worked out following workaround that really works regardless of OneDrive synchronization status.

Explainer of the situation: When OneDrive for Desktop is synchronizing an Excel workbook (let's call it Data Workbook), and that workbook is trying to be accessed from another workbook (let's call it Analysis Workbook) via PowerQuery or even directly by regular pivot table (not even PowerPivot) - Excel will regularly throw out error The process cannot access the file because it is being used by another process. That happens because the import/reading mechanism wants exclusive access to the Data Workbook (even though it is reading it only). You can't do a thing about it.

Workaround: You have to "fool" the import mechanism into thinking it is not accessing another workbook. There is a good enough solution for this which can be applied and automated in most situations:

  1. set up a simple single formula in Analysis Workbook that pulls all relevant data from Data Workbook, and then
  2. set up import mechanism to use that as a source.

This successfully circumvents any and all issues with OneDrive. Details on the example of using regular pivot table as the data consumer in Analysis workbook:

  1. Let's say your data workbook is My Data.xlsx, and an Excel table in that data workbook is the source of your data, and that table is named source_data. For the sake of simplicity, I'm presuming all workbooks are in the same folder.
  2. in Analysis workbook, add a worksheet "ds_trick", and in A1 cell place formula ='My Data.xlsx'!source_data[#All] (no matter how large the source data table is, it is going to fit into this worksheet because the formula is in A1 and there cannot be more rows in Data workbook's worksheet then in the Analysis workbook worksheet so you are safe on that side)
  3. Let's say that this data table fills columns from A to N. Change the pivot table data source to 'ds_trick'!$A:$N. Notice that you are not specifying the rows in this reference - you are referencing whole columns!
  4. make sure that whatever automation mechanism you are creating is opening the Data workbook before you are ordering pivot cache refreshing.

Notes:

  • Pivot tables do not support named ranges as source so you cannot directly reference the data workbook's Excel table in it. It also won't work if you try to create a named range in analysis workbook that references table in data workbook. You have to pull the data via formula in Analysis workbook.
  • Without named range as data source for pivot table you are left with "fixed" number of rows to define which is not good, so the trick for that is to reference whole columns - luckly pivot tables are good with trimming empty rows so this actually is done without any latency in processing input data. This way you are successfully faking dynamic named range as source for pivot table (in terms of rows)

Upvotes: 0

ACCtionMan
ACCtionMan

Reputation: 511

I found this topic when trying to work out how to do the same thing. Just in case anyone finds this in the future, I'll mention how I applied MarcelBeug's solution.

Code was this

let Source = Folder.Files("MyFilePathHere\"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each 
Excel.Workbook([Content],true)),

Changed to

let Source = Folder.Files("MyFilePathHere\"),
#"Filtered Rows1" = Table.SelectRows(Source, each not Text.StartsWith([Name], "~$")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Custom", each 
Excel.Workbook([Content],true)),

I made the change via the GUI editor using a Text Filter on the file name. The filter was 'does not begin with' and the value was '~$'.

Upvotes: 0

MarcelBeug
MarcelBeug

Reputation: 2967

Filter out files with a name starting with "~".

Upvotes: 2

Related Questions