Hi

I have a spreadsheet which uses a number of macros and forms to control
entering of data relating to a product. Each new product requires a new
instance of the spreadsheet so I open a 'Master' copy and then save with a
new name.
Creating copies in this way gives a problem if I edit any of the code or
form structure as I have to go back through each instance and import the
affected modules and forms previously exported from the MASTER.

So. is it possible to have my code loaded in separate file and access this
remotely from the spreadsheets?
OR
Create a macro in the spreadsheet which will delete all current modules
(apart from the one containing itselr) and froms then import the code from an
external file? In other words automate the task I am having to do.

Look at VBIDE.CodeModule.AddFromFile.
You will need a reference to the VBE extensibility library.
Bear in mind that after importing the code it will need a re-compile, so
all your variables will be reset and forms will need to be unloaded etc.

RBS

"GDCobra" wrote in message
...
Hi

I have a spreadsheet which uses a number of macros and forms to control
entering of data relating to a product. Each new product requires a new
instance of the spreadsheet so I open a 'Master' copy and then save with a
new name.
Creating copies in this way gives a problem if I edit any of the code or
form structure as I have to go back through each instance and import the
affected modules and forms previously exported from the MASTER.

So. is it possible to have my code loaded in separate file and access this
remotely from the spreadsheets?
OR
Create a macro in the spreadsheet which will delete all current modules
(apart from the one containing itselr) and froms then import the code from
an
external file? In other words automate the task I am having to do.

Thanks for the reply RB. Please excuse my ignorance, I have looked for
information on 'VBIDE.CodeModule.AddFromFile' (and the individual
constituents) in the help file but nothing comes back. Just to clarify I am
programming this through the VB environment in Excel 2003, am I looking in
the right place?

"RB Smissaert" wrote:

Look at VBIDE.CodeModule.AddFromFile.
You will need a reference to the VBE extensibility library.
Bear in mind that after importing the code it will need a re-compile, so
all your variables will be reset and forms will need to be unloaded etc.

RBS

"GDCobra" wrote in message
...
Hi

I have a spreadsheet which uses a number of macros and forms to control
entering of data relating to a product. Each new product requires a new
instance of the spreadsheet so I open a 'Master' copy and then save with a
new name.
Creating copies in this way gives a problem if I edit any of the code or
form structure as I have to go back through each instance and import the
affected modules and forms previously exported from the MASTER.

So. is it possible to have my code loaded in separate file and access this
remotely from the spreadsheets?
OR
Create a macro in the spreadsheet which will delete all current modules
(apart from the one containing itselr) and froms then import the code from
an
external file? In other words automate the task I am having to do.

Did you add the reference in the Excel VBE under Tools, References?
If you have done that then look in the object browser under Classes:
CodeModule and then
in right pane Member of CodeModule: AddFromFile.
Right-click that and click the help.

RBS


"GDCobra" wrote in message
...
Thanks for the reply RB. Please excuse my ignorance, I have looked for
information on 'VBIDE.CodeModule.AddFromFile' (and the individual
constituents) in the help file but nothing comes back. Just to clarify I
am
programming this through the VB environment in Excel 2003, am I looking in
the right place?

"RB Smissaert" wrote:

Look at VBIDE.CodeModule.AddFromFile.
You will need a reference to the VBE extensibility library.
Bear in mind that after importing the code it will need a re-compile, so
all your variables will be reset and forms will need to be unloaded etc.

RBS

"GDCobra" wrote in message
...
Hi

I have a spreadsheet which uses a number of macros and forms to control
entering of data relating to a product. Each new product requires a
new
instance of the spreadsheet so I open a 'Master' copy and then save
with a
new name.
Creating copies in this way gives a problem if I edit any of the code
or
form structure as I have to go back through each instance and import
the
affected modules and forms previously exported from the MASTER.

So. is it possible to have my code loaded in separate file and access
this
remotely from the spreadsheets?
OR
Create a macro in the spreadsheet which will delete all current modules
(apart from the one containing itselr) and froms then import the code
from
an
external file? In other words automate the task I am having to do.

RB - What should I be linking under ToolsReferences?
There's nothing n the availible list that looks like a likely candidate and
if I click on browse it is looking for olb, tlb or dll files. Should I be
compiling my code and linking to that. Sorry for being a numpty but I'm just
about to finish for the night so I'm in a bit of a hurry. Thanks for your
help so far, I feel I am getting somewhere.

"RB Smissaert" wrote:

Did you add the reference in the Excel VBE under Tools, References?
If you have done that then look in the object browser under Classes:
CodeModule and then
in right pane Member of CodeModule: AddFromFile.
Right-click that and click the help.

RBS


"GDCobra" wrote in message
...
Thanks for the reply RB. Please excuse my ignorance, I have looked for
information on 'VBIDE.CodeModule.AddFromFile' (and the individual
constituents) in the help file but nothing comes back. Just to clarify I
am
programming this through the VB environment in Excel 2003, am I looking in
the right place?

"RB Smissaert" wrote:

Look at VBIDE.CodeModule.AddFromFile.
You will need a reference to the VBE extensibility library.
Bear in mind that after importing the code it will need a re-compile, so
all your variables will be reset and forms will need to be unloaded etc.

RBS

"GDCobra" wrote in message
...
Hi

I have a spreadsheet which uses a number of macros and forms to control
entering of data relating to a product. Each new product requires a
new
instance of the spreadsheet so I open a 'Master' copy and then save
with a
new name.
Creating copies in this way gives a problem if I edit any of the code
or
form structure as I have to go back through each instance and import
the
affected modules and forms previously exported from the MASTER.

So. is it possible to have my code loaded in separate file and access
this
remotely from the spreadsheets?
OR
Create a macro in the spreadsheet which will delete all current modules
(apart from the one containing itselr) and froms then import the code
from
an
external file? In other words automate the task I am having to do.

Look for:
Microsoft Visual Basic for Applications Extensibility 5.3
The number could be different.

RBS

"GDCobra" wrote in message
...
RB - What should I be linking under ToolsReferences?
There's nothing n the availible list that looks like a likely candidate
and
if I click on browse it is looking for olb, tlb or dll files. Should I be
compiling my code and linking to that. Sorry for being a numpty but I'm
just
about to finish for the night so I'm in a bit of a hurry. Thanks for your
help so far, I feel I am getting somewhere.

"RB Smissaert" wrote:

Did you add the reference in the Excel VBE under Tools, References?
If you have done that then look in the object browser under Classes:
CodeModule and then
in right pane Member of CodeModule: AddFromFile.
Right-click that and click the help.

RBS


"GDCobra" wrote in message
...
Thanks for the reply RB. Please excuse my ignorance, I have looked for
information on 'VBIDE.CodeModule.AddFromFile' (and the individual
constituents) in the help file but nothing comes back. Just to clarify
I
am
programming this through the VB environment in Excel 2003, am I looking
in
the right place?

"RB Smissaert" wrote:

Look at VBIDE.CodeModule.AddFromFile.
You will need a reference to the VBE extensibility library.
Bear in mind that after importing the code it will need a re-compile,
so
all your variables will be reset and forms will need to be unloaded
etc.

RBS

"GDCobra" wrote in message
...
Hi

I have a spreadsheet which uses a number of macros and forms to
control
entering of data relating to a product. Each new product requires a
new
instance of the spreadsheet so I open a 'Master' copy and then save
with a
new name.
Creating copies in this way gives a problem if I edit any of the
code
or
form structure as I have to go back through each instance and import
the
affected modules and forms previously exported from the MASTER.

So. is it possible to have my code loaded in separate file and
access
this
remotely from the spreadsheets?
OR
Create a macro in the spreadsheet which will delete all current
modules
(apart from the one containing itselr) and froms then import the
code
from
an
external file? In other words automate the task I am having to do.