Just A Suggestion

[Bob Calvanese]

I have been reading a lot of posts and see a lot of hard coding as well as
macro's within excel itself.

I was once a reporting automations developer for a telemarketing company. I
was responsible for all financial reporting and there was another guy who
did all the insurance reporting automation.

All of our reports where in excel and where all custom templates sent to us
by our clients. We inturn turned the templates into the reports and
automated them so the reporters could run them.

My approach to this was to find a way to control the automation out side of
excel using VB6. It took a year, but I developed a system that used the
template as a map that my system would scan. It would then create a set of
tables that would hold the information needed to get the data from oracle,
open the report file, and populate the report. I used made up key words that
the reporters could understand and key into their report template where they
would normally input the data. This enabled them to set up their reports
into my system without having to know anything about programming. Once they
set the report up they could run it every day with the click of a button. If
the client made changes to the template, the reporters would merely make the
same changes to their template, rescan it, and it would be ready to run
again.

The guy who automated the insurance reporting had a different approach. He
took each report and wrote macro's in excel for each one. This turned into a
nightmare for him, because he had to maintain all the reports indavidually,
and when the client wanted changes he had to re-code the report each time.
Needless to say he became stuck in his position because he was the only one
who could maintain all of his propriatary reports.

Because my system eliminated the need for my position (aside from
maintaining one system), I was able to move into the programming department.
After the other guy left (he put himself in a position where he had no where
else to go), I took all his reports, stripped out his macro's, and scanned
them into my system.

The point to all of this is... If you have a lot of excel automation. Do not
use macro's within excel to do it. You will wind up with a nightmare of
propriatary code to maintain, and you will be the one stuck with it (unless
that is what you want).

If you only have a few automation things to do, then macro's are great.

Just a suggestion.

 

[Tom Ogilvy]

Everyone has opinions, so here is mine:
Building a custom solution for each variation of a largely similar process
will be problematic as you describe. Beyond that, the choice of whether to
program in Excel itself or use Automation from VB6 has little direct impact.
A single workbook can hold a "master" app which operates on other workbooks
as well.


If I replaced this sentence:

My approach to this was to find a way to control the automation out side of
excel using VB6.

with
My approach to this was to develop a generalized macro in a single
workbook in Excel to control the generation of the reports.

The story would be no different.

 

[Bob Calvanese]

Everyone has opinions, so here is mine:
Building a custom solution for each variation of a largely similar process
will be problematic as you describe. Beyond that, the choice of whether
to
program in Excel itself or use Automation from VB6 has little direct
impact.
A single workbook can hold a "master" app which operates on other
workbooks
as well.

I am talking about a system that can be installed on any computer right from
the network and can utomate any excel report. VB6 was just the language that
I chose to use. This system built all needed SQL on the fly and retrieved
the data from an oracle database, as well as maintained sets of tables for
each report on the fly. The system also maintained directories and many
other things, as well as being an executable and installing all necissary
dll's upon installation .

This is not an oppinion, but fact. I am just trying to make a suggestion on
something that I have learned through experience.

If I replaced this sentence:
with
My approach to this was to develop a generalized macro in a single
workbook in Excel to control the generation of the reports.

The story would be no different.

Again, I am talking about an entire system. Not a generalized macro, not to
mention that because the macro is actually a part of the excel file... file
size becomes an issue as well as macro security issues that arise.

Even if it could be done in excel, it would be better suited for a full
fledged language that can be compiled into an executable, not to mention all
the work arounds that would have to be done to make up for the limitations
of VBA.

I have nothing against macro's, but it would be foolish to have to maintain
300+ reports per day using them. It just would not be an efficiant way of
doing it.
--
Regards,
Bob Calvanese

 

[Guest]

Guess I will take my turn: To me it just gets back to some of the
fundamentals of programming. I learned to program a long time ago (way too
long ago!) before there even was a "PC" and maybe things have changed, but I
don't think so. Back then, when you had to hard code just about everything,
you learned pretty quickly that any system you wrote you would also have to
maintain. So well-documented code was a must. Modular programming was not
as easy to write, but necessary for many reasons: easier to maintain, easier
to reuse, and (a significant concern back then!) did not gobble up additional
memory.

My point: Any programming language -VBA or VB or C# or whatever - is merely
a tool to do the work, and (for the purposes described) all have the
capability to automate Excel and other Office apps. There are pros and cons
to any of them, the choice of which to use is highly dependent on the user
and the environment. I use VBA because that is what I have available - my
employer is not about to purchase VB or C# for me. But if my code is well
documented, modular (keep in mind you can export your code and have a code
library set up, or can even add modules at runtime) and smartly written, it
should be easy for me to maintain and deploy - and for others to continue to
use when I am no longer around. These factors are influnced much more by HOW
the code is written, not what development environment was used.

And, Bob, I must disagree:

"This is not an oppinion, but fact."

You do state some facts (i.e. difficulties you had, what you did to overcome
them) but in generalizing your approach to fit every similar situation you
are indeed stating opinion, not fact.

K Dales

Everyone has opinions, so here is mine:
Building a custom solution for each variation of a largely similar process
will be problematic as you describe. Beyond that, the choice of whether
to
program in Excel itself or use Automation from VB6 has little direct
impact.
A single workbook can hold a "master" app which operates on other
workbooks
as well.

I am talking about a system that can be installed on any computer right from
the network and can utomate any excel report. VB6 was just the language that
I chose to use. This system built all needed SQL on the fly and retrieved
the data from an oracle database, as well as maintained sets of tables for
each report on the fly. The system also maintained directories and many
other things, as well as being an executable and installing all necissary
dll's upon installation .

This is not an oppinion, but fact. I am just trying to make a suggestion on
something that I have learned through experience.

If I replaced this sentence:
with
My approach to this was to develop a generalized macro in a single
workbook in Excel to control the generation of the reports.

The story would be no different.

Again, I am talking about an entire system. Not a generalized macro, not to
mention that because the macro is actually a part of the excel file... file
size becomes an issue as well as macro security issues that arise.

Even if it could be done in excel, it would be better suited for a full
fledged language that can be compiled into an executable, not to mention all
the work arounds that would have to be done to make up for the limitations
of VBA.

I have nothing against macro's, but it would be foolish to have to maintain
300+ reports per day using them. It just would not be an efficiant way of
doing it.

 

[Bob Calvanese]

I am only trying to post a suggestion to help people not to get caught up in
a bunch of propriatary macro's, and be the one stuck maintaining them. If
the insurance guy that I mentioned in my original post would have listened
to me when I suggested a more centralized approach, he probably would have
got into the programming department and not stuck with the nightmare that he
brought on himself. He thought that if he became the macro guru that he
would have job security. Little did he know that he was setting his own
trap. He had to leave because the company did not want to risk someone else
having to maintain all those indavidual reports, and he could not take it
anymore.

Oppinion... Fact... Call it what you want. If someone wants to maintain
several hundred indavidual propriatary macro's rather than one system...
It's up to them.

I am only trying to pass on a little experience. Maybe I should not have
mentioned any particular language (my bad), But I still think a system like
that would be best suted for a full fledged language that can be made
executable (IMHO).

Best Regards

 

[Guest]

OK, I think in general we are in agreement. The main point is one that I
think you, Tom and I all agree with: for any custom solution developed the
person developing it bears responsibility for its maintenance over time (and
making it accessible and maintainable even after they are no longer around).