Macro to open/close/save workbook

[JonathanB2]

So here is my problem. I need to have a workbook (all of them in a folder, ideally) refresh in the middle of the night. The file is large so the refresh takes a full minute. I know I can't auto refresh a closed workbook (pretty sure anyway), and I can't leave the workbook open for various reasons.

So I'm thinking I could have a macro in an open excel (it kicks off on open) that will open a particular workbook elsewhere (the one I need refreshed), open it, refresh it and close/save it. Ideally it would do this for the entire group of workbooks in the folder but I'd be happy to start with just one. I would like to set it to occur every twelve hours (so at midnight, say, this would occur - I would just have to leave the workbook with the macro in it open on a computer so that it runs and completes this task every night).

Is this possible? Any ideas/workarounds?

Thanks!

[adyteo]

have all the workbooks to refresh on open, then create a VBA to open, wait for completion, and save each of the workbooks. is this what you want?

[JonathanB2]

I don't want the workbooks I need to refresh to do so on open but to have another macro open them, refresh them and then close them. So from a workbook on my computer, the macro would go into another drive, open the file refresh, save/close. Does that make sense? Sorry if this is confusing. The reason the macro has to be from a separate workbook is because I want the workbooks that I need to refresh to do so in the middle of the night when nobody is at work.

[adyteo]

I understand that but what I was suggesting is to have each workbook set to Autorefresh (refresh on open). then with your Macro you just open them and save/closed them.

[JonathanB2]

Oh gotcha. Yes, that would be much easier....but there's a few reasons it wouldnt' work for this particular project. For one, if it refreshes on open THEN they input data it won't refresh again until they open it again. So if they open it on a friday (it refreshes), then they enter data and close it....until they open it again on Monday it won't refresh (and it won't update the charts etc. the workbooks are linked to). I could set it to refresh every 30 seconds etc. but lke I said it's a large file and it takes a min. or more to refresh, so this might screw them up.

[adyteo]

oops. I typed the wrong answer.

[adyteo]

I understand what you say. but, to help your macro from your control worksheet (the one that opens, saves all the others) you can set all others to refresh on open and then you only need to create a macro for opening and closing them, without bothering to create code for refreshing them also.
Your macro would contain something like this

Please Login or Register  to view this content.

[JonathanB2]

Works great, thanks so much!