Do you have the world's greatest Personal.xls and want to share it with others in your company? Watch Episode 780 for the steps necessary to distribute the personal macro workbook.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Now, if you create macros in your own personal macro workbook and you want to share those with others, there's a great way to do this.
First thing we have to do is figure out where your personal macro workbook is.
So, if we hit ALT+F11, and then, here in the PROJECT explorer, find PERSONAL.xls.
Now, just click on that once, and then in the IMMEDIATE window -- to get to the IMMEDIATE window, CONTROL+G -- type PRINT THISWORKBOOK.FULLNAME and press ENTER, and it's going to give you the exact path to PERSONAL.xls.
So, I’ll copy that.
Now, what I'm going to do is I'm going to close Excel and go out to Windows explorer, and navigate to that path.
So, there's PERSONAL.xls, and now what we're going to do is we're going to mail this to other people but, before we mail it, we need to rename it.
So, I'm going to rename it PERSONAL2.xls.
Alright.
Great, and then I'll mail it to a bunch of other people, and those other people, in theory, are going to put it on their desktop or somewhere where they can find it.
Now, on the other person's computer, what we're going to do is open Excel 2003 and there's a chance that they don't have a PERSONAL.xls.
If they don't have a PERSONAL.xls, we’ve got to force them to have one, and it’s very easy to do that.
So, what we're going to do is just go to TOOLS, MACRO, RECORD NEW MACRO, and we'll call it anything -- MACRO1 -- but we're going to store it in the PERSONAL MACRO WORKBOOK, click OK, and we don't have to do anything.
We just simply click stop.
Well, that action right there created their own PERSONAL.xls.
Now, what we're going to do is press ALT+F11 and, sure enough, there's our PERSONAL.xls.
Now, the person on the other side, the person who just received the file, they're going to open PERSONAL2.xls.
Of course, we can't have 2 workbooks open that are both called PERSONAL.
That's why we have to rename it PERSONAL2, and we're going to come over here to the VBA window.
This is pretty wild I never knew that you could do this.
I'm going to expand both projects.
That way I can see the modules, and here, in the real PERSONAL.xls, the one I want to get in, I have a MODULE1.
So, I'm going to open this one you'll see that we have modules with a MODULE1 and what I can do is I can simply take this MODULE1, and drag it and drop it here in PERSONAL.xls.
So, I get MODULE11 which is a little bit annoying but all of the macros from PERSONAL1.xls are now on my machine.
I can close PERSONAL2 and, forevermore, this new machine will have PERSONAL.xls.
Very cool.
Of course, now, the trick is to rename it PERSONAL2, mail it to all the other people, and then once the other people get it, they can simply drag the module from one workbook to another right in VBA.
Well, hey.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Now, if you create macros in your own personal macro workbook and you want to share those with others, there's a great way to do this.
First thing we have to do is figure out where your personal macro workbook is.
So, if we hit ALT+F11, and then, here in the PROJECT explorer, find PERSONAL.xls.
Now, just click on that once, and then in the IMMEDIATE window -- to get to the IMMEDIATE window, CONTROL+G -- type PRINT THISWORKBOOK.FULLNAME and press ENTER, and it's going to give you the exact path to PERSONAL.xls.
So, I’ll copy that.
Now, what I'm going to do is I'm going to close Excel and go out to Windows explorer, and navigate to that path.
So, there's PERSONAL.xls, and now what we're going to do is we're going to mail this to other people but, before we mail it, we need to rename it.
So, I'm going to rename it PERSONAL2.xls.
Alright.
Great, and then I'll mail it to a bunch of other people, and those other people, in theory, are going to put it on their desktop or somewhere where they can find it.
Now, on the other person's computer, what we're going to do is open Excel 2003 and there's a chance that they don't have a PERSONAL.xls.
If they don't have a PERSONAL.xls, we’ve got to force them to have one, and it’s very easy to do that.
So, what we're going to do is just go to TOOLS, MACRO, RECORD NEW MACRO, and we'll call it anything -- MACRO1 -- but we're going to store it in the PERSONAL MACRO WORKBOOK, click OK, and we don't have to do anything.
We just simply click stop.
Well, that action right there created their own PERSONAL.xls.
Now, what we're going to do is press ALT+F11 and, sure enough, there's our PERSONAL.xls.
Now, the person on the other side, the person who just received the file, they're going to open PERSONAL2.xls.
Of course, we can't have 2 workbooks open that are both called PERSONAL.
That's why we have to rename it PERSONAL2, and we're going to come over here to the VBA window.
This is pretty wild I never knew that you could do this.
I'm going to expand both projects.
That way I can see the modules, and here, in the real PERSONAL.xls, the one I want to get in, I have a MODULE1.
So, I'm going to open this one you'll see that we have modules with a MODULE1 and what I can do is I can simply take this MODULE1, and drag it and drop it here in PERSONAL.xls.
So, I get MODULE11 which is a little bit annoying but all of the macros from PERSONAL1.xls are now on my machine.
I can close PERSONAL2 and, forevermore, this new machine will have PERSONAL.xls.
Very cool.
Of course, now, the trick is to rename it PERSONAL2, mail it to all the other people, and then once the other people get it, they can simply drag the module from one workbook to another right in VBA.
Well, hey.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
