Mail merge with excel to produce an invoice-like letter

[Jacob Cheng]

Dear all,

I am trying to merge an excel file to produce an invoice. I have
studied the examples on the web and noticed that there are two methods
avaliable to produce an invoice. I know that using ODBC connect to the
excel file may be one of the best solution for my problem, but since
ODBC connection is not avaliable in my PC (due to the policy setting

<), I have to find other solution.

Another solution which I have found from the web is using the
combination of NEXTIF and SET. Just like the example from the MS
knowledge base. (http://support.microsoft.com/default.aspx?scid=kb;en-us;105888&Product=wrd)
Unfortunately, the example there is only the solution for knowing the
max number of record to be displayed in a single page. While for my
case, I the max number of record is not known yet. (I think this is
normal in the real world, there won't be any limit for the number of
items to be printed in a single invoice)

Now, I am trying to find if something like "loop" can be done OR if
there are any workaround for the problem.

Can anybody help? Thanks in advance.

Regards,
Jacob

 

[Cindy M -WordMVP-]

Hi Jacob,

When you say you've checked the info on the web, does that include the links on my website?
From the description you give, I'd say not... Check in "Special Merges" and look at the
DATABASE field example, as well as the KB article referenced.

It basically doesn't matter whether ODBC is used or not, but it would be easier to judge about
the connection method if we knew what the data source is?

Another solution which I have found from the web is using the
combination of NEXTIF and SET. Just like the example from the MS
knowledge base. (http://support.microsoft.com/default.aspx?scid=kb;en-us;105888&Product=wrd)
Unfortunately, the example there is only the solution for knowing the
max number of record to be displayed in a single page. While for my
case, I the max number of record is not known yet. (I think this is
normal in the real world, there won't be any limit for the number of
items to be printed in a single invoice)

Now, I am trying to find if something like "loop" can be done OR if
there are any workaround for the problem.

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

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

 

[Jacob Cheng]

Dear Cindy Meister,

Really thanks for your help for my problem, as well as all the
previous cases such that I can reference them before I try to ask.

Thanks again for pointing me to your homepage, the "Special Merges"
section. I want to clarify if the DATABASE field example is the one on
Leigh Weber's website and the KB article is article Q105888? If yes, I
afraid that the KB article cannot solve my problem as I don't want to
duplicate the code {nextif ...}{if ...} for a thousand times. (B'coz
my boss won't accept this)

For the DATABASE field example on Leigh Weber's website, I notice that
it uses ODBC to connect the Access DB source. I agree that this
example fit my problem which I only need to change a little things,
- Change the data source from access to excel and other connection
string
- Change the sql stmt

But since I am lack of the knowledge in these fields, I don't know how
could I make the connection without the help from ODBC. (I got error
message "Error! Cannot open data source." with different combination
of connection string for excel). Can you give me some hints on that.
And, if avaliable, could you show me an example for doing that without
ODBC.

Thanks again for your help.

Regards,
Jacob

P.S. The reason for not using ODBC is due to company policy. I am not
allowed to setup the ODBC for every computer.

 

[Cindy M -WordMVP-]

Hi Jacob,

I want to clarify if the DATABASE field example is the one on
Leigh Weber's website and the KB article is article Q105888? If yes, I
afraid that the KB article cannot solve my problem as I don't want to
duplicate the code {nextif ...}{if ...} for a thousand times. (B'coz
my boss won't accept this)

Yes, and yes.

The KB article: there is no "NextIf" that I see in the solution proposed
by the article. And no need to know in advance how many records, or to
duplicate stuff. Please read the article again, very carefully; the
solution bases on your having a field in the data that uniquely
identifies each 1 side of the 1:n (such as a primary key). When the
records are sorted according to this field, the change will occur when
the content of this field changes. The IF basically checks for that.

For the DATABASE field example on Leigh Weber's website, I notice that
it uses ODBC to connect the Access DB source. I agree that this
example fit my problem which I only need to change a little things,
- Change the data source from access to excel and other connection
string
- Change the sql stmt

But since I am lack of the knowledge in these fields, I don't know how
could I make the connection without the help from ODBC. (I got error
message "Error! Cannot open data source." with different combination
of connection string for excel). Can you give me some hints on that.
And, if avaliable, could you show me an example for doing that without
ODBC.

Note that Leigh's sample is just that, a SAMPLE. The same technique can
be used with any data source, and any connection method (with some
reservations for OLE DB in Word 2002 and later). I suggest that for
this, you experiment with your current data source and the "Insert
Database" button on the Database toolbar. Don't worry yet about linking
to mail merge, just work with it until you can get a proper connection
to your data source and see a table of the data.

Note for ODBC: Are you trying to use MS query? See if it will work for
you *without* using MSQuery.

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

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

 

[Badger]

Jacob,

If you're still needing help, try looking at KB article Q294686.
I think it should do the job as the code does not have to be duplicated like
Q 105888.
And if you're still stuck after looking here, then could I have an example
of what you are trying to achieve and I'll see if I can help.

Regards
Paul

 

[Jacob Cheng]

Dear Paul,

Thanks for your help. I have read a similar KB article (Q211303)
before. It can done without duplicated code but it has other problem.
Before explaining the problem, I am going to show an example for what
type of letter which I am trying to do with mail merge. Consider the
invoice, it has several fields,
- Company Name for the recipient
- Product
- Quantity
- Unit Price
- Amount
- Total

A common way we put these information is placing the Company Name in
the top of the invoice. Then a table for displaying the Product,
Quantity, Unit Price & Amount. At last, There may be a total sum.

INVOICE

To: ABC Company

Product Price Qty. Amount
=======================================================
Computer $2000.00 10 $20000.00
LCD Monitor $500.00 10 $5000.00
...

For the case that invoice is printed one by one, I can genereate the
invoice by simply "typing" the text by OLE connection. But now the
system need to generate bundle of invoice in a single action. This is
the reason why I want to do this by mail merge.

A solution for not using mail merge is to do all the stuffs by
program. But it will decrease the flexibility for future modification
to the invoice. Using some template will allow user to change the
layout without modification in program.

So I got problem in Form Letter Merge, how to display the table for
Product, Price, Quantity & Amount?

If I use Category Merge, the table is no long a problem. But another
problem arise, how to display Company Name and other static wording
like "INVOICE" & "To:"?

Now, I try to do this by saving all the information within the table
in a single cell. Though this can print the invoice correctly, the
flexibility decrease. Leigh Weber's website provides a solution for my
problem but it requires ODBC. (Cindy said the same technique can be
used in other connection method, but I don't know how >_<) Do you have
any suggestion? Thanks.

For your information, I am using MSWord 2000 and a excel file to
perform mail merge. Solution for using MSWord in other version are
welcome but the data source need to be either excel file or a text
file.

Regards,
Jacob

P.S.
The above example is modified from Leigh Weber's website
http://www.knowhow.com/Guides/CompoundMerges/CompoundMerge.htm

 

[Cindy M -WordMVP-]

Hi Jacob,

Leigh Weber's website provides a solution for my
problem but it requires ODBC. (Cindy said the same technique can be
used in other connection method, but I don't know how >_<)

Did you experiment with the Insert Database button, as I recommended
in my last response to you? If you aren't familiar with the
interface, so that we know we're talking about the same things in a
discussion, there's very little any one can do to help you.

By default, a connection to Excel in Word 2000 should be using DDE.
That would be another connection method. But if you don't try it out,
you certainly will not find it.

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

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

 

[Badger]

Jacob,

Have sent you a sample document and data, showing how this can be done.
See what you think.

Paul