I have a huge library of extremely complex code that we run every
night. In general terms, 1/3rd of that code is used only once a day
when the book is created. Another 1/3rd is utility code that could be
removed to some other location. Finally, there is the last third that
needs to stay with the book and the users interact with.

The code is currently located in a 2.8 meg XLA on a network drive.
This is also a problem because the first person that opens the book
"locks" the XLA and I can no-longer save changes to it. Additionally,
if the network falters even for a second, the link to the XLA "goes
bad" and every command starts returning random errors.

So what I'm thinking about doing is moving the code the user touches
into a separate library of some sort, and then saving it directly into
the book. That should eliminate both problems, as well as ensure they
have the right code version for the book they have open.

So finally, here's my question: if I place the code that needs to be
with the book into an XLA, can I copy it into the spreadsheet's
modules via VBA? Are there general-purpose commands for moving code
around in general?

Maury

Workbooks("Book1").VBProject.VBComponents("Userfor m1").Export _
Filename:="C:\temp.frm"
Workbooks("Book3").VBProject.VBComponents.Import _
Filename:="C:\temp.frm"
Kill "C:\temp.frm"


--
__________________________________
HTH

Bob

"Maury Markowitz" wrote in message
...
I have a huge library of extremely complex code that we run every
night. In general terms, 1/3rd of that code is used only once a day
when the book is created. Another 1/3rd is utility code that could be
removed to some other location. Finally, there is the last third that
needs to stay with the book and the users interact with.

The code is currently located in a 2.8 meg XLA on a network drive.
This is also a problem because the first person that opens the book
"locks" the XLA and I can no-longer save changes to it. Additionally,
if the network falters even for a second, the link to the XLA "goes
bad" and every command starts returning random errors.

So what I'm thinking about doing is moving the code the user touches
into a separate library of some sort, and then saving it directly into
the book. That should eliminate both problems, as well as ensure they
have the right code version for the book they have open.

So finally, here's my question: if I place the code that needs to be
with the book into an XLA, can I copy it into the spreadsheet's
modules via VBA? Are there general-purpose commands for moving code
around in general?

Maury

If the code is protected then you are not going to have much luck. Otherwise
check out this link...

http://www.cpearson.com/excel/vbe.aspx
--
HTH...

Jim Thomlinson


"Maury Markowitz" wrote:

I have a huge library of extremely complex code that we run every
night. In general terms, 1/3rd of that code is used only once a day
when the book is created. Another 1/3rd is utility code that could be
removed to some other location. Finally, there is the last third that
needs to stay with the book and the users interact with.

The code is currently located in a 2.8 meg XLA on a network drive.
This is also a problem because the first person that opens the book
"locks" the XLA and I can no-longer save changes to it. Additionally,
if the network falters even for a second, the link to the XLA "goes
bad" and every command starts returning random errors.

So what I'm thinking about doing is moving the code the user touches
into a separate library of some sort, and then saving it directly into
the book. That should eliminate both problems, as well as ensure they
have the right code version for the book they have open.

So finally, here's my question: if I place the code that needs to be
with the book into an XLA, can I copy it into the spreadsheet's
modules via VBA? Are there general-purpose commands for moving code
around in general?

Maury

On Jun 24, 3:04*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
If the code is protected then you are not going to have much luck. Otherwise
check out this link...

Thanks! And no, I don't bother protecting my code.

This might not be the right way to do this though, I might be looking
for a tech solution to a user-space problem. I'll keep thinking about
it.

Maury

Shared XLA's can be a problem - particularly if you need to do updates.
Something which has worked for me is to store the main xla in a web
(intranet) location and have some code in the main workbook which opens the
xla (thus copying it into the "temporary internet files" folder on the
user's PC). That way the main xla is not locked and it can be updated any
time.

Tim

"Maury Markowitz" wrote in message
...
I have a huge library of extremely complex code that we run every
night. In general terms, 1/3rd of that code is used only once a day
when the book is created. Another 1/3rd is utility code that could be
removed to some other location. Finally, there is the last third that
needs to stay with the book and the users interact with.

The code is currently located in a 2.8 meg XLA on a network drive.
This is also a problem because the first person that opens the book
"locks" the XLA and I can no-longer save changes to it. Additionally,
if the network falters even for a second, the link to the XLA "goes
bad" and every command starts returning random errors.

So what I'm thinking about doing is moving the code the user touches
into a separate library of some sort, and then saving it directly into
the book. That should eliminate both problems, as well as ensure they
have the right code version for the book they have open.

So finally, here's my question: if I place the code that needs to be
with the book into an XLA, can I copy it into the spreadsheet's
modules via VBA? Are there general-purpose commands for moving code
around in general?

Maury

On Jun 25, 12:01*am, "Tim Williams" <timjwilliams at gmail dot com
wrote:
Shared XLA's can be a problem - particularly if you need to do updates.
Something which has worked for me is to store the main xla in a web
(intranet) location and have some code in the main workbook which opens the
xla (thus copying it into the "temporary internet files" folder on the
user's PC). *That way the main xla is not locked and it can be updated any
time.

Hmm, this sounds very interesting. I've never linked to an XLA over
the intranet though, how do you do this?

The other problem with my current line of thinking is that it's
annoying to refer to code in one XLA from another. This really works
against modularizing the code. Someone suggested using References for
this, but I have yet to get this to work. I could also re-write the
really low-level utility routines in pure VB and link to them, but
then you loose all debugging capability, which is a huge price to pay.

Maury