![]() |
#1
|
|||
|
|||
![]()
File attached (RPT) with macro - CTRL+Q to run.
The macro creates bookmarks which I am hoping to use in a MM layout (flash). Table data is fine but placing bookmarked data it to mail merge is proving impossible (INCLUDETEXT does not appear to work?) The two book marks I wish to appear as line1 and line 2 on each label RADDATE CLIENT REF Is my only solution to drop all MM data to a "temporary" excel fill which can then be used by the MM layout? I would truly appreciate any help Last edited by Nisio07; 10-14-2014 at 12:17 AM. Reason: file upload |
#2
|
||||
|
||||
![]()
I assume this is a continuation of the project discussed here: https://www.msofficeforums.com/mail-...-header-2.html
Your post refers to macros, but there don't appear to be any in either document (even though the RPT.doc gives a macro warning). Likewise, your post refers to INCLUDETEXT fields, but there are none in either document. And, yes, INCLUDETEXT fields do work - even for mailmerges - as previously discussed. Furthermore, the Mailmerge SQL prompt refers to what is, ostensibly, yet another document that you're presumably using as a datasource. As it stands, I have no idea what the problem is. What is the origin of all the data? An excel workbook, perhaps?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
![]() Quote:
the two attached amended and corrected documents should now give a better idea of what I am hoping to achieve. That macro bm001 assigns bookmarks to a number of fields in the header and footer. I am looking to use includetext to reference this data in each label of the mailmerge. the attached should work better for you now. You should see I have included two different forms of INCLUDETEXT but neither works - Both did initially but not now and I changed nothing in the meantime. Is there a solution within word or could I/should I write the data to excel and then it would be very simple MM Apologies again for any wasted time. Regards Mary Ps RPT.doc is the data source and flash is the (incomplete) mail merge template |
#4
|
||||
|
||||
![]()
Surely using your macro runs counter to what you said in the other thread about "would thus like to avoid Macros"??? If macros aren't a problem, why not generate the whole thing with a macro from the RPT.doc and forget about using mailmerge? For example:
Code:
Sub Demo() Application.ScreenUpdating = False Dim DocSrc As Document, DocTgt As Document, TblSrc As Table, TblTgt As Table, Rng As Range Dim StrClnt As String, StrProj As String, StrRdDt As String, StrLocn As String, StrCRef As String Dim StrISO As String, StrDia As String, StrWeld As String, StrID As String Dim StrMtrl As String, StrActNo As String, StrReqNo, Result As String, i As Long, j As Long Set DocSrc = ActiveDocument Set DocTgt = Documents.Add With DocSrc Set TblSrc = .Range.Tables(1) j = -Int(-(TblSrc.Rows.Count - 1) / 3 * 2) 'Get the ClientName, ProjectName, RadDate, Location, ClientRef With .Sections(1).Headers(wdHeaderFooterPrimary).Range.Tables(1) Set Rng = .Cell(1, 1).Range With Rng .End = .End - 1 StrClnt = .Text End With Set Rng = .Cell(1, 4).Range With Rng .End = .End - 1 StrProj = .Text End With Set Rng = .Cell(2, 2).Range With Rng .End = .End - 1 StrRdDt = .Text End With Set Rng = .Cell(3, 2).Range With Rng .End = .End - 1 StrLocn = .Text End With Set Rng = .Cell(4, 4).Range With Rng .End = .End - 1 StrCRef = .Text End With End With End With With DocTgt 'Do the page setup With .PageSetup .PaperSize = wdPaperA4 .LeftMargin = CentimetersToPoints(0.72) .RightMargin = CentimetersToPoints(0.72) .BottomMargin = CentimetersToPoints(0) .TopMargin = CentimetersToPoints(0.45) End With 'Create the output table Set TblTgt = .Tables.Add(Range:=.Range, NumRows:=j, NumColumns:=3, _ DefaultTableBehavior:=wdWord8TableBehavior, AutoFitBehavior:=wdAutoFitWindow) With TblTgt 'Format the output table .Rows.HeightRule = wdRowHeightExactly .Rows.Height = CentimetersToPoints(2.38) .Columns.Width = CentimetersToPoints(6.57) .Borders.Enable = False With .Range With .ParagraphFormat .TabStops.Add Position:=CentimetersToPoints(4.75), Alignment:=wdAlignTabLeft .SpaceBefore = 0 .SpaceAfter = 0 End With With .Font .Size = 9 .Name = "Calibri" End With End With With .Range For i = 2 To TblSrc.Rows.Count With TblSrc 'Get the ISO Num, Diameter, Weld No, Wr I.D, Material, Actual Wire No, Required Wire No, Result Set Rng = .Cell(i, 1).Range With Rng .End = .End - 1 StrISO = .Text End With Set Rng = .Cell(i, 2).Range With Rng .End = .End - 1 StrDia = .Text End With Set Rng = .Cell(i, 3).Range With Rng .End = .End - 1 StrWeld = .Text End With Set Rng = .Cell(i, 4).Range With Rng .End = .End - 1 StrID = .Text End With Set Rng = .Cell(i, 5).Range With Rng .End = .End - 1 StrMtrl = .Text End With Set Rng = .Cell(i, 6).Range With Rng .End = .End - 1 StrActNo = .Text End With Set Rng = .Cell(i, 7).Range With Rng .End = .End - 1 StrReqNo = .Text End With Set Rng = .Cell(i, 8).Range With Rng .End = .End - 1 Result = .Text End With End With Set Rng = .Cells((i - 1) * 2 - 1).Range With Rng .End = .End - 1 .Text = StrRdDt & Chr(11) & StrCRef & Chr(11) & _ "ISO NUM: " & StrISO & vbTab & "DIA: " & StrDia & Chr(11) & _ "WELD NO: " & StrWeld & vbTab & "WR ID: " & StrID End With Next End With End With With .Range.Find .ClearFormatting With .Replacement .ClearFormatting .Font.Bold = True .Font.Underline = wdUnderlineSingle .Text = "^&" End With .Forward = True .Format = True .MatchCase = True .Wrap = wdFindContinue .MatchWildcards = False .MatchAllWordForms = False .Text = "ISO NUM:" .Execute Replace:=wdReplaceAll .Text = "DIA:" .Execute Replace:=wdReplaceAll .Text = "WELD NO:" .Execute Replace:=wdReplaceAll .Text = "WR ID:" .Execute Replace:=wdReplaceAll End With End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
![]()
Word fail me ... but I must say ... You genius you.....
Did try to PM you... ![]() 1. If I need minor changes to this which I cannot complete can I ask your help again .. Should I post here or a new one.. 2. Also I do need a second label report from this .. nothing as complex... Again, new post or here Finally, see above ... HUGE THANKS .. YOU GENIUS YOU... Regards nisio |
#6
|
||||
|
||||
![]() Quote:
Quote:
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
![]()
Paul
Some minor changes I have managed to make myself but our technician has come back and asked me to amend label print so that it prints down column by column. It currently prints across - label 1,3,5,7,9,11,13,15,17,... but now wants label 1,4,7,10,13, etc.. I trust this makes sense... Current prototype report attached. While I appreciate this is occupying every waking moment of my life the same may not be the case for you. Please email with any further queries.. Regards Mary |
#8
|
||||
|
||||
![]()
OK, make the following changes to the code.
Replace: j = -Int(-(TblSrc.Rows.Count - 1) / 3 * 2) With: j = (TblSrc.Rows.Count - 2) * 3 + 1 Replace: .LeftMargin = CentimetersToPoints(0.72) .RightMargin = CentimetersToPoints(0.72) With: .LeftMargin = CentimetersToPoints(0.65) .RightMargin = CentimetersToPoints(0.65) After: .TopMargin = CentimetersToPoints(0.45) Insert: Code:
With .TextColumns .SetCount NumColumns:=3 .EvenlySpaced = True .Spacing = 0 .Width = CentimetersToPoints(6.57) End With Set TblTgt = .Tables.Add(Range:=.Range, NumRows:=j, NumColumns:=3, _ With: Set TblTgt = .Tables.Add(Range:=.Range, NumRows:=j, NumColumns:=1, _ After: .Borders.Enable = False Insert: .Rows.Alignment = wdAlignRowCenter Replace: Set Rng = .Cells((i - 1) * 2 - 1).Range With: Set Rng = .Cells((i - 2) * 3 + 1).Range What the above changes do is to give the document itself a 3-column page layout, in which a single-column table is used. The table then uses the column-wrapping to manage the layout.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
![]() |
Tags |
bookmark, macro |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
bgranzow | Mail Merge | 9 | 06-05-2015 05:03 AM |
Is data merge and mail merge the same thing? | ikearns | Mail Merge | 1 | 09-12-2014 03:53 AM |
Mail merge how to link mail merge field value to a column heading | dsummers | Mail Merge | 1 | 05-08-2014 02:59 PM |
Mail Merge Duplication of address on merge | RICKY | Mail Merge | 1 | 09-26-2012 03:14 PM |
![]() |
Aude | Mail Merge | 1 | 01-06-2012 07:38 PM |