a little help with an Excel project, pleaze...

[theBEE]

hi all.

My current project is giving me problems with how to go about it.
I know how to do the code and all, but of what method to implement?
Questions will following explanation.

Heres the layout...

We have a master Excel workbook template.
This master will be copied multiple times for multiple vehicles, these are
data workbooks.
The data workbooks will be stored in a Orcale database.
Users will periodically modify the individual copies that are the data
workbooks.
Requirements require that the workbook be security and lock from user
interface except through use of GUIs.

Hopefully, this is a paints the picture of what is required.

I first took the ideal of VBA code with a set of internal GUIs.
All was working until it dawn on me that every time the master template
workbook is copied, also the code is copied.
If I had to make an update/change to the code, there will be hundreds of
these workbooks distributed globally through the Orcale DB.
This is very poor maintance. The code is useful, but I have to find another
method. I got to separate the code from the data.

Next, I create a source workbook that would contain the VBA code and GUIs.
This source workbook will be deployed through our standard deployment plan
and could easily be updated when required.
The actual data workbooks would execute the code that is contained within
the source workbook.

Now, I thinking of just writing a visual basic application that is called
from the data workbooks.
The VB executable would be deployed and maintained with our standard
deployemnt plan.

Any other ideals or solutions???? It can not be an addin, the user can
disable addins.

I am normally a C/C++ programming with medium level skills with VB, not much
of a .Net person yet.
Been too busy to take the time to convert over to .Net, C# is out of the
question for this project.

Any suggestions or other ideals, I am always open to ideals. Currently I
have no one to bounce ideals off of.

Thanks in advance.

theBee.

 

[NickH]

Hi theBEE,

As you rightly say, it is always a good idea to keep code separate
from data.

How about storing the information within Oracle as database tables
rather than workbooks. Each user could then have their own copy of a
master workbook, containing your code, plus a template workbook.

When the user launches the master workbook it could prompt them to ask
which workbook (but its really a table) they wish to update. The
master workbook could then populate a copy of their template with data
from the appropriate table(s). They could then make changes to the
template before pressing a submit button at which point your code does
any necessary validation checks before writing back to the database
and killing the template copy.

If you find it helpful, it won't hurt to include the submission code
in the template because the template itself is not subject to
corruption from data and since the copies are only temporary you don't
need to worry about keeping them up to date.

HTH NickH