Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-14-2014, 12:14 AM
Nisio07 Nisio07 is offline Mail merge bookmark (again) Windows 7 64bit Mail merge bookmark (again) Office 2007
Novice
Mail merge bookmark (again)
 
Join Date: Sep 2014
Posts: 21
Nisio07 is on a distinguished road
Default Mail merge bookmark (again)

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
Attached Files
File Type: doc RPT.doc (75.5 KB, 12 views)
File Type: docx flash.docx (18.4 KB, 10 views)

Last edited by Nisio07; 10-14-2014 at 12:17 AM. Reason: file upload
Reply With Quote
  #2  
Old 10-14-2014, 02:46 AM
macropod's Avatar
macropod macropod is offline Mail merge bookmark (again) Windows 7 64bit Mail merge bookmark (again) Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,369
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 10-14-2014, 12:45 PM
Nisio07 Nisio07 is offline Mail merge bookmark (again) Windows 7 64bit Mail merge bookmark (again) Office 2007
Novice
Mail merge bookmark (again)
 
Join Date: Sep 2014
Posts: 21
Nisio07 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
I assume this is a continuation of the project discussed here: https://www.msofficeforums.com/mail-...-header-2.html CORRECT

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). Apologies... I have deleted and renamed some sensitive info and files. This should now be resolved. 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?

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
Attached Files
File Type: doc RPT.doc (81.0 KB, 10 views)
File Type: docx flash.docx (19.0 KB, 9 views)
Reply With Quote
  #4  
Old 10-15-2014, 12:07 AM
macropod's Avatar
macropod macropod is offline Mail merge bookmark (again) Windows 7 64bit Mail merge bookmark (again) Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,369
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
Note: I don't know which variables you're actually after, so the code captures all you seem to have indicated so far, even though it only outputs the ones shown in your latest 'flash' document.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 10-15-2014, 11:11 PM
Nisio07 Nisio07 is offline Mail merge bookmark (again) Windows 7 64bit Mail merge bookmark (again) Office 2007
Novice
Mail merge bookmark (again)
 
Join Date: Sep 2014
Posts: 21
Nisio07 is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 10-15-2014, 11:41 PM
macropod's Avatar
macropod macropod is offline Mail merge bookmark (again) Windows 7 64bit Mail merge bookmark (again) Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,369
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Nisio07 View Post
Word fail me ... but I must say ... You genius you.....

Did try to PM you... to say thanks ..

Finally, see above ... HUGE THANKS .. YOU GENIUS YOU...
You're welcome.
Quote:
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..
By all means. FWIW, the code I posted is possibly more 'brute-force' than necessary. It appears you might be using label stationery, in which case, it should be possible to tell Word to create a new document based on that rather than starting from scratch and going through all the page & table setup hoops.
Quote:
2. Also I do need a second label report from this .. nothing as complex... Again, new post or here
May as well continue in this thread. If both are always produced at the same time, or the use gets to choose between them, it might only entail adding some more code to what we already have.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 10-21-2014, 02:21 PM
Nisio07 Nisio07 is offline Mail merge bookmark (again) Windows 7 64bit Mail merge bookmark (again) Office 2007
Novice
Mail merge bookmark (again)
 
Join Date: Sep 2014
Posts: 21
Nisio07 is on a distinguished road
Default

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
Attached Files
File Type: doc RPT-Ver2.doc (104.5 KB, 10 views)
Reply With Quote
  #8  
Old 10-21-2014, 03:34 PM
macropod's Avatar
macropod macropod is offline Mail merge bookmark (again) Windows 7 64bit Mail merge bookmark (again) Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,369
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
Replace:
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]
Reply With Quote
Reply

Tags
bookmark, macro



Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail merge bookmark (again) 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
Mail merge bookmark (again) Conditional merge fields in mail merge Aude Mail Merge 1 01-06-2012 07:38 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:08 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft