Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers,microsoft.public.word.newusers
|
|||
|
|||
Inserting data into Word document by a range of dates from Excel spreadsheet
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) |
#2
Posted to microsoft.public.excel.newusers,microsoft.public.word.newusers
|
|||
|
|||
Inserting data into Word document by a range of dates from Excel spreadsheet
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/show...?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. -- Cheers macropod [MVP - Microsoft Word] "Gordon" wrote in message ... 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) |
#3
Posted to microsoft.public.excel.newusers,microsoft.public.word.newusers
|
|||
|
|||
Inserting data into Word document by a range of dates from Excel spreadsheet
Oops - Change the SKIPIF field to:
{SKIPIF {=OR({MERGEFIELD DeceaseDate \@ "MMdd"}{StartDate \@ "MMdd"},{MERGEFIELD DeceaseDate \@ "MMdd"}{EndDate \@ "MMdd"})}=1} -- Cheers macropod [MVP - Microsoft Word] "Gordon" wrote in message ... 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) |
#4
Posted to microsoft.public.excel.newusers,microsoft.public.word.newusers
|
|||
|
|||
Inserting data into Word document by a range of dates from Excel spreadsheet
"macropod" wrote in message
... 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.... |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Embedded Excel spreadsheet in Word Document - cannot update data | Tables | |||
Inserting Excel spreadsheet into Word | Microsoft Word Help | |||
Inserting Excel spreadsheet into Word - why does it get corrupt? | Microsoft Word Help | |||
Merge data in Excel Spreadsheet into a word document | Mailmerge | |||
Inserting a multiple page excel spreadsheet into Word | Microsoft Word Help |