• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Error "Someone else is working in FILE_A right now. Please try again later" after use of Power Query|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Error "Someone else is working in FILE_A right now. Please try again later" after use of Power Query|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryError "Someone else is working in F…
sp_PrintTopic sp_TopicIcon
Error "Someone else is working in FILE_A right now. Please try again later" after use of Power Query
Avatar
Anke Baumann

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
May 11, 2020
sp_UserOfflineSmall Offline
1
October 5, 2022 - 12:27 am
sp_Permalink sp_Print

Hello everyone, since a couple of weeks I am having trouble with the following:

Our master file (File_A) contains our base data, as well as VBA macros. 
File_A is open.

Scenario 1:
One File_A macro opens File_B and then updates a Power Query of File_B (querying File_A data).
The File_A macro then saves and closes File_B.

Scenario 2:
A File_C is open as well; File_C contains VBA macros.
One File_C macro opens File_D and updates a Power Query of File_D (querying File_A data).
The File_C macro then saves and closes File_D.

My problem for both scenarios:
After a PQ update, no matter if triggered by a macro from within File_A, or triggered by a macro from the external File_C, it is impossible to save File_A -> I get the error message "Someone else is working in File_A right now. Please try again later".

This has only appeared recently (maybe 4 weeks ago?), it worked fine before.
We are using Microsoft 365.
File A is stored on a shared drive (however it is not a shared file).
File B, C and D are stored in repositories on SharePoint.

Any idea for the cause and what to fix here?
Thanks a lot!

sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
October 18, 2022 - 4:50 pm
sp_Permalink sp_Print

Hi Anke,

If file A is open, querying File_A data from another file will return a query error. Maybe you can change the code to save a copy of the File A and query the copy instead.

Can you upload a sample query?

Avatar
Anke Baumann

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
May 11, 2020
sp_UserOfflineSmall Offline
3
November 2, 2022 - 10:53 pm
sp_Permalink sp_Print

Thank you Catalin.

 

Where could I find specification / description of this behaviour (file A is open, querying File_A data from another file will return a query error)?

Is it possible to catch this error in VBA? 

 

What I find strange is that I had no problem with the setup of scenario 1 and 2 until recently.

 

Thanks again, Anke

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
November 4, 2022 - 9:53 pm
sp_Permalink sp_Print

Hi Anke,

When you query file A, if it is open, the query will fail. 

There are 2 ways to avoid this: in power query, if a file is open, the query editor will show 2 entries for the same file, one of them will start with the "~" prefix, it's a temporary hidden file. Filter the Name column to exclude files that begins with "~" symbol. This works only if file A is on a normal folder, not OneDrive/Sharepoint folder. (on these folders, excel creates a different type of hidden temporary copy, with .tmp extension)

The second way is to write code to copy File A into a different location, point the queries to the copy instead of the original file.

You can catch errors in a query:

On Error Resume Next

tbl.QueryTable.Refresh

If Err.Number <> 0 then ' Query failed, you can read Err.Description to see the reason

Avatar
Anke Baumann

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
May 11, 2020
sp_UserOfflineSmall Offline
5
November 16, 2022 - 1:23 am
sp_Permalink sp_Print

Thanks Catalin.

I am not querying a folder, but file A, so the temporary "~"file should not be the issue?

My query in file B is updating without error; however, when trying to save file A after the successful query, file A is displaying the error "Someone else is working in file A right now...". If I close file A and leave file B open on my computer, and then my colleague opens file A on her computer (in my first post I had mentioned it is a file on a shared drive) they can still not save it. Only after I have closed all Excel files on my computer, file A will not throw the error anymore.

File A is our database that is manually updated (and therefore opened) multiple times during the day. We have a couple of Power Queries connected to file A. It would have been great to query file A directly; to create a copy seems counterintuitive, but maybe it's the way to go.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
November 16, 2022 - 6:03 am
sp_Permalink sp_Print

I am not querying a folder, but file A, so the temporary "~"file should not be the issue?

Instead of querying file a directly, start a query from folder. The query will list all the files in that folder, apply a filter to identify file A. Apply another filter to remove the temp copy starting with ~. Note that the query will get the changes only after you save file A.

sp_AnswersTopicAnswer
Answers Post
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
7
November 16, 2022 - 9:08 am
sp_Permalink sp_Print

Hello,

If you don’t already have, do a file copy of file A as a backup just in case.

Open file A and save a new copy using Save As. Rename file A and then rename the copy so it gets the same name as file A had. Does the error message still pop up?

Br,
Anders

Avatar
Anke Baumann

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
May 11, 2020
sp_UserOfflineSmall Offline
8
November 30, 2022 - 11:12 pm
sp_Permalink sp_Print

Hello Anders,

thank you for your reply. Yes, we had done that however it did not resolve the issue.

Avatar
Anke Baumann

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
May 11, 2020
sp_UserOfflineSmall Offline
9
November 30, 2022 - 11:21 pm
sp_Permalink sp_Print

Thank you Catalin, I opted for starting the queries from a folder. I replicated my queries and with that linked my pivot tables to the new data source; this seems to do the trick. All testing was successful, it will go life next week. Thank you for your help.

Avatar
Cagnam
North Amarica

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
December 3, 2022
sp_UserOfflineSmall Offline
10
December 3, 2022 - 4:03 am
sp_Permalink sp_Print

There are 2 ways to avoid this: in power query, if a file is open, the query editor will show 2 entries for the same file, one of them will start with the "~" prefix, it's a temporary hidden file. Filter the Name column to exclude files that begins with "~" symbol. This works only if file A is on a normal folder, not OneDrive/Sharepoint folder. (on these folders, excel creates a different type of hidden temporary copy, with .tmp extension)

 

Re:Above -- How do you filter out the .tmp files.

I can filter "~" ok

but cannot see the .tmp files when i link to onedrive.

 

Thanks

Cagnam

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
11
December 3, 2022 - 6:19 pm
sp_Permalink sp_Print

As said, filtering out temp file works only outside OneDrive.

Avatar
Cagnam
North Amarica

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
December 3, 2022
sp_UserOfflineSmall Offline
12
December 4, 2022 - 4:27 am
sp_Permalink sp_Print

Thanks..

I was trying to find a way to refresh a query, where the source excel file is open and resides on OneDrive.

I could not find a solution..so is it safe to say... Files on OneDrive must be closed in order to refresh the query.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
13
December 4, 2022 - 4:23 pm
sp_Permalink sp_Print sp_EditHistory

Worth to mention something about OneDrive:

OneDrive is not just on your local computer, those files are in cloud as well.

While you cannot refresh a query to a local file from OneDrive folder, you can still connect to the cloud version with no restriction.

https://www.myonlinetraininghu.....ower-query

 

If you have a personal account, not OneDrive Business, and the above link does not work, try:

Get from onedrive cloud the share link, but you need to apply a trick:

Initially, the onedrive link looks like:

https://1drv.ms/x/s!AuMAcqo4%5B...]

You have to use it in a browser to open the file, the link will change to something like this:

https://onedrive.live.com/view.aspx?resid=E1C2D[...]3!457&ithint=file%2cxlsx&authkey=!AH9[...]

The final step is to change from view to download:

https://onedrive.live.com/download.aspx?resid=E1C2D[...]3!457&ithint=file%2cxlsx&authkey=!AH9[...]

This final link can be used in a Query from Web

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Sherry Fox, Richard West, Dana Friedt, Nada Perovic
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Forum Stats:
Groups: 3
Forums: 24
Topics: 6218
Posts: 27265

 

Member Stats:
Guest Posters: 49
Members: 31901
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.