My Online Training Hub

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

Auto Refresh a Power Query|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Auto Refresh a Power Query|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_PrintTopic sp_TopicIcon
Auto Refresh a Power Query
Avatar
Peter Stevenson

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
January 3, 2021
sp_UserOfflineSmall Offline
1
November 2, 2021 - 7:15 am
sp_Permalink sp_Print

I have a query within spreadsheet B, that is held within a private channel in a MS Team. The query gets data from spreadsheet A that is held in another channel (thanks Amanda for your Get Data From SharePoint video!). Spreadsheet A has lots of sensitive data that I can only have access to, spreadsheet B is accessible to 6 members in my team and strips out the sensitive data through PQ and thus only presents non sensitive data.

 

Therefore, I'm the only one who can refresh the query in spreadsheet B, as my team members don't have access to spreadsheet A. I'm looking for spreadsheet B to be updated on a daily basis without me having to go in and manually having to refresh myself.

 

I was thinking of using 'Automate' within Excel Online and then running the script through PowerAutomate, however this does not record refreshing of a PQ. It appears you cannot use PowerAutomate to run a Macro either.

 

Are there any other options to automate this task?

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

Hi Peter,

Excel Online cannot refresh queries yet. Your best option at the moment is to write some VBA that opens the file periodically and triggers the refresh before saving and closing.

Mynda

Avatar
Justine Frescher

Active Member
Members

Dashboards
Level 0
Forum Posts: 3
Member Since:
October 27, 2021
sp_UserOfflineSmall Offline
3
November 16, 2021 - 3:37 pm
sp_Permalink sp_Print

Mynda

Sorry but if we use Excel on the Desktop but via SharePoint folders does the same apply? 

Thanks

Justine 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
November 17, 2021 - 4:39 pm
sp_Permalink sp_Print

It doesn't matter what the location is of the source data, Excel Online has no refresh functionality, so the only option is to refresh from the desktop version of Excel.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Louis Muti, Misael Gutierrez Sr.
Guest(s) 11
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:
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Joao Marques
Regi Hampton
Taffie Elliott
Paramita Chakraborty
David du Toit
Forum Stats:
Groups: 3
Forums: 24
Topics: 6358
Posts: 27805

 

Member Stats:
Guest Posters: 49
Members: 32335
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen