Email a file to install a macro, toolbar & button to a user. Re-explained in Post 19

[duugg]

Hi all,

I have finally come up with a macro and would like to install it to around 10 people's "Personal Workbook" in my department. Let me explain a bit further...

We use web-based software which has an "Export to Excel" option which we all use. The resulting data populates into a spreadsheet automatically.

I would like the user to be able to click on Tools/Macro/Run Macro and then run that particular macro. I assume that this macro should be in the "Personal Workbook" so that the macro will be visible no matter what worksheet they may have open?

Can this be done automatically/with a macro or must this be manually done?

I"m using Excel 2003.

Thanks much.

[shg]

Create an add-in and have them install that. It gives you a small measure of configuration control.

[duugg]

Create an add-in and have them install that. It gives you a small measure of configuration control.

After doing some reading on add-ins, I'm not sure that this is what I'm looking for. I'm getting the impression from what I read that the add-in only applies to that particular workbook. As stated above, users will be generating Excel data from a new workbook every single time because their web-based software automatically exports a data list to Excel.

Having said that, they will always have a "new" spreadsheet to work on for that given day. Unless I missed something, I don't think they'll see the custom macro I installed for them via an add-in when they go to Macros and look at their list.

I did hear something awhile back that you can click on tools/add-ins and then pick the add-in but then what? Does this add-in install my custom macro to the users personal workbook? How does the user execute this custom-designed macro that I gave them keeping in mind that it's a brand new exported workbook?

Thanks

[royUK]

Don't know what you hve read, but an addin is a workbook that contains code. Once installed it is available to any open workbook on the PC.

You have to code carefully to ensure that the code will run on the correct workbook, i.e. the ActiveWorkBook.

[duugg]

Don't know what you hve read, but an addin is a workbook that contains code. Once installed it is available to any open workbook on the PC.

You have to code carefully to ensure that the code will run on the correct workbook, i.e. the ActiveWorkBook.

Well that is fantastic news Roy, thanks! Now you mentioned that I have to ensure that the code will run on the correct workbook. I can tell you that the exported workbook automatically gives the name "FullScreen" every single time, without fail. The reason that you saw FullScreen (2) earlier in the thread is that I have my macro set to keep the original intact and make a copy; hence the name FullScreen (2).

So, having said that, what is the best course of action to take before I send out this add-on?

Are there any key things that I have to do to ensure that this will work correctly?

Thanks much

[royUK]

I haven't seen the code

[duugg]

Please Login or Register  to view this content.

[royUK]

Your code needs tidying up before converting to an addin

[royUK]

This is better. It might be possible to make it work more efficiently if I saw an example workbook

Please Login or Register  to view this content.

[duugg]

Roy,

Thanks for the redo, but a lot of things didn't happen. Certain columns weren't deleted, the first (header) row color wasn't changed. Some cells names in row 1 were deleted, etc. I'd like to battle that a bit later. But even if my code is correct and I decide to make it more efficient, is there a thread here where I can go and get a STEP BY STEP process is outlined from my beginning to the users end?

In other words, after I successfully created the add-in, I simply emailed it to a colleague, she opened it and nothing happened. It was only after reading more about add-ins that I realized it not only had to be saved, but had to be saved in the add-in area (or by clicking on browse) and then installing it. So, while at the colleague's computer, I installed the add-in, exported the new "FullScreen" report and then I became stumped. Unlike installing a macro where I just go to macros/run macro, select the macro and hit run, there was nothing to "click on" to make this add-in execute.

What is the user supposed to do to make this happen fluidly? I don't want to have to have users go through a series of saving the add-in to a certain place, installing it, and then go through more hoops just to get this macro going. If there's no other way, then I may have to do this part at their computer myself.

Once the add-in is installed at the colleagues computer, what is the exact course of action that he or she must take to run my custom macro?

Does the macro have to be further modified before the user can execute it perfectly?

Thanks

[royUK]

You need to add a way to un the macro, maybe add a button to the Tools menu.
The cpde does need cleaning up first though.

If you attach an example workbook I will have a look

[duugg]

My company won't allow me to do that as the properties of our workbooks and the data both contain private company information.

Hmmm, interesting about a way to run the macro. Can you give me code to add a button that says "report" and install it as a toolbar button in a new toolbar? If so, where does this code get added? I'm hoping it's before I save it as an add-in and not at the users desk.

[royUK]

You add code to the addin to create the menu, the use the addininstall event to run t

[duugg]

You add code to the addin to create the menu, the use the addininstall event to run t

Roy,

You remind me of myself when other people in my department ask me how to do something general in Windows, I just blurt out the most basic of details thinking that "they should know how get there or where this button is" without realizing that they haven't a clue as to what I mean. I haven't the foggiest clue what "use the addinstall event" to run means.

The only thing I can garner out of that is that...somewhere...the words "addinstall" need to be typed in some module, the same module? a different one? Is that all I have to type is "addinstall" and a button will automatically popup on a new toolbar on the users computer and the code will execute? Can you be a bit more specific?

Thanks much

I'm

[Kenneth Hobson]

AddInstall is an event in the ThisWorkbook object. You can doubleclick ThisWorkbook in the VBE explorer and select the workbook and that event from the dropdown buttons.

You can also get to the ThisWorkbook object by right clicking the excel icon by the file menu and then select, View Code.

[duugg]

AddInstall is an event in the ThisWorkbook object. You can doubleclick ThisWorkbook in the VBE explorer and select the workbook and that event from the dropdown buttons.

You can also get to the ThisWorkbook object by right clicking the excel icon by the file menu and then select, View Code.

Okay, sorry I really am a newbie at this. Okay, I'm in the "ThisWorkbook".

Do I need to put code somewhere? If so, what code do I put and where?

Do I double click this "AddInstall" somewhere? If so, I can't find it.

[royUK]

Roy,

You remind me of myself when other people in my department ask me how to do something general in Windows, I just blurt out the most basic of details thinking that "they should know how get there or where this button is" without realizing that they haven't a clue as to what I mean. I haven't the foggiest clue what "use the addinstall event" to run means.

The only thing I can garner out of that is that...somewhere...the words "addinstall" need to be typed in some module, the same module? a different one? Is that all I have to type is "addinstall" and a button will automatically popup on a new toolbar on the users computer and the code will execute? Can you be a bit more specific?

Thanks much

I'm

If you add a workbook after removing the sensitive data I could check the code & indicate where to put the other code. As for further details, it's difficult to know how much to give without knowing the user's skill levels. However, giving the key words a Google search using excel vba addininstall produces a lot more detail.

[duugg]

Roy,

Thanks so much for your tenacity with helping me on this. I have some other boring duties to work on, but will pick this issue up where I left off hopefully later today. I'll keep posting any questions/advancements I make on this issue. I have a feeling that this will eventually become THE thread to go to for remotely installing a macro to other users computers using Add-ins.

Thanks again.

[duugg]

Okay here we go..

In a nutshell, let me try and explain this in a way that I'll be able to, in "Laymen's" terms, lol.

I don't think the actual code is the relevant part here. Let me explain what I mean. Even the simplest of code will allow me to convey what I'm trying to do here. Let's take this macro named "Daffy"...

Please Login or Register  to view this content.

Okay, John has a macro named "Daffy" in his personal workbook that's always available to him. John likes his macro so much that he wants his co-worker (who works in a different state) named Mary to have this same macro. John has no way of installing this macro to Mary's desk other than remotely through some file/files that he can email to her. Mary doesn't have much knowledge of computers so she just wants to click on a toolbar button named "Daffy" on her newly created "Daffy" toolbar and have this macro run.


Essentially, here are the steps to the story above

1. Send the "Daffy" macro to Mary's computer through email.
2. Have the "Daffy" macro somehow be installed automatically to Mary's Personal workbook.
3. Create and install a toolbar named "Daffy" to Mary's computer
4. Create and install a toobar button named "Daffy" in the "Daffy" toolbar.
5. Have the "Daffy" toobar button run the macro named "Daffy" in Mary's personal workbook.

Kudos to anyone that can pull this one off!

P.S. Excel 2003

Thanks much

[royUK]

The code for the macro should be in the ddin workbook, as should code to create the menu.

As i said attach a workbook with the code in it, I will check that it will run OK as an addin then add some code to create the menu.

Failing that search the Forum, I & others have posted code to add menus.

[duugg]

Roy,

Is there a reason that the "Daffy" macro won't suffice?
(by the way, complete and total coincidence that it matches your avatar, I laughed when I myself realized this).


Let's say I included a workbook here that contains the "Daffy" macro above on sheet 1/module 1. Because of "Daffy's" small code size, I'll be able to better understand why and where you put the code to make all this happen.

Using the long macro I have will make it more difficult for a newbie like myself, although I am learning a great deal thanks to all of you.

If you must use my particular code, I will see what I can do to upload it.

Thanks much