Macro to Add Macro to Personal.xls Upon Opening

**amyxkatexx** · 08-02-2010, 01:11 PM

Since I am working on creating templates for a company that is going to be needing to edit these templates in the future, I'd like to have a way to not include the macros in the actual template itself but instead create a separate document with all the macros for all the templates in it (sort of my like own Personal.xls would be) but include an extra macro that will automatically move all of these macros to the users own Personal.xls when they open it (or maybe click a button).

I don't know what would be involved in order to create this, especially considering the fact that some users may have deleted their Personal.xls in the past or many other things could have happened.

I would think the best way to do this would be to somehow have the macro create a new workbook, name it something like "Clothing_Template_Macros.xls" and save it it to their startup folder with the macros in it...can this be done without knowing the exact location of their startup folder?

And on top of all that, I was hoping for it to have an attached toolbar with buttons linking to the different macros...but I would assume it has to know where this workbook is going to be located for it to link correctly.

Anybody ever tried to do something like this before?

Is this all just a pipe dream?

**amyxkatexx** · 08-02-2010, 01:14 PM

Quick thought...would an addin work?

How would I execute something like this with an addin? I don't really even know how to convert a workbook to an addin to begin with on Excel for Mac 2004.

**blane245** · 08-02-2010, 02:20 PM

There a couple of ways to go here. Firstly, check out the Microsoft Visual Basic of Applications Extensibility reference in VBA. You will find ways to manipulate code modules and other components. Secondly, you could locate all of your code in one Excel workbook and use it to create other workbooks with user selected templates. I prefer the first methods, as you may never know what you will run into in someone else's Personal.xls

**romperstomper** · 08-02-2010, 05:36 PM

An add-in sounds ideal for this. Creating one in XL2004 is the same as in other versions - File-Save As, change the type to 'Excel add-in'.
You will need to create some sort of menu system for running the routines though - my preference is to build the menus in code when the add-in is opened.

**amyxkatexx** · 08-03-2010, 01:44 PM

I think add-in is definitely the way to go as well. Any idea how updating an add-in would work though?

The ultimate goal of this is to be able to update the template and the macros separately from each other but still have them work hand in hand for multiple users. I hoped that if I could somehow make this add-in like a bit of program where when the user opens it, it asks them to locate the template and so then all the linking works appropriately and then it installs a toolbar to their excel with the appropriate buttons for running this macro.

I definitely think this is possible, the only problem is, if I update the add-in and then send it out to everyone again, what will happen when they try to run it...will it create a duplicate of itself or just replace the current one?