Just A Suggestion

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.

--
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.


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.
--
Regards,
Tom Ogilvy


"Bob Calvanese" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> --
> Bob Calvanese
>
>

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> 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:
>> 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.

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
>
> "Bob Calvanese" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>>
>> --
>> Bob Calvanese
>>
>>
>
>

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

"Bob Calvanese" wrote:

> "Tom Ogilvy" <[email protected]> wrote in message
> news:%[email protected]...
> > 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:
> >> 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.
>
> 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
> >
> > "Bob Calvanese" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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.
> >>
> >> --
> >> 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

--
Bob Calvanese
"K Dales" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Bob Calvanese" wrote:
>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:%[email protected]...
>> > 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:
>> >> 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.
>>
>> 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
>> >
>> > "Bob Calvanese" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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.
>> >>
>> >> --
>> >> Bob Calvanese
>> >>
>> >>
>> >
>> >
>>
>>
>>

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).

"Bob Calvanese" wrote:

> 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
>
> --
> Bob Calvanese
> "K Dales" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > "Bob Calvanese" wrote:
> >
> >> "Tom Ogilvy" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > 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:
> >> >> 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.
> >>
> >> 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
> >> >
> >> > "Bob Calvanese" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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.
> >> >>
> >> >> --
> >> >> Bob Calvanese
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>
>