Microsoft Office Forums Start MailMerge for 3 different Word documents in Excel depending on value in column A

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 02-05-2019, 03:44 AM
macropod's Avatar
macropod macropod is offline Start MailMerge for 3 different Word documents in Excel depending on value in column A Windows 7 64bit Start MailMerge for 3 different Word documents in Excel depending on value in column A Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default


To suppress the output for the 0 records, you could use a SKIPIF field, coded along the lines of:
{SKIPIF{MERGEFIELD LetterType}= "0"}

I have no idea what you'd done with the IF field in in your image. If you want me to investigate it, you'll need to attach the actual file to a post.

PS: Please post your images etc. here, not on a 3rd-party website from which they might disappear.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #17  
Old 02-05-2019, 04:40 AM
Kalü Kalü is offline Start MailMerge for 3 different Word documents in Excel depending on value in column A Windows 10 Start MailMerge for 3 different Word documents in Excel depending on value in column A Office 2010 32bit
Advanced Beginner
Start MailMerge for 3 different Word documents in Excel depending on value in column A
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

Trust me i would love to give you the file as an example but i am not allowed to... I have no clue what the IT did with this templates but it seems to be a little bit broken *lol*
However, the output were ok in my few tests.

I tried to integrate
{skipif{MERGEFIELD LetterType}= "0"} which gave me an error.

Then i tried to integrate
{skipif{if{MERGEFIELD LetterType}= "0"}} which doenst do anything, i still get output for 0-values...
Reply With Quote
  #18  
Old 02-05-2019, 05:18 AM
Kalü Kalü is offline Start MailMerge for 3 different Word documents in Excel depending on value in column A Windows 10 Start MailMerge for 3 different Word documents in Excel depending on value in column A Office 2010 32bit
Advanced Beginner
Start MailMerge for 3 different Word documents in Excel depending on value in column A
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

Nervermind, I got it by adding
Code:
 where (Anz>0)
into the vba code:
Code:
SQLStatement:="SELECT * FROM `Sheet1$` where (Anz>0)"
Can i somehow replace the large amount of text with abstracts in word that only show the full amount of text when the letters are merged?
Reply With Quote
  #19  
Old 02-05-2019, 06:05 AM
Kalü Kalü is offline Start MailMerge for 3 different Word documents in Excel depending on value in column A Windows 10 Start MailMerge for 3 different Word documents in Excel depending on value in column A Office 2010 32bit
Advanced Beginner
Start MailMerge for 3 different Word documents in Excel depending on value in column A
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

....and now the master question which probably makes all my work nothing...

Is there a way to write the excel code so that it doesnt require a reference to the Word object model to be set, via Tools|References in the VBE. Because this is too difficult for my collegues here

here my final code
Code:
Option Explicit
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim StrMMSrc As String, StrMMDoc As String, StrMMPath As String, StrName As String
Dim i As Long, j As Long
Const StrNoChr As String = """*./\:?|"
Dim wdApp As New Word.Application, wdDoc As Word.Document
wdApp.Visible = False
wdApp.DisplayAlerts = wdAlertsNone
StrMMSrc = ThisWorkbook.FullName
StrMMPath = ThisWorkbook.Path & "\"
StrMMDoc = StrMMPath & "abc.docx"
Set wdDoc = wdApp.Documents.Open(Filename:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)
With wdDoc
  With .MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
      LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
      "Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
      SQLStatement:="SELECT * FROM `Sheet1$` where (Anz>0)"
    For i = 1 To .DataSource.RecordCount
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("ID")) = "" Then Exit For
        StrName = .DataFields("ID")
      End With
      .Execute Pause:=False
      For j = 1 To Len(StrNoChr)
        StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
      StrName = Trim(StrName)
      With wdApp.ActiveDocument
        .SaveAs Filename:=StrMMPath & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
    Next i
    .MainDocumentType = wdNotAMergeDocument
  End With
  .Close SaveChanges:=False
End With
wdApp.DisplayAlerts = wdAlertsAll
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing
Application.ScreenUpdating = False
End Sub
Reply With Quote
  #20  
Old 02-05-2019, 02:18 PM
macropod's Avatar
macropod macropod is offline Start MailMerge for 3 different Word documents in Excel depending on value in column A Windows 7 64bit Start MailMerge for 3 different Word documents in Excel depending on value in column A Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Once you've done the Word referencing in the Excel workbook, there is no need for anyone else to do so - the referencing travels with the workbook.

Here's an approach for handling your three letters that allows you to keep each in its own document and still have just a single document for your mailmerge:
1. Save each of your existing mailmerge main documents as ordinary Word documents, naming them sb1.docx, sb2.docx, & sb3.docx, respectively (as you originally indicated them to be).
2. Create a new mailmerge main document.
3. Use just the following field code in your new mailmerge main document:
{SKIPIF{MERGEFIELD Anz}= "0"}{QUOTE{INCLUDETEXT "{FILENAME \p}\..\sb{MERGEFIELD Anz}.docx"}}
4. Save your new mailmerge main document in the same folder as your three letters.
5. Reference only your new mailmerge main document in the VBA code.

Note: with the above there should be no need for your 'where (Anz>0)' in the SQL statement.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #21  
Old 02-05-2019, 11:32 PM
Kalü Kalü is offline Start MailMerge for 3 different Word documents in Excel depending on value in column A Windows 10 Start MailMerge for 3 different Word documents in Excel depending on value in column A Office 2010 32bit
Advanced Beginner
Start MailMerge for 3 different Word documents in Excel depending on value in column A
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

Thank you very much.
If other users dont need to bind the library, why do they get that error-message: "Can't find project or library" while it is working perfectly on my pc
Reply With Quote
  #22  
Old 02-06-2019, 12:10 AM
macropod's Avatar
macropod macropod is offline Start MailMerge for 3 different Word documents in Excel depending on value in column A Windows 7 64bit Start MailMerge for 3 different Word documents in Excel depending on value in column A Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Unless the others are using an earlier version of Office than you're using, they shouldn't be getting the error. If they do use an earlier version of Office than you use, simply set the reference on a PC using the earliest version of Office you need to support, then distribute that workbook. Otherwise, a significant re-write of the code would be required.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #23  
Old 02-06-2019, 12:29 AM
Kalü Kalü is offline Start MailMerge for 3 different Word documents in Excel depending on value in column A Windows 10 Start MailMerge for 3 different Word documents in Excel depending on value in column A Office 2010 32bit
Advanced Beginner
Start MailMerge for 3 different Word documents in Excel depending on value in column A
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

We have all the same version ofOffice 2010 and Windows 10
Reply With Quote
  #24  
Old 02-06-2019, 12:32 AM
macropod's Avatar
macropod macropod is offline Start MailMerge for 3 different Word documents in Excel depending on value in column A Windows 7 64bit Start MailMerge for 3 different Word documents in Excel depending on value in column A Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

That suggests you developed the code & set the reference in something other than the workbook you're distributing (e.g. in your Personal workbook).
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #25  
Old 02-06-2019, 12:36 AM
Kalü Kalü is offline Start MailMerge for 3 different Word documents in Excel depending on value in column A Windows 10 Start MailMerge for 3 different Word documents in Excel depending on value in column A Office 2010 32bit
Advanced Beginner
Start MailMerge for 3 different Word documents in Excel depending on value in column A
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

Yes it could be that i set it in word how can I change that?
Reply With Quote
  #26  
Old 02-06-2019, 12:39 AM
macropod's Avatar
macropod macropod is offline Start MailMerge for 3 different Word documents in Excel depending on value in column A Windows 7 64bit Start MailMerge for 3 different Word documents in Excel depending on value in column A Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

It's an Excel macro, so you add your code to the workbook you're distributing and set the reference there.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #27  
Old 02-06-2019, 12:43 AM
Kalü Kalü is offline Start MailMerge for 3 different Word documents in Excel depending on value in column A Windows 10 Start MailMerge for 3 different Word documents in Excel depending on value in column A Office 2010 32bit
Advanced Beginner
Start MailMerge for 3 different Word documents in Excel depending on value in column A
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

if i open the workbook on my pc it is already set....
Reply With Quote
  #28  
Old 02-06-2019, 12:49 AM
macropod's Avatar
macropod macropod is offline Start MailMerge for 3 different Word documents in Excel depending on value in column A Windows 7 64bit Start MailMerge for 3 different Word documents in Excel depending on value in column A Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

But is it set if you open it on another PC? If not, you probably had your Personal workbook's VBA project active when you set it instead of having your mailmerge workbook's VBA project active.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #29  
Old 02-06-2019, 01:01 AM
Kalü Kalü is offline Start MailMerge for 3 different Word documents in Excel depending on value in column A Windows 10 Start MailMerge for 3 different Word documents in Excel depending on value in column A Office 2010 32bit
Advanced Beginner
Start MailMerge for 3 different Word documents in Excel depending on value in column A
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

Gonna test it asap
Reply With Quote
  #30  
Old 02-06-2019, 01:48 AM
Kalü Kalü is offline Start MailMerge for 3 different Word documents in Excel depending on value in column A Windows 10 Start MailMerge for 3 different Word documents in Excel depending on value in column A Office 2010 32bit
Advanced Beginner
Start MailMerge for 3 different Word documents in Excel depending on value in column A
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

it is not set on other's pc
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Start MailMerge for 3 different Word documents in Excel depending on value in column A Mailmerge Excel with a Master Word Template (via VBA) stevenel Excel Programming 8 08-14-2018 03:05 PM
Start MailMerge for 3 different Word documents in Excel depending on value in column A Rounding Up or Down depending on Start & End time richardst Excel 4 06-21-2016 06:03 PM
Marking specific cells depending on start and end time entered ellebb85 Excel Programming 0 01-17-2016 02:50 AM
Start MailMerge for 3 different Word documents in Excel depending on value in column A documents saved with double column revert to single column when re-opened danw Word 6 04-08-2015 06:24 AM
Merge Word documents using a mailmerge field Concertina Mail Merge 3 02-18-2013 04:12 AM


All times are GMT -7. The time now is 10:23 PM.


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