Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / August 2004

Tip: Looking for answers? Try searching our database.

Can I merge Ecel data into a Word Doc?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Chris - 28 Aug 2004 08:19 GMT
Hello-

I have customer data in an Excel worksheet and would like to merge
that data into an invoice template so that I can print invoices for
each name in the data sheet. One catch, I don't know how.

Any assistance is much appreciated.

Thank you!
Chris
Doug Robbins - 28 Aug 2004 13:52 GMT
It's not straightforward doing this in  Word as one you probably have is
"multiple items per condition"  If you have Access, it would be better to
move the data into that and then use a report in Access which is better at
this sort of thing than Word.  One other alternative is to see the "Multiple
items per condition" item under the "Special merges" section of fellow MVP
CIndy Meister's website at

http://homepage.swissonline.ch/cindymeister/MergFram.htm

Alternatively, if you use a catalog (or in Word XP and later a directory),
type mailmerge
in which in the mailmerge main document, you have the mergefields in the
individual cells of a single row table and NOTHING else in the document so
that when you execute the merge you get a document that contains a table
containing all of the data, listed in customer order, then you run the
following macro
on that document, it will create separate tables for the each customer, one
table to each page.  Then with a bit more work, which also could be
performed by a macro as I think about it, you could get your invoices.

' Macro to create multiple items per condition in separate tables from a
directory type mailmerge

Dim source As Document, target As Document, scat As Range, tcat As Range

Dim data As Range, stab As Table, ttab As Table

Dim i As Long, j As Long, k As Long, n As Long

Set source = ActiveDocument

Set target = Documents.Add

Set stab = source.Tables(1)

k = stab.Columns.Count

Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k - 1)

Set scat = stab.Cell(1, 1).Range

scat.End = scat.End - 1

ttab.Cell(1, 1).Range = scat

j = ttab.Rows.Count

For i = 1 To stab.Rows.Count

   Set tcat = ttab.Cell(j, 1).Range

   tcat.End = tcat.End - 1

   Set scat = stab.Cell(i, 1).Range

   scat.End = scat.End - 1

   If scat <> tcat Then

       ttab.Rows.Add

       j = ttab.Rows.Count

       ttab.Cell(j, 1).Range = scat

       ttab.Cell(j, 1).Range.Paragraphs(1).PageBreakBefore = True

       ttab.Rows.Add

       ttab.Cell(j + 1, 1).Range.Paragraphs(1).PageBreakBefore = False

       For n = 2 To k

           Set data = stab.Cell(i, n).Range

           data.End = data.End - 1

           ttab.Cell(ttab.Rows.Count, n - 1).Range = data

       Next n

   Else

       ttab.Rows.Add

       For n = 2 To k

           Set data = stab.Cell(i, n).Range

           data.End = data.End - 1

           ttab.Cell(ttab.Rows.Count, n - 1).Range = data

       Next n

   End If

Next i

Signature

Please respond to the Newsgroup for the benefit of others who may be
interested.   Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

> Hello-
>
[quoted text clipped - 6 lines]
> Thank you!
> Chris

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.