• 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
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Power BI Refresh from Open Excel Workbook|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Power BI Refresh from Open Excel Workbook|Power Query|Excel Forum|My Online Training Hub

vba course banner

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 QueryPower BI Refresh from Open Excel Wo…
sp_PrintTopic sp_TopicIcon
Power BI Refresh from Open Excel Workbook
Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 62
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
1
March 4, 2020 - 12:45 pm
sp_Permalink sp_Print

I’ve spent hours researching this with no luck.  If I am pulling data from a table within an Excel Workbook over to another Excel Workbook using Power BI, can I keep the source file open while refreshing?  I always receive the data source error, file used by another program.  I guess I have to close the workbook and then refresh?  I’d like to keep both open as I enter data and refresh.

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4517
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
March 4, 2020 - 12:49 pm
sp_Permalink sp_Print

No, the source workbook must be closed for Power Query to refresh, sorry.

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 62
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
3
March 4, 2020 - 1:27 pm
sp_Permalink sp_Print

Not sure about that.  I disabled OneDrive and was able to achieve what I was asking.  I can refresh the query while the source is open.  Thus, OneDrive is the culprit (my guess with the syncing in the background).

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4517
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
March 4, 2020 - 4:07 pm
sp_Permalink sp_Print

You didn't mention OneDrive initially. If you're referencing an Excel file on your hard drive it must be closed for refreshing. As for OneDrive, I haven't tested it enough to say. Did the changes get picked up by the refresh or is it still showing the previously sync'd data?

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 62
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
5
March 4, 2020 - 7:25 pm
sp_Permalink sp_Print

Do not have to close the source file, just save it.  Once saved, the refresh picks up all changes immediately in the other file and both files remain open at the same time.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4517
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
March 4, 2020 - 8:18 pm
sp_Permalink sp_Print

Ah, that'll be because the OneDrive sync client is uploading the changes from your PC to the OneDrive file. So, why were you getting the error initially? Was the Excel file only saved on your hard drive?

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 62
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
7
March 6, 2020 - 1:01 am
sp_Permalink sp_Print

Correct, that's what I was able to figure out.  I am pretty certain I was getting the original error because of the OneDrive sync client (i.e. the error was not be able to access the Data Source file because it was in use).  Once I "paused" OneDrive, everything worked as it should.  

 

Also, of note, there is a way to have this work and have OneDrive act as "normal."  You can achieve this by having the Query point to the URL link of the OneDrive source file (vs. having the Query point directly to the Excel Workbook).  However, if you are like me and live in an area where internet speeds are not good, the delay is too long to refresh.  Doing above, cuts down the refresh time tremendously.   

sp_AnswersTopicAnswer
Answers Post
Avatar
Erik Halkjaer
Member
Members
Level 0
Forum Posts: 35
Member Since:
March 18, 2020
sp_UserOfflineSmall Offline
8
January 7, 2021 - 11:52 pm
sp_Permalink sp_Print

Hi

I have been having the same issue using Excel Power Query and found a solution.

If you edit your query and remove queries from files beginning with "~$" ( equals open files ), then at least it works for me.

 

Erik

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 62
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
9
February 1, 2021 - 5:13 am
sp_Permalink sp_Print

I am posting along this thread as it has to do with a refresh issue with a file not associated with OneDrive (i.e. the source data file is on the local drive but no sync with OneDrive; call this file "test.") and not working properly.  Every time I refresh the main query, the "test" file cannot be resaved to the local drive; it has a "file sharing" message.  If I close close the main query file, the source file works fine and can be saved.  It's almost as if every refresh of the query is keeping that source file from being saved, generating the "file sharing" message.  Any thoughts?  I had no luck with Erik's solution above.  Thanks.     

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4517
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
10
February 1, 2021 - 1:09 pm
sp_Permalink sp_Print

ExcelNovice, you haven't said what type of file 'test' is. If it's an Excel file then it must be closed for Power Query to refresh properly, so this may be the cause of your problem.

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 62
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
11
February 1, 2021 - 11:31 pm
sp_Permalink sp_Print

Sorry, I made the mistake of "assuming" since talking about the same power query refreshing with another open file but good point.  The source file is an Excel .xlsm file.  

 

What is odd is that I've been able to make this work before with the source file open (and, does not matter, source file is local drive or OneDrive).  For some reason, Excel is giving me the "file sharing" message within the source file while trying to save it after a refresh from the main file.  I've been able to create new "test" files and it's working but I'd like to know how to fix going forward.  I've tried all the basic stuff (indexing, sharing wizard checked, virus disabled, etc.).  

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4517
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
12
February 2, 2021 - 12:29 pm
sp_Permalink sp_Print

Not sure, sorry. As a general rule I try to keep my source files closed when refreshing.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Gilbert Lemek
Ashleigh Farquharson
Jayz Luu
Fred Smith
Charles DeGraffenreaid
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Forum Stats:
Groups: 3
Forums: 24
Topics: 6360
Posts: 27812

 

Member Stats:
Guest Posters: 49
Members: 32340
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.