Sharepoint Excel with "View in Desktop" opened, How to Auto-Refresh "Desktop" without closing and re-opening Desktop?

Question

Sharepoint Excel with "View in Desktop" opened, How to Auto-Refresh "Desktop" without closing and re-opening Desktop?

BrainStain00 5

Have opened Excel in Sharepoint (online), then clicked button to "View in Desktop" since thats the only way to view in "Full Screen" mode.

How can my "View in Desktop" file, "already opened" and running, get refreshed with that new info being imported with vLookup and the (=) formula, WITHOUT it having to close and re-open since its auto-running and no one is there to manually do this.

The Desktop mode in "Full Screen" is meant to auto run 24/7 so it can be automatic to receive Job Status Updates.

Also the TODAY() feature needs to update also for bar-charts action but its open and has no interaction from users other than the Online version being updated from another workbook feeding it new jobs a few times each week.

So there are two (2) parts, 1. Refreshing the "View in Desktop" mode, and 2. the TODAY() getting refreshed daily with no user interaction.

I know its meant to update when opened, but its already opened, and can Refresh with a calculation, and am trying a few things for that, its just not Refreshing daily.

Ideas Please. (see image below)

I-bar Status Report Today

1 answer

Your answer

Answer 1

Harold Milan 13,990 Independent Advisor

Hi,

Excel Desktop doesn’t auto-refresh when left open because it stops

recalculating and syncing after launch. To keep your dashboard accurate 24/7:

Enable automatic refresh for queries or linked data.

Use a simple VBA macro to run RefreshAll and CalculateFull on a timer (e.g., every 10 minutes).

Ensure Calculation Options → Automatic is turned on.

This combination keeps VLOOKUP updates and TODAY() current without manual interaction.

I hope this helps!

BrainStain00 5 Reputation points

2025-12-23T15:44:30.19+00:00

I will look that up, thanks Harold for the quick response, that sounds helpful.

So with Refreshing the Desktop version on regular intervals, the today() will update, allowing the bar charts to update by one day.

Will any jobs entered into the Online version be updated into the Desktop version also? Just trying to understand its power plz.

Thanks Harold
Harold Milan 13,990 Reputation points Independent Advisor

2025-12-23T15:46:44.6966667+00:00

I'm really glad if this helped you out! If you found the information useful, feel free to mark this as the accepted answer it not only confirms the Helps you, but it can also help others who might be facing the same issue and are looking for guidance.

Happy to help!
BrainStain00 5 Reputation points

2025-12-23T16:44:30.97+00:00

Harold,

Thanks for your input on helping all this work together.

I have a question about the VBA macro. Ive created many of these buttons for other things, which are very helpful, but not for Online related/associated file, and now have already almost completed this, but in saving the macro created, a popup says I must save this Desktop version in a .xlsm format. (Remember this Desktop version is the "View in Desktop" file that the Sharepoint Online file that opened this Desktop, which is a .xlsx version, so will my Sharepoint Online Excel recognize the macro file .xlsm again when I have selected the option "View in Desktop",

or

will it create another file with a .xlsx format and ignore the button Ive created,

or

if I dont create a button but just create the macro, will it not require this to be a .xlsm format?

Just trying to be prepared for what the online version is going to react to ahead of time?

Thanks
Harold Milan 13,990 Reputation points Independent Advisor

2025-12-23T18:53:08.8366667+00:00

Thank you for explaining your scenario in detail. Macros require the file to be saved as .xlsm. SharePoint will keep that format, but Excel Online cannot run VBA macros they only work when you open the file in the desktop app. If you need automation in the browser, consider Office Scripts instead.
BrainStain00 5 Reputation points

2025-12-23T19:38:12.0933333+00:00

Thank you Harold,

Just so that I'm following you, after I create the Activex Macro and button in the Desktop version, I need to save it as .xlsm to be able to run the macro (not script) while the Desktop is open 24/7, (which I think I can also just open the xlsm by itself in a Desktop Excel, since it knows where it came from),

Then if I ever need to be in the online version, and I happen to click the "View in Desktop" option, it will probably open by default an .xlsx, which will not be able to run the Macro.

Can I execute the Macro anyway, or do I have to find the (possibly out dated .xlsm file) and sync it somehow with that RefreshAll button, to let that go back to running 24/7 and updating the TODAY(), and will the button sync to the Sharepoint Excel Online version to update it then and have a Macro Enabled version that can again sync and bring it up to date? (I guess experimenting will answer that)

Just trying to make sure before I start, since the Macro I just made gave me several warnings that were not in the instructions to make it.

Makes sense what you've laid out using the RefreshAll option, and somewhere theres a Timer that I can have it refresh about once an hour or so?

Sorry for the detailed question, Ive just been trying this so many ways from so many suggestions, I'd like one to work as easy as it sounds.

Thanks sir.
Harold Milan 13,990 Reputation points Independent Advisor

2025-12-23T20:02:08.4366667+00:00

Yes, you can make Excel Desktop refresh automatically every hour using a simple VBA timer with Application.OnTime. Excel Online cannot run macros, so this only works in the desktop app.
BrainStain00 5 Reputation points

2025-12-23T20:17:01.8366667+00:00

Thanks Harold,

I have the Macro created, only in the .xlsm like you said and only on my Desktop version, and it seems to do a refresh, all the menu icons went blank (or very light for awhile since there is a lot to sync or refresh)

Didnt see the button in the Online version, but maybe it wont show that.

Closed the Desktop version, opened the Online version, then clicked the "View in Desktop", which opened a .xlsx version, without the button, so I guess I need to keep the .xlsm Desktop version close by my side, I dont know if the .xlsm version will sync when I add data to the Online version xlsm, and cant be manually updating the .xlsm manually.

Oh well, I dont know how the Sharepoint Excel .xlsx will sync with the .xlsm, even thou it may wake up the TODAY() like you said, lol.

Thanks for helping, I must have missed a step somewhere. Have a great day.
Harold Milan 13,990 Reputation points Independent Advisor

2025-12-23T20:36:24.9933333+00:00

Thanks for sharing your experience! To keep automation working, you’ll need to use the same .xlsm file in Excel Desktop or consider Office Scripts for browser-based automation. Let me know if you’d like guidance on either option I’m happy to help!

Share via

Sharepoint Excel with "View in Desktop" opened, How to Auto-Refresh "Desktop" without closing and re-opening Desktop?

1 answer

Your answer