Solved

Excel VBA - Entering code in Workbook_Open event for new workbook created by copying worksheets from original workbook

Posted on 2011-09-25
10
437 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:AndresHernando
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36597479
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?
0
 

Author Comment

by:AndresHernando
ID: 36597534
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?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36597564
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?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:AndresHernando
ID: 36597857
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
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 36597891
For example:
Sub SaveFile()
   Dim wb As Workbook
   Dim varFileName
   Dim strDesktop As String
   
   ' copy sheet to new workbook - just for demo purposes
   ActiveWorkbook.Sheets(1).Copy
   Set wb = ActiveWorkbook
   ' get path to desktop
   strDesktop = CreateObject("WScript.Shell").specialfolders("Desktop") & "\"
   ' prompt for save file name
   varFileName = Application.GetSaveAsFilename(strDesktop & wb.Name)
   ' save
   If varFileName <> False Then wb.SaveAs varFileName
End Sub

Open in new window


Would that work?

Regards,
Rory
0
 

Author Comment

by:AndresHernando
ID: 36598193
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36598245
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.
0
 

Author Comment

by:AndresHernando
ID: 36598474
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36598552
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:

   varFileName = Application.GetSaveAsFilename(strDesktop & wb.Name, "Microsoft Excel files (*.xlsm), .xlsm")

Open in new window

0
 

Author Comment

by:AndresHernando
ID: 36708192
Rory,  as usual, you have been a huge help.  Got it working fine now.

--Andres
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question