About personal.xls and add-ins...?

[abxy]

I've created some macros and UserForms that have and are becoming "tool
of the trade" around the office, the thing is, these macros ar
currently attached to specific workbooks, so when you click on the ico
on the toolbar for that macro or userform, it'll load the workbook tha
macro or userform is housed in. When people use these macros an
Userforms, i don't want all these different workbooks opening up, wha
can I do? I've heard something about a personal workbook(personal.xls
or an add-in alteranative to the personal workbook just from zippin
through the forums, but nothing is too detailed on the subject, can
get a little help from you all about solving my dilemma?

Thanks :

 

[Chip Pearson]

The personal.xls file is irrelevant in this context. What you
want to do is create an add-in. Put all the forms and code in to
a single workbook and the save that file as an Excel Add-In. To
do this, simply choose Save As and in the "Save as type"
selection, choose Excel Add-In. Distribute this file to your
users.

 

[teatree]

you guys are so fast!

i was about to say the same thing about personal.xls, that's more
place to store your own favorite "utilities"

j-walk has a good faq page on addins, it is old, but is still mostl
relevant

http://j-walk.com/ss/excel/faqs/xl95faq7.ht

 

[Bob Phillips]

A point to note about add-ins is that, although you can open them as any
other workbook, it is better to install them (Tool>Addins and browse to the
file, then check the entry), as this will then get loaded each time Excel
starts, avoiding re-opening.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

The personal.xls file is irrelevant in this context. What you
want to do is create an add-in. Put all the forms and code in to
a single workbook and the save that file as an Excel Add-In. To
do this, simply choose Save As and in the "Save as type"
selection, choose Excel Add-In. Distribute this file to your
users.

 

[help_wanted]

I have created a workbook containing the macro that I need t
distribute... saved it as an .xla and installed it via Add-Ins. Whe
I open a workbook I see the addin load but the macro is not available.
It is still looking for an open workbook containing the macro.... An
suggestions??? Is this only for user forms ??? Thanks for th
tips on add-ins and thanks in advance for any other info.

 

[matt4003]

I am having the same problem after creating the Add-in....the macr
isn't available.

Also, I am trying to maintain 7 different files which all use the sam
macro to formate the input and layout of the data. As we continue t
develop our biz processes I am going to need to update the macro tha
controls these files. All the files are in the same shared directory.
It justs takes too long to open every file, manually update the macr
and save everything, files located on a network drive in Germany, take
a while to save.....any thoughts on how I could distribute the code t
these files?

 

[Bob Flanagan]

Matt, if a file is an add-in, the macros are not visible by design. The
solution is to either create a commandbar and assign the macros to buttons
on it, or to add a new menu and menu items to the Excel worksheet menu. My
recommendation is the second. It is easier. And, when you close Excel,
have Excel delete the menu you added.

Have the users keep the add-in in a particular directory, say program
files\MyAdd-ins. And load it as an add-in via Tools, Add-ins. The name
that appears on the add-in list is the title property of the workbook. You
may have to convert to a workbook, set the title, then convert back (IsAddin
is a workbook property you can change).

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[John Mason]

Hello,

I recently created some new EXCEL VB tools that are becoming popular in
my office. I put all the VB code in one workbook and saved it as an
XLA file. This file is then copied under the c:\program files\
directory in every users PC.

I have created excel workbook templates that interface this software.
First, the new addin file is selected from the TOOLS|ADDIN dialog.
These templates have several macro buttons on the spread sheets that
call various sub procedures in the add-in.

Note, when assigning a macro to the button, the list does not show the
addin sub procedures. I just typed in the name of the sub in the
assign Macro dialog box.

This works great until I found a little problem. When the template is
copied from the server to the hard drive and then back to the server,
Excel tries to "fix" the relative path of the addin file that contains
the macro assigned to the button. Now the button is looking for the
add-in in "\\serveraddress\program files\" instead of "c:\program
files\". This is quite annoying since now the template buttons needs
to be re-assigned back to the macros.

How can I keep the Excel from changing the path of the add-in macro
assigned to on sheet macro buttons?

Thanks for your help.

 

[Vlad Zelenko]

John,

From what I understand, Add-In files (such as .XLA) are automatically found
by Excell if placed in User's directory As Follows:

<SYSTEM ROOT>\Documents and Settings\<user_name>\Application
Data\Microsoft\AddIns

The above path is for Windows 2000/XP systems. Double-check the path for 98
and before.

Hope this helps.

 

[Vlad Zelenko]

Bob,

Isn't there a way to create Excel Add-In that exposing some public functions
accessible from the worksheet? Otherwise, how does the "Analysis ToolPack -
VBA" add-in work?

If I am misguided, please correct me.

Thanks.

Matt, if a file is an add-in, the macros are not visible by design. The
solution is to either create a commandbar and assign the macros to buttons
on it, or to add a new menu and menu items to the Excel worksheet menu. My
recommendation is the second. It is easier. And, when you close Excel,
have Excel delete the menu you added.

Have the users keep the add-in in a particular directory, say program
files\MyAdd-ins. And load it as an add-in via Tools, Add-ins. The name
that appears on the add-in list is the title property of the workbook. You
may have to convert to a workbook, set the title, then convert back (IsAddin
is a workbook property you can change).

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel