|
|
Thread Tools | Display Modes |
#16
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#17
|
|||
|
|||
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... |
#18
|
|||
|
|||
Nervermind, I got it by adding
Code:
where (Anz>0) Code:
SQLStatement:="SELECT * FROM `Sheet1$` where (Anz>0)" |
#19
|
|||
|
|||
....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 |
#20
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#21
|
|||
|
|||
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 |
#22
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#23
|
|||
|
|||
We have all the same version ofOffice 2010 and Windows 10
|
#24
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#25
|
|||
|
|||
Yes it could be that i set it in word how can I change that?
|
#26
|
||||
|
||||
It's an Excel macro, so you add your code to the workbook you're distributing and set the reference there.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#27
|
|||
|
|||
if i open the workbook on my pc it is already set....
|
#28
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#29
|
|||
|
|||
Gonna test it asap
|
#30
|
|||
|
|||
it is not set on other's pc
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mailmerge Excel with a Master Word Template (via VBA) | stevenel | Excel Programming | 8 | 08-14-2018 03:05 PM |
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 |
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 |