VBA to close existing file & open a new file

[Mike62]

Hi - need help with a simple issue. I have a excel file that acts as a template (call it "Master.xlsm"). It is opened, changes made to it and then saved under a new file name (call this one "ABC101.xlsm"). When the file has been saved under the new name, I need it to close "ABC101", then re-open "Master.xlsm". There are other events that happens when "ABC101" after it is renamed, so I would like to imbed this new step as the final step in the process.
Thanks!

 

[Norie]

Do you want all this to happen from the 'Master.xlsm' workbook?

 

[Mike62]

No, this would from the newly created file - each one would be different, as the 'new' file when created is renamed to a combination of a clients name and an invoice number. So the process would be (a) open master file; (b) work on it, then a "save as" is done naming the file under client name & invoice no (e.g. ABC Ltd Inv001); (c) VBA to re-open the master file again, then (d) close the client file (ABC Ltd Inv001), thus leaving the master file open to work on for another client/invoice (e.g. DEF Ltd Inv002). Is this possible? Thanks!

 

[Norie]

Why not try something like this?

1 From another file or the master file create a copy of the master file.

2 Open the client/invoice file.

3 Work on copy of master file, presumably involving client/invoice file.

4/5 Save and close copy of master file using client/invoice name.

4/5 Close client/invoice file.

6 Move onto next client/invoice file.

You might even consider using a template for the master file and instead of opening/copying it each time create a new file based on it.

 

[Mike62]

Hi - this is for another person, who's looking for something as simple as possible. Is there a macro that could run, closing any excel file, other than the Master File? Maybe an If/Then statement? Thinking if open file is not MasterFile, then close, or something to that effect.

 

[Norie]

Where would this code be located?

 

[mark hansen]

If I'm understanding you correctly, you can check the file name when opening and "do this" if the file name is Master.xlsm and (else) "do that" if the file name isn't Master.xlsm.

First scenario ---I do pretty much the same thing for scheduling templates. I have a central file that I use to create other files for each week. The weekly file is just a copy of the central file with a different file name. I use Save Copy As to create the new weekly file. Depending on the file name when opening, they see different worksheets for either , 1) creating appointments or 2) see the worksheets that has the macro to create another weeks schedule ("Open the books for another week out).

Second scenario---In a similar process, I have people pick up template from SharePoint, and depending on the file name, they can be presented with a user form that asks for information. When they answer questions, and close the user form, I use the information from the user form to create a file name and use Thisworkbook.SaveAs to a specific folder and close the template (without saving it).

Have you though about just keeping the Master.xlsm file open in the background. As in the second scenario ---When you open the file, check the file name. If the file name is master, get the necessary information for the new file name and do a Thisworkbook.SaveAs, but keep the New file open. Since you are already working with the file with the new file name you won't mess up your master file. Continue your work on the new file and save and close. The blank master will already be open ready for you to start on the next one. You can rerun the macro that checks the file name and automatically get the information for the next file and proceed from there.

Hope this helps
Mark