![]() |
|
#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... to say thanks .. Two favours ..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 Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
Mail Merge is Deleting objects in my header and footer during the merge
|
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 |
Conditional merge fields in mail merge
|
Aude | Mail Merge | 1 | 01-06-2012 07:38 PM |