Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Creating an Invoice with Mail Merge
I spent yesterday afternoon and evening trying to find out how to generate
an an invoice using a Word Mail Merge, but was unable to find a suitable solution. From searching Google newsgroups this question appears to get asked fairly regularly but there appears to be no good answer. As a final check on if this can be done therefore, here is my problem! My version of Word is 2003 11.6359.6360 SP1. I am running Windows XP SP 2. The data I have is in a tab delimited text file, with the data as follows: Client Name Quote Date Quote Number Client Address 1 Client Address 2 Client Address 3 Quote Line Description Quote Line Price Quote Line Tax Quote Line Total The header details are repeated for each line of the quote. So for example if the quote has five lines, there will be five lines in the text file, but the header details will be the same for each line. I would like a Word template where I can run a "merge" to generate a quote. The Quote should be formatted as follows: The start of the quote should contain the quote header information: the client name and address, quote date and number etc. Then there should be a list of quote lines - these need to appear in a table and appear one after the other, not one line per page. ## Note: I have used a "Directory" Word merge to get each line of the quote to appear as a new line in the merged table. The front of the quote should say now many pages there are in the quote. ## Note: It doesn't matter if I use the NUMPAGES field or the SECTIONPAGES field it always appears as 1 on the first page (however page headers *after* page 1 get the total correct in the header). In the page header on each page after the first page should be the page number in the format "Quote (quote number) page X of Y". ## Note: I have worked out how to set up the template to have different headers and footers for the first page and subsequent pages. At the bottom of each page apart from the last page should be sub-totals, showing the total so far. ## Note: Putting a sum(above) in a table in the footer didn't work - it gets a "Table Index cannot be zero" error. At the end of the table should be totals for each column (total pre-tax price, total tax price, total post-tax price). Finally *at the end of the document* should appear together various terms and conditions. This in effect should be the footer for the last page. ## Note: Putting an IF PAGE = NUMPAGES or IF PAGE=SECTIONPAGES section after the table displays the footer for each row merged. Although I am using Word 2003 ideally this solution should be as generic as possible - at least back to Word 2000. Also note that the "Database" field solution is not suitable as it does not allow the table to be formatted, it requires a pre-formatted table to be used. |
#2
|
|||
|
|||
I would use Access for this.
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/cindy...r/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 -- 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 "Robert Jones" wrote in message ... I spent yesterday afternoon and evening trying to find out how to generate an an invoice using a Word Mail Merge, but was unable to find a suitable solution. From searching Google newsgroups this question appears to get asked fairly regularly but there appears to be no good answer. As a final check on if this can be done therefore, here is my problem! My version of Word is 2003 11.6359.6360 SP1. I am running Windows XP SP 2. The data I have is in a tab delimited text file, with the data as follows: Client Name Quote Date Quote Number Client Address 1 Client Address 2 Client Address 3 Quote Line Description Quote Line Price Quote Line Tax Quote Line Total The header details are repeated for each line of the quote. So for example if the quote has five lines, there will be five lines in the text file, but the header details will be the same for each line. I would like a Word template where I can run a "merge" to generate a quote. The Quote should be formatted as follows: The start of the quote should contain the quote header information: the client name and address, quote date and number etc. Then there should be a list of quote lines - these need to appear in a table and appear one after the other, not one line per page. ## Note: I have used a "Directory" Word merge to get each line of the quote to appear as a new line in the merged table. The front of the quote should say now many pages there are in the quote. ## Note: It doesn't matter if I use the NUMPAGES field or the SECTIONPAGES field it always appears as 1 on the first page (however page headers *after* page 1 get the total correct in the header). In the page header on each page after the first page should be the page number in the format "Quote (quote number) page X of Y". ## Note: I have worked out how to set up the template to have different headers and footers for the first page and subsequent pages. At the bottom of each page apart from the last page should be sub-totals, showing the total so far. ## Note: Putting a sum(above) in a table in the footer didn't work - it gets a "Table Index cannot be zero" error. At the end of the table should be totals for each column (total pre-tax price, total tax price, total post-tax price). Finally *at the end of the document* should appear together various terms and conditions. This in effect should be the footer for the last page. ## Note: Putting an IF PAGE = NUMPAGES or IF PAGE=SECTIONPAGES section after the table displays the footer for each row merged. Although I am using Word 2003 ideally this solution should be as generic as possible - at least back to Word 2000. Also note that the "Database" field solution is not suitable as it does not allow the table to be formatted, it requires a pre-formatted table to be used. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mail merge with attachments | Mailmerge | |||
Mail Merge is creating blank pages between form letters | Mailmerge | |||
Using Mail Merge for Creating a Document of Biographies... | Microsoft Word Help | |||
Using Mail Merge for Creating a Document of Biographies... | Mailmerge | |||
Using Hyperlinks in Mail Merge IF...THEN...ELSE Statements | Mailmerge |