Inserting data into Word document by a range of dates from Excel spreadsheet

[Gordon]

I use Word (2007) to create a weekly "Notes" sheet for my group of Churches.
The names of the deceased, which we remember week by week on the anniversary
of their deaths, is held (at the present time) on an Excel 2007 spreadsheet.
Is there any way (by Macro for example) I can automatically insert the names
from the sheet into a table in Word by date range?
Or can someone think of a better way to do this? (I don't have Access)

 

[macropod]

Hi Gordon,

You can't merge to a variable-row table, per se, but you can do a merge that outputs multiple lines in a tabular format. Word calls
this a catalog merge.

For your puposes, you need to first establish the start & end dates for the merge. You can do that with fields coded as:

{QUOTE
{SET a{=INT((14-{DATE \@ M})/12)}}
{SET b{={DATE \@ yyyy}+4800-a}}
{SET c{={DATE \@ M}+12*a-3}}
{SET d{DATE \@ d}}
{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}}
{SET Weekday 6}
{SET jd{=INT((jd)/7)*7+Weekday}}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET h{=f-INT(1461*g/4)}}
{SET i{=INT((5*h+2)/153)}}
{SET dd{=h-INT((153*i+2)/5)+1}}
{SET mm{=i+3-12*INT(i/10)}}
{SET yy{=100*e+g-4800+INT(i/10)}}
{SET EndDate "{dd}-{mm}-{yy}"}}

{QUOTE
{SET Delay -6}
{SET jd{=jd+Delay}}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET h{=f-INT(1461*g/4)}}
{SET i{=INT((5*h+2)/153)}}
{SET dd{=h-INT((153*i+2)/5)+1}}
{SET mm{=i+3-12*INT(i/10)}}
{SET yy{=100*e+g-4800+INT(i/10)}}
{SET StartDate "{dd}-{mm}-{yy}"}}

These fields are derived from examples in my Date Calc 'tutorial', at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
or
http://www.gmayor.com/downloads.htm#Third_party
In particular, look at the items titled 'Calculate a Stepped Date' and 'Calculate a Date Sequence' (2nd entry). Do read the
document's introductory material. You can simply copy these fields into your document and make the minor changes outlined below.

In each case, you'll need to modify the last line of the field coding, as shown above. You can do this by deleting the '\@ "dddd, d
MMMM yyyy"' string, then selecting the '"{dd}-{mm}-{yy}"' string and pressing Ctrl-F9 to create a new pair of field braces (ie
'{ }') into which you can type the 'SET EndDate ' or 'SET StartDate ', as applicable. For the 2nd field, you'll also need to change
the Delay value to '-6'.

You then need to create a SKIPIF field, coded as:
{SKIPIF {=OR({MERGEFIELD DeceaseDate \@ "yyyyMMdd"}<{StartDate \@ "yyyyMMdd"},{MERGEFIELD DeceaseDate \@ "yyyyMMdd"}>{EndDate \@
"yyyyMMdd"})}=1}
where the field braces (ie '{ }') are created in pairs via Ctrl-F9 and 'DeceaseDate' is the name of the field containing the date of
decease.

Now that you've done this, all that remains to do is to insert the mergefields for retrieving the deceaseds' names and dates of
decease, which you can do via the mailmerge wizard. The date field will need whatever date-formatting switch you prefer - again,
refer to the tutorial.

The result should be a merged document containing all the decease details from last Monday to this Sunday, inclusive.

 

[macropod]

Oops - Change the SKIPIF field to:
{SKIPIF {=OR({MERGEFIELD DeceaseDate \@ "MMdd"}<{StartDate \@ "MMdd"},{MERGEFIELD DeceaseDate \@ "MMdd"}>{EndDate \@ "MMdd"})}=1}

 

[Gordon]

Oops - Change the SKIPIF field to:
{SKIPIF {=OR({MERGEFIELD DeceaseDate \@ "MMdd"}<{StartDate \@
"MMdd"},{MERGEFIELD DeceaseDate \@ "MMdd"}>{EndDate \@ "MMdd"})}=1}

Wow! Thanks! The amount of work to get that working is probably in inverse
proportion to the numbers of people we have to remember! I think I'll leave
it for the moment if you don't mind....