Macro to close workbook and replace with new workbook

[chinaboy]

Hi,

Is it possible to create a macro to close the currently open workbook, delete the file, then replace that file with another workbook stored on the share drive. Then finally open the workbook? I basically want to put a command button the user clicks and will replace their workbook with a new version stored on the network drive.

Does anybody have a sample code that can accomplish the above request?

Thanks

[MarvinP]

Hi chinaboy,

Your question is a little strange. You will need the Excel Macro running behind a workbook. If you close that workbook then the macro will no longer run. When you ask about the "currently open workbook" you must have two workbooks open. One of them has the macro running in it and the other one is what you want closed, I guess. Can you rethink this problem and give a better description based on my answer above?

[chinaboy]

Your correct, the question is a bit strange, but I thought I would give it a try. In access I was able to do something similar.

1. When the client opens their front-end, the code checks to see if the front-end is the latest version.

2. If the version numbers are different, then it will give the user a message saying that their front-end is out of date and it will now close and then reopen. It closes and then deletes the old database, copies the new front-end from the location set in the location table, and then reopens the front-end for the user.


I was hoping to find a solution like this in excel. Should I just give that up and find another solution?

[Medpack]

Sounds possible, but would take a bit of time to check variables in another workbook, saveas, etc, but the basic idea is;

Open your sample workbook, check source version (I'm assuming buried in your template/main workbook), if different version than source then execute some macro in the default named workbook, grab the activeworkbook's name, close it, save template workbook as activeworkbook's name.

[chinaboy]

Thank you for the responses. I already have a procedure that compares the open workbook with a text file that has the updated version number. Can I have a command button that the user clicks then runs a batch file that can close the workbook and copy and open the updated workbook?

[Medpack]

I can't see why it wouldn't be possible to run a batch file, although you'd probably need some references added under your vba editor to execute it and my expertise in that area is limited. If your batch file is just to used to execute closing the current workbook and opening the updated workbook - I would imagine it would be easier to just keep it all in Excel.