Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access table to Mail Merge
I have a table with this information:
StudentID | LastName | FirstName | Grade | Subjects 103289 | Doe | Jane | 7 | MathChr(9)43Chr(9)1,2,5Chr(13)Chr(10)ScienceChr(9) 61Chr(9)3,4 64897 | Does | John | 6 | ArtChr(9)43Chr(9)1,2,5Chr(13)Chr(10)HistoryChr(9)6 1Chr(9)3EnglishChr(9)64Chr(9)4,8 54678 | Doer | Jill | 8 | EnglishChr(9)38Chr(9)1 I need to know two things. 1 - Each row should be on it's own page 2 - The Subjects column is tab delimited, and if there are multiple records there are return characters at the end of the preceding lines. I could make it whatever for the delimiters though. I need to be able to place the Subjects column in a table on the report. How can this be done? Thanks in advance, Jamie |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access table to Mail Merge
Assuming I have understood correctly and
a. your Subjects column is actually a memo field in Access and b. what you want is something like StudentID: 103289 LastName: Doe FirstName: Jane Grade: 7 ------------------------- | Subject | % | XXX | ------------------------- | Math | 43 | 1,2,5 | ------------------------- | Science | 61 | 3,4 | ------------------------- next page StudentID: 64897 etc. where | and - represent real table cell borders, then I think your best bet is probably to do the merge to a new document, inserting your Subject field content as is, but surrounded by recognisable markers and any trim text that needs to go in the table, e.g. so you have something like +++ Subjecttab%tabXXXcrlf Mathtab43tab1,2,5crlf Sciencetab61tab3,4 --- then use a VBA macro to select each chunk of text between +++ and --- and use ConvertTextToTable to delete the +++ and ---, convert the text to a table, and format it how you want. e.g. Sub Macro1() Dim rngSearch As Word.Range Dim tblCurrent As Word.Table Set rngSearch = ActiveDocument.Content With rngSearch With .Find .ClearFormatting .Replacement.ClearFormatting .Text = "(+++)(*)(---)" .Replacement.Text = "\2" .Forward = True .Wrap = wdFindStop .Format = False .MatchCase = False .MatchWholeWord = False .MatchKashida = False .MatchDiacritics = False .MatchAlefHamza = False .MatchControl = False .MatchByte = False .MatchAllWordForms = False .MatchSoundsLike = False .MatchWildcards = True End With .Find.Execute Replace:=True While .Find.Found Set tblCurrent = .ConvertToTable(Separator:=vbTab) With tblCurrent ' do any table formatting in here, e.g. .Borders.Enable = True .Columns(1).Width = 100 End With Set tblCurrent = Nothing ' then find the next chunk rngSearch.SetRange _ Start:=rngSearch.End, _ End:=ActiveDocument.Content.End .Find.Execute Replace:=True Wend End With End Sub See, e.g. Graham Mayor's article at http://www.gmayor.com/installing_macro.htm for hints on how to install macros. There are other ways to do this, but if that looks as if it does the basics, I suspect it's the simplest approach in this case. Peter Jamieson http://tips.pjmsn.me.uk Jamie Steenberge wrote: I have a table with this information: StudentID | LastName | FirstName | Grade | Subjects 103289 | Doe | Jane | 7 | MathChr(9)43Chr(9)1,2,5Chr(13)Chr(10)ScienceChr(9) 61Chr(9)3,4 64897 | Does | John | 6 | ArtChr(9)43Chr(9)1,2,5Chr(13)Chr(10)HistoryChr(9)6 1Chr(9)3EnglishChr(9)64Chr(9)4,8 54678 | Doer | Jill | 8 | EnglishChr(9)38Chr(9)1 I need to know two things. 1 - Each row should be on it's own page 2 - The Subjects column is tab delimited, and if there are multiple records there are return characters at the end of the preceding lines. I could make it whatever for the delimiters though. I need to be able to place the Subjects column in a table on the report. How can this be done? Thanks in advance, Jamie |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail Merge does not include all records from list or Access table | Mailmerge | |||
Mail Merge does not include all records from list or Access table | Mailmerge | |||
Mail Merge from an Access Database table | Mailmerge | |||
Can Word 2003 mail merge an Access value from a look-up table? | Mailmerge | |||
Access to Word Table Mail Merge | Mailmerge |