none
Cannot refresh data if excel files are open due to temp ~$ file RRS feed

  • Question

  • I have a folder with a few excel files. I am using a function successfully with a query that runs on all the files in the folder. However, if one of the excel files is open, a ~$FILENAME.xlsx is created - and I get the following error:

    [DataSource.Error] The process cannot access ~$FILENAME.xlsx because it is being used by another process.

    Is there way to exclude those files? I have confirmed that a query works on an open excel file - just not on those background temp files starting with ~$...

    Otherwise with all files closed it works great!

    cheers,

    J

    Monday, August 18, 2014 7:32 PM

Answers

  • There's not an easy way to do it straight through the UI, but if you have the table of files you can add a task with this formula:

    = Table.SelectRows(Source, each not Text.StartsWith([Name], "~$"))

    where Source is the name of your table. This will remove all files that start with ~$. If you want to do this through the UI, you can use the Begins With filter on ~$, then show the formula bar and put the word "not" before Text.StartsWith.

    Hope that helps.

    Wednesday, August 27, 2014 5:31 PM

All replies

  • There's not an easy way to do it straight through the UI, but if you have the table of files you can add a task with this formula:

    = Table.SelectRows(Source, each not Text.StartsWith([Name], "~$"))

    where Source is the name of your table. This will remove all files that start with ~$. If you want to do this through the UI, you can use the Begins With filter on ~$, then show the formula bar and put the word "not" before Text.StartsWith.

    Hope that helps.

    Wednesday, August 27, 2014 5:31 PM
  • Hi,

    I have a similar issue with Power Query.
    When I set up Power Query to open up files in a folder, if one of the files is using by another person, then Power Query won't refresh but give me the following error message

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

    I found that I didn't consistently get this error message. For some Queries, even if one of the files in the folder is used by another user, it doesn't matter; but for others Queries just couldn't be refreshed.

    Is it a bug or designed?

    If I use point-link a cell to a cell in another workbook and that workbook is being used by someone else, I will just get the last saved value. I would have thought Power Query would do the same.

    Hope you could help on this.

    Sam

    Friday, January 6, 2017 11:25 PM
  • Okay, got it, if I filtered out $ in Name, that would do
    Friday, January 6, 2017 11:58 PM