How do I automate opening an excel document every 24 hours in the background, refresh connections, save and close the workbook in excel using vba?

SPS41

New Member
Joined
Feb 21, 2023
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I have a workbook with a connection. I have gone to connection properties and set it to refresh periodically in the background. But I don’t know if it saves the document after the refresh. I don’t think so because the time of last modification of document doesn’t change.
So I’m trying to automate opening the workbook refresh, then save and close all in the background. I’m also looking into batch files and automating it that way. But I’m also wondering if there is a macro to do the same?
Any help would be greatly appreciated!
Thanks in advance!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
I have a workbook with a connection. I have gone to connection properties and set it to refresh periodically in the background. But I don’t know if it saves the document after the refresh. I don’t think so because the time of last modification of document doesn’t change.
So I’m trying to automate opening the workbook refresh, then save and close all in the background. I’m also looking into batch files and automating it that way. But I’m also wondering if there is a macro to do the same?
Any help would be greatly appreciated!
Thanks in advance!
Another way I can think of a solution is:
I enabled background refresh, is it possible that I can call a macro to save and then close once the refresh was triggered. But my concern is that:
1. How do I check if the background refresh actually works if the data is not saved? How do I know if the connection has been refreshed if there is no save afterwards? Aren’t all the changes lost after refresh is triggered but no save is called?
2. If the refresh happens in the background and I write a macro to save and then close, will that also happen in the background? Or will the excel sheet open and then save and close out?
I’m new to this so this may be trivial.
Thanks for the help!
 
Upvote 0
Another way I can think of a solution is:
I enabled background refresh, is it possible that I can call a macro to save and then close once the refresh was triggered. But my concern is that:
1. How do I check if the background refresh actually works if the data is not saved? How do I know if the connection has been refreshed if there is no save afterwards? Aren’t all the changes lost after refresh is triggered but no save is called?
2. If the refresh happens in the background and I write a macro to save and then close, will that also happen in the background? Or will the excel sheet open and then save and close out?
I’m new to this so this may be trivial.
Thanks for the help!
I misunderstood background refresh. I thought it would refresh in the background (when excel was closed). Didn’t realize it would trigger refresh only while open.
Now I’m back to square 1. I can always add it to startup. But because this excel file is on the server I was wondering if there was a way to trigger it open, refresh, save and close without the use of another excel file.
 
Upvote 0
You could create a vba macro but that would require that either your main workbook remain open all the time or .... you place the macro into another workbook that remains open all the time.

A better method would be to create an event in Windows Scheduler which will allow you to set the time when to open the original workbook. Your macros inside the main workbook will run (if you create the macro to do so) and then close, waiting for the next 24 hours to pass.

Task Scheduler for developers - Win32 apps
 
Upvote 0

Forum statistics

Threads
1,223,589
Messages
6,173,227
Members
452,505
Latest member
Neeraj1990

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top