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]

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!

 

[SPS41]

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!

 

[SPS41]

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.

 

[Logit]

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

 

[SPS41]

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

But my computer will be in the way then right? Only if I turn on my computer the windows scheduler works. It’s on my company’s server. Is there a way to make windows scheduler run the macros in the cloud rather than from my computer?

 

[Logit]

Is there a way to make windows scheduler run the macros in the cloud rather than from my computer?

I don't know the answer to your question.

Seems to me if your computer is always on, being on the network shouldn't matter as all of your code is on your computer. If this process is critical you would want
to insure your computer would restart should the power go out then come back on. Maybe a UPS system ? And then you want to have a small sub in the ThisWorkbook
module that auto-starts your macro.

 

[SPS41]

I don't know the answer to your question.

Seems to me if your computer is always on, being on the network shouldn't matter as all of your code is on your computer. If this process is critical you would want
to insure your computer would restart should the power go out then come back on. Maybe a UPS system ? And then you want to have a small sub in the ThisWorkbook
module that auto-starts your macro.

Thanks a lot Logit. Thats very helpful. I’ll look into that. Appreciate the help!