AndresHernando
asked on
Excel VBA - Entering code in Workbook_Open event for new workbook created by copying worksheets from original workbook
My originating workbook has code that generates a new workbook by copying some of its worksheets: Sheets(WksNamesArray).Copy
I want to add code to the NEW workbook's Workbook_Open event that will prompt the user to save the workbook. Is there a way to do that?
Thanks, --Andres
I want to add code to the NEW workbook's Workbook_Open event that will prompt the user to save the workbook. Is there a way to do that?
Thanks, --Andres
It would be easier to create a template with the required code in it, then simply create a new workbook from that template and copy the worksheets to that. Do you really want the prompt code to run every time they open it?
ASKER
Hi rorya,
I can't use templates; I need the original file to generate the new file.
What I intend to do, so that it will prompt to save only once, is to populate a "helper cell" that signals that the file has been saved.
Is there a way to put code in the new file's Workbook_Open event using VBA?
I can't use templates; I need the original file to generate the new file.
What I intend to do, so that it will prompt to save only once, is to populate a "helper cell" that signals that the file has been saved.
Is there a way to put code in the new file's Workbook_Open event using VBA?
There is, but it is dependent on user settings that you can't alter.
So before we start, two questions:
1. Why can't you use templates? The original file can still generate the new file based on the template
2. Why do you want to prompt the user to do a Save As after they open the file for the first time? Why can you not prompt them for a location initially instead?
So before we start, two questions:
1. Why can't you use templates? The original file can still generate the new file based on the template
2. Why do you want to prompt the user to do a Save As after they open the file for the first time? Why can you not prompt them for a location initially instead?
ASKER
rorya,
1) The reason I can't (or better said, would rather not) use templates is that there will be many users who will be downloading the originating file and, to minimize the complexity, I'd rather have them download a single file.
2) Prompting them for a specific location (like desktop?) will probably be OK.
Thanks, --Andres
1) The reason I can't (or better said, would rather not) use templates is that there will be many users who will be downloading the originating file and, to minimize the complexity, I'd rather have them download a single file.
2) Prompting them for a specific location (like desktop?) will probably be OK.
Thanks, --Andres
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Rory, your code would work with one exception: the filename is blank (waiting for the user to enter a filename). I need it to show the existing filename. I tried tweaking your code but couldn't make that happen.
Thanks, --Andres
Thanks, --Andres
The file name should not be blank (it isn't in my tests) as it uses wb.name to pass the existing file name. If you want to use the name of the file the sheets came from, then you will need to pass that instead.
ASKER
Rory, So you can see what is happening, I'm attaching the file that is generated and meant to be emailed to the recipient.
Thanks, --Andres Approval-Unit---PPR-FLR-11-0005-.xlsm
Thanks, --Andres Approval-Unit---PPR-FLR-11-0005-.xlsm
I don't think I'm seeing what you are, as I see the file name in the save box.
I would however change the getsaveasfilename to add the file filter:
I would however change the getsaveasfilename to add the file filter:
varFileName = Application.GetSaveAsFilename(strDesktop & wb.Name, "Microsoft Excel files (*.xlsm), .xlsm")
ASKER
Rory, as usual, you have been a huge help. Got it working fine now.
--Andres
--Andres