Importing/deleting .bas files (a lot)? Repost

[KR]

I am challenged with a project where about 250 users will all have their own
workbooks in a shared area, but I need to minimize the effort associated
with code updates and some worksheets with reference data.

One option that has come up is to keep the code modules separate (as .bas
files and whatever userforms export as) in the same
network drive, then each time a user opens their file, import the
appropriate modules- so the only code in each workbook would be in the
onopen event (load all bas files) and the close event (remove all bas
modules). That way, if I ever need to make changes, I only have to change
one set of .bas files to affect all the workbooks.

However, I've had trouble when I try to remove .bas files using a second
workbook (on a separate project)...even when I put in time delays, save and
close the file, etc., it hasn't been reliable. It often simply doesn't
remove the old .bas file(s).

Anyone have any thoughts on this .bas import/delete method, as opposed to
keeping all the code in a separate workbook? With the separate workbook
option, I'd need to hide the code workbook so the user didn't switch windows
and close it. I'd also need to search for it and close it when they close
their individual workbook... It sounds like there would be all sorts of
potential for problems in that approach too....

Thanks for any advice,
Keith

 

[Harald Staff]

Hi Keith

Don't import/export code and modules, it's messy and vulnerable. Put your
code in an addin instead:
http://erlandsendata.no/english/index.php?d=envbaaddinsgeneral
addins doesn't have windows and can't easily be closed during the session.

HTH. Best wishes Harald

 

[KR]

Harald-

Thank you for the link. I haven't used XLAs before, so please forgive the
rather basic question, as I'm trying to understand the approach I should
take to use an add-in, since I won't have access to each person's PC. Which
of the following is the better approach?

Give all users a shortcut to the XLA on the server
When they open it (read-only) have it check their UserID and automatically
open their individual workbook
(XLA remains hidden, but loaded)
When they are done, they close their own workbook
Code in each workbook's onClose event also closes or unloads the XLA

Option 2: each person has a shortcut to their own workbook, and each
individual workbook loads and unloads the XLA directly

There are some strong logistical advantages of the first approach, because
we need to be able to create new workbooks on the fly if one doesn't already
exist- but I could get around it by having all shortcuts go to one master
workbook that will open both the XLA and the individual workbook?

I guess what I really need is a better understanding of how the XLA
functions related to other workbooks; I'd assume it is completely
independent (unless VBA code manipulates it), but I'd like to only have it
loaded when one or more of the individual workbooks are open.

Thanks for any advice,
Keith

 

[Harald Staff]

Without extremely analyzing your project <g> I believe that each of your
files should open a single server instance xla on opening itself, and close
it again on close. I'd go for your opotion 2. The "on the fly" problem
could be solver with a template workbook already containing code and design.
Templates are XLT workbooks.

You have to think of how to update the xla too if it's a single file on the
server. If someone among the 250 has it open then you can't overwrite or
update it, so you'll need a "don't open us" setting and maybe a "close us
now" check somewhere.

Your very first xla creation should not have 250 users depending on it. Do a
little testing and learning first, it is worth the time and it is very
powerful and useful knowledge.

HTH. Best wishes Harald