It happens that formulated :
On Tue, 22 Jun 2010 19:59:09 -0400, GS wrote:

suggestion...
Instead of calling the proc you want to run after the addin opens, why
not move that procs content into the Workbook_Open event or an
Auto_Open proc in a standard module? That would eliminate need for
pausing your code.

The add in reads data that a user enters into a worksheet, performs some
data valadation (beyond what Excel can do at data entry time), does a mess
of computations and outputs two files that are used to control a machine
that fabricates a product.

It is essential that the data be separated from the code, so that when the
code needs to be upgraded, all the workbooks that use the code do not need
to be changed. I can just email a new copy of the .xla, they save it to
the appropriate folder and everything is good to go. All workbooks are
effectively updated.

I assume the code is in the XLA. What I don't fully understand is where
the code is that you use to set the addin's Installed prop. This leads
me to conclude that the structure of your project may not be as
efficient as it could be to accomplish your task goals. More info about
this would be helpful in order to provide better suggestions.


Also, is there any reason why you can't open the addin via:
Set wkbAddin = Workbooks.Open(FileName:="C:\RollForm.xla")
I'm not sure. I tried doing this and it did not execute Workbook_Open in
ThisWorkbook of the addin. I tried calling a routine from ThisWorkbook in
the originally opened workbook and it could not find it.

Strange! Are you sure an error didn't occur? This would interupt the
Workbook_Open event code and so make it appear as it did not execute.


I'm not familiar with having an addin that is not it the addins collection.

Addins do not have to be members of the Addins collection to be used.
They do have to be members if you want them available via the UI's
Addins Manager dialog.


How do I do it?

Just as I showed you! -Open the XLA same as any other workbook. You
could use a 'frontloader' addin to do this. A frontloader addin is a
member of the addins collection that adds a menuitem to Excel's menubar
so users can run your non-member addin when needed. This is where I
would put whatever code you currently use to set the addin's Installed
prop, except I would use Workbooks.Open as suggested.

What are the advantages?

There are several advantages depending on your level of skill and how
you want your project structured. For example:
- you can give your addin any filename extension that you want so it
doesn't appear in FileOpen dialogs that are filtering for common Excel
filename extensions.
- changing the filename extension also makes it harder for users to
find your addin via Windows Explorer.
- not having the addin loaded until used makes Excel startup faster
and frees up more system resources that would otherwise be used by an
installed addin.
- frontloader addins are usually smaller and can be used to very
startup conditions before loading your main addin. This could be any
criteria necessary for your addin to work properly. (ie: required
workbooks be open, required data is available, etc.)
- works well when automating instances of MS Office apps.
- you can open-password protect your addins so that only your
frontloader can open them. This is the password that you set in the
SaveAs dialog via the Tools, General Options menu. If anyone tries to
open your addin via the UI they will be prompted for the password.
- your addin can be configured to lock down Excel's UI so as to
prevent users from accessing your code via the VBA IDE.

...just to name a few!


Thanks.

BTW - I am a very experienced programer, but I don't know much about VBA
(or VB). Is there a good source on the web that documents all of the
objects and their properties and methods? I am constantly finding out
about new ones, yet many times when I search for them in Excel's help it
does not find anything.

The best place to look is in the Object Explorer of VBA's IDE. The lang
reference (F1) online help is available in that window for any selected
item.

As for good books on Excel VBA, anything by John Walkenback is going to
be of benefit.

Also, you may still be able to find editions of Wrox's Programmer to
Programmer series of Excel <version VBA by authors Bovey, Bullen,
Green. I highly recommend anything by these authors if you're
interested in Excel VBA programming at the professional level.
Unfortunately, this series is discontinued and so Excel 2007 VBA is the
last title to be published by these authors.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc