Dynamically generating a word document from oracle data

[G Dahler]

Hello,

Sorry if I'm posting to the wrong newsgroup: I really don't know where to
post this question !

We need to be able to generate a word document, from a template, and fill in
some "blanks" using data coming from an oracle database. We need the
solution to be as simple as possible. Scalability is NOT an issue here, we
will have a very small number of users.

The generation could occur from a web page, or from a fat client, we don't
care. We have a native oracle client installed as well as ODBC drivers to
oracle.

Of course, the best solution would be a web page.

Here is an example of what we need.

a) The end user has to input some id (Ex: client id) (In a text box in a
word document or on a web page)

b) After the "id" is filled, the macro/program/application has to fetch some
data from an oracle database and fill in some fields in a word document.

For example, the original document template could be:

Dear Mr _Z_,

Our records indicate that on _X_, you asked for information about our new
product _A_. We sent you an invoice #(_B_) for the amount of _C_

After inputing the customer id, our end user should end up with a word
document containing something like:

Dear Mr Smith,

Our records indicate thant on May 26th 2003, you asked for information about
our new product "Ultra carpet cleaner". We sent you an invoice #8121 for the
amount of 23.87$

What is the easiest/cheapest way to do it.

We are running office 2000 premium. We are NOT a microsoft shop, we run on
UNIX and Netware. If we could only use VB for application or jscript, that
would be fine. We simply don't know where to look at, which doc to read. We
may be hiring a consultant to do this, but we presume it is VERY easy to do
and don't want to end up with a costly solution.

We already have some sort of prototype using excel and word, but it's very
complicated and we cannot close the excel app after getting the data from
oracle. It was only an experiment and is way too complicated.

Thanks

 

[Cindy M -WordMVP-]

Hi G,

Since you're posting in a "Developer" group I'm going to give you a
"developer" level answer, to begin with. I suspect that's not what you want,
however, and will follow outlining some basic dev approaches with a minimal
"nuts-and-bolts" answer.
--
Given all the constraints you mention, have you considered generating an HTML
file using script from the webpage, then opening that in Word and printing?

Another option would be to use script (or whatever) to retrieve the data from
Oracle, generate a delimited text file (or HTML table), then use this as a
data source for Word's built-in mail merge. This would certainly be an easy
way for the user to set up the Word document and indicate where the data
coming from Oracle should go. Chances are, you wouldn't have to automate Word
at all, if I'm understanding the task correctly.

The more "classic" approach, if you're dealing with a given set of
documents/templates, would be to insert bookmarks as the data targets,
automate Word
- open the document
- insert the data into the bookmarks
- print, close, etc.
--
Now, assuming you want to minimize programming, mailmerge is likely the way to
go. Since you have Oracle ODBC drivers, I'd say mail merge ought to be able to
link directly to Oracle. And you'd probably need to use MSQuery for this. If
the only criterium the user should make is to specify the record ID, this
should work.

When you go into "Open Data Source" you should see a button for MS Query;
click it. Now you should get a list of ODBC drivers for various types of data
bases; choose the Oracle driver. Depending on how the ODBC driver has been
configured, this could immediately open the database and allow the user to
select the table he needs, or he might need to navigate to the database, enter
passwords, that kind of thing. I have to leave that up to the consultant you
hire as I've never personally mail-merged to Oracle. Then the user displays
the Query grid, chooses the field he wants to query on, and types in the ID.

If this is too much work for the users, a mid-way solution would be to use a
macro (VBA) to set up the link to the Oracle database, then display a box
where the user can type in the ID to select the record.

If you want to follow up on either of the above suggestions after talking to
the consultant, I suggest posting in the word.mailmerge.fields newsgroup.

We need to be able to generate a word document, from a template, and fill in
some "blanks" using data coming from an oracle database. We need the
solution to be as simple as possible. Scalability is NOT an issue here, we
will have a very small number of users.

The generation could occur from a web page, or from a fat client, we don't
care. We have a native oracle client installed as well as ODBC drivers to
oracle.

Of course, the best solution would be a web page.

Here is an example of what we need.

a) The end user has to input some id (Ex: client id) (In a text box in a
word document or on a web page)

b) After the "id" is filled, the macro/program/application has to fetch some
data from an oracle database and fill in some fields in a word document.

For example, the original document template could be:

Dear Mr _Z_,

Our records indicate that on _X_, you asked for information about our new
product _A_. We sent you an invoice #(_B_) for the amount of _C_

After inputing the customer id, our end user should end up with a word
document containing something like:

Dear Mr Smith,

Our records indicate thant on May 26th 2003, you asked for information about
our new product "Ultra carpet cleaner". We sent you an invoice #8121 for the
amount of 23.87$

What is the easiest/cheapest way to do it.

We are running office 2000 premium. We are NOT a microsoft shop, we run on
UNIX and Netware. If we could only use VB for application or jscript, that
would be fine. We simply don't know where to look at, which doc to read. We
may be hiring a consultant to do this, but we presume it is VERY easy to do
and don't want to end up with a costly solution.

We already have some sort of prototype using excel and word, but it's very
complicated and we cannot close the excel app after getting the data from
oracle. It was only an experiment and is way too complicated.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail