Macros, Mail Merge and sorting data into 2 different tables

[HeatherO]

I think I might be in way over my head, so I am just looking for any help or
suggestions to help me sort this out. Anyways I have a word document which I
want to put data into from an excel spreadsheet.
Here's basically how the document is there is a prompt for the end user to
input the date and then there are some basic places to put data (which I know
I can do with a Mail merge). However there is stuff like if the person
doesn't have this in the field or it's a blank don't print it or the header
i.e. points card: {MergeField pntscrd}.
To make matters worse there are 2 tables as well and in the tables I am to
put information in that might have one or more lines depending on data in
excel file. The data is by account and therefore might have 2 rows with same
account but the document is by account. Which I don't think I can do with
the mail merge so I will somehow need to create I believe a sub routine to
put the data into the table based on the account # from the mail merge.
The second table is taking the values from the first and doing a vlookup to
another excel spreadsheet and putting the data from the 2nd spreadsheet into
it (basically details). I've tried using if statements in mail merge and for
some reason they don't work. I don't know if it 's because I am saying if
{mergefield Col.P} > 0 then {MergeField Col. Q} this is of course with a
mail merge.
TIA
Heather
p.s. I am also finding it hard to record macros with the mail merge utility
I can't right click on the field and edit it that way while running a record
macro which is annoying.

 

[Doug Robbins]

Yes, I think that you are getting in a bit over your head - especially as
you are trying to produce a "multiple items per condition" type report.

Word does not really have the ability to perform a "multiple items per
condition (=key field)" mailmerge.

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

Or, if you create a Catalog (on in Word XP and later, it's called Directory)
type mailmerge main document with the mergefields in the cells of a one row
table in the mailmerge main document with the keyfield in the first cell in
the row and then execute that merge to a new document and then run the
following macro, it will create separate tables with the records for each
key field in them. With a bit of further development, you may be able to
get it to do what you want.

' 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

Now, apart from having scared the bejesus out of you, this is not the sort
of thing for which you can make much use of the macro recorder. You are
going to need to get fairly well acquainted with the Word object model and
the use of Visual Basic.

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

 

[HeatherO]

Thanks Doug,
The scope of the project has changed yet again. So what I am just going
to try to do is take it in steps. I am not doing a mail merge anymore, what
I am going to have to do is read through the excel file since some clients
might have an english document and others might have a french document
therefore 2 different documents. So I decided to just take all the excel
spreadsheets and whatever information I am pulling from other spreadsheets I
will take and dump into the next columns on the one original spreadsheet.
Then I am going to go through the spreadsheet one row at a time check if the
account is the same and if so just populate the old document's table with the
new stuff, and if not then start a new document and use bookmark's and table
references' like 1,0 to insert the data into the word document. The only
thing I am unsure of is if I want to link all the documents together so they
all can be printed and stored under one file name. I know it does this in
the mail merge but I was just curious if there is a save as and append to the
original document kind of thing for word? Thanks for all your help, I just
really had to sit back and analyze all this stuff, plus I think I will have
to go back to the end user and say what he asks for is impossible. They want
me to then go back and sort through the documents and store them in Last Name
and then account order, I mean it's an excel spreadsheet not a database.
Sorry just venting.
Thanks again