#1
|
|||
|
|||
Word MergeField Code Text issue
I try to modified a current spreadsheet that I did not created.
In the excel file a button was created to do these step : Create new .CSV file Open my Word master Create word document with my .csv file Close and save the .csv file. When the word document open, they did not ask to select text code. As a default, the Japonese code is select. So cute, but not good. Sould I change something in this ? Thank you in advance Code:
With wdapp 'Disable alerts to prevent an SQL prompt .DisplayAlerts = wdAlertsNone 'Open the mailmerge main document Set wddoc = .Documents.Open("C:\Users\Test.docx") With wddoc .ActiveWindow.View.Type = wdNormalView With .MailMerge 'Define the mailmerge type .MainDocumentType = wdDirectory 'Connect to the data source .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, AddToRecentFiles:=False, _ Revert:=False, Format:=wdOpenFormatAuto, Connection:="Data Source=" _ & strWorkbookName & ";Mode=Read", SQLStatement:="SELECT * FROM 'Sheet1'" .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With 'Define the output .Destination = wdSendToNewDocument 'Excecute the merge .Execute 'Disconnect from the data source .MainDocumentType = wdNotAMergeDocument End With 'Close the mailmerge main document .Close False End With 'Restore the Word alerts .DisplayAlerts = wdAlertsAll 'Display Word and the document .Visible = True End With |
#2
|
||||
|
||||
The only thing that affects the language of a mailmerge main document and, hence, the output documents, is the language settings in the mailmerge main document itself; your VBA code has nothing to do with that.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Ok great ! So there is a place in Word that I should click something like...open always as code text windows as default ?
Thank you |
#4
|
||||
|
||||
This is basic word-processing! Open the mailmerge main document, select everything, then choose Review|Proofing>Set Proofing Language and choose the correct language.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Ok, I tried but it did not worked. Still got japanese letter when in word with "é"
Example : Montr饌l Do you have another idea in your mind ? |
#6
|
||||
|
||||
Without seeing you workbook, & mailmerge main document, it's impossible to know what the issue is with that. Can you attach the mailmerge main document and a workbook with some representative data to a post (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Of course, if you have time to help me it will be kindly appreciated.
I needed to reduce my file. I am a french canadian, you probably need to put your windows session with Français(Canada). The button to start the macro is in AF2 The VBA password is 60515 Choose module : Garanties You need to modify folder link in row 372, 418, 483 & 509 Document Source MAAX - Ver9.5 FR - UW1-008.xlsm Master - Garanties et Options - UW - 020.docx |
#8
|
||||
|
||||
I would be surprised if the field coding in your mailmerge main document would actually work, since you're missing the required space after every mathematical operator (i.e. =, <>). Moreover your field coding is unnecessarily complex. Furthermore, I can't access your VBA code to see what it's doing vis-à-vis the CSV export & mailmerge. Even after supplying the password, I can see no code; all that happens is that I get a DLL loading error for every code module. That said, if you merely named the Excel range to be used for the merge, you could reference that name as the data table in the mailmerge main document without the need for a CSV export. When I do that, I see no output errors.
Attached is a much simplified version of your mailmerge main document.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
|
#10
|
||||
|
||||
I can see the modules; I just can't access them due to a 'DLL could not be loaded' error.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
My current Excel and word template exceed the maximum size for upload. Thank you for your help !
Have you ever this ? On other site I found this : The solution is to create the data source file as a Word document instead of as a text file : Code:
Dim dsDoc As Object Set dsDoc = wwApp.Documents.Add With dsDoc .Content.InsertAfter Text:=<merge field names text> & vbCr .Content.InsertAfter Text:=<merge field data text> ' no trailing vbCR .SaveAs sMergeFile .Close SaveChanges:=wdDoNotSaveChanges End With Set dsDoc = Nothing Code:
Dim strWorkbookName As String strWorkbookName = nomfichiermerge Dim wdapp As New Word.Application Dim wddoc As Word.Document With wdapp 'Disable alerts to prevent an SQL prompt .DisplayAlerts = wdAlertsNone 'Open the mailmerge main document Set wddoc = .Documents.Open("C:\Document\Options - 002.docx") With wddoc .ActiveWindow.View.Type = wdNormalView With .MailMerge 'Define the mailmerge type .MainDocumentType = wdDirectory 'Connect to the data source .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, AddToRecentFiles:=False, _ Revert:=False, Format:=wdOpenFormatAuto, Connection:="Data Source=" _ & strWorkbookName & ";Mode=Read", SQLStatement:="SELECT * FROM 'Sheet1'" .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With 'Define the output .Destination = wdSendToNewDocument 'Excecute the merge .Execute 'Disconnect from the data source .MainDocumentType = wdNotAMergeDocument End With 'Close the mailmerge main document .Close False End With 'Restore the Word alerts .DisplayAlerts = wdAlertsAll 'Display Word and the document .Visible = True End With |
#12
|
||||
|
||||
Quote:
And, as I said before, if you name the Excel range to be used for the merge, you could reference that name as the data table in the mailmerge main document without the need for a CSV export. Finally, if you're to continue using the current approach, you should check the contents of your problem CSV file; the problem may be there and have nothing to do with the merge as such.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#13
|
|||
|
|||
I am pretty sure you are right !
Take #2 Here is my code : Document Source MAAX - Ver9.5 FR - UW1-008a.xlsm |
#14
|
||||
|
||||
There is nothing about your code that would generate an error in the CSV file or in Word's processing of that for a mailmerge. That said, without seeing the CSV file your system produces, I can't comment on that.
Some comments on your code: Do you realise that, with: Dim i, j, k, l, m, n, o, p, q, r, s, t, u As Double only u is a Double - all the rest are Variant. In any event, your usage suggests those that are actually being used should be declared as Long, not as Double. The rest should be omitted as they just add clutter. All this: Code:
Sheets("MergePreFinal").Select Sheets("MergePreFinal").Copy Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Rows("1:2").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Code:
With Sheets("MergePreFinal") .UsedRange.Value = .UsedRange.Value .Rows("1:2").Delete End With Code:
Public Sub Automatisation() 'Dimension des variables Dim i As Long, nomfichier As String 'Automatisation des bases 'On copy paste les lignes du document source concernant les Options et Base seulement dans un fichier Excel se nommant Base With Sheets("MergePreFinal") .UsedRange.Value = .UsedRange.Value .Rows("1:2").Delete 'Espace entre Vie et Vie Pac i = 2 Do If .Cells(i, .Range("FIN").Column) = "BASE" Then i = i + 1 ElseIf .Cells(i, .Range("FIN").Column) = "OPTI" Then i = i + 1 Else .Rows(i).EntireRow.Delete End If Loop Until .Cells(i, Range("FIN").Column) = "FIN" .Rows(i).EntireRow.Delete End With 'Must save in CSV because more than 255 mergefields! Application.DisplayAlerts = False nomfichier = "S:\Merge Excel Files\Base" & Format(Now(), "yyyymmddhhmmss") & ".csv" ActiveWorkbook.SaveAs Filename:=nomfichier, FileFormat:=xlCSV, CreateBackup:=False, _ ConflictResolution:=xlLocalSessionChanges, AddToMRU:=False Application.DisplayAlerts = True Dim wdApp As New Word.Application, wdDoc As Word.Document With wdApp 'Disable alerts to prevent an SQL prompt .DisplayAlerts = wdAlertsNone 'Open the mailmerge main document Set wdDoc = .Documents.Open("S:\Master - Garanties et Options - UW - 019.docx") With wdDoc With .MailMerge 'Define the mailmerge type .MainDocumentType = wdDirectory 'Connect to the data source .OpenDataSource Name:=nomfichier, ReadOnly:=True, AddToRecentFiles:=False, _ Revert:=False, Format:=wdOpenFormatAuto, Connection:="Data Source=" _ & nomfichier & ";Mode=Read", SQLStatement:="SELECT * FROM 'Sheet1'" .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With 'Define the output .Destination = wdSendToNewDocument 'Excecute the merge .Execute 'Disconnect from the data source .MainDocumentType = wdNotAMergeDocument End With 'Close the mailmerge main document .Close False End With 'Restore the Word alerts .DisplayAlerts = wdAlertsAll 'Display Word and the document .Visible = True End With 'le code ouvre un Word avec le sommaire des garanties! End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#15
|
|||
|
|||
Thank you to have revised. This is really appreciate ! I will change my code with your recommendation of course.
I come back with what I posted yesterday. With all my research on internet, there is only one clear solution. https://www.experts-exchange.com/que...ail-merge.html The solution is to create the data source file as a Word document instead of as a text file. The code block above save the data source file as a Word document. You then do the MailMerge specifying the Word document file as the data source (instead of a text file). Could you please help me to introduce it in my current code. Code:
Dim dsDoc As Object Set dsDoc = wwApp.Documents.Add With dsDoc .Content.InsertAfter Text:=<merge field names text> & vbCr .Content.InsertAfter Text:=<merge field data text> ' no trailing vbCR .SaveAs sMergeFile .Close SaveChanges:=wdDoNotSaveChanges End With Set dsDoc = Nothing |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mail Merge Code Issue | Andrewwill | Mail Merge | 6 | 01-06-2018 10:25 PM |
How to link Excel fields with MergeField in Word? | Fabulist | Mail Merge | 3 | 02-03-2016 02:32 PM |
creating a code to create dynamic text in word | ah8471 | Word VBA | 1 | 12-29-2015 10:52 PM |
Printing selected pages based on a MERGEFIELD.g. If MERGEFIELD=x then print pages 1,2,4 if MERGEFIEL | Richard.p | Word | 3 | 04-21-2015 01:14 AM |
Omit mergefield if it contains specific text | MikeStewart | Mail Merge | 3 | 10-01-2014 01:31 AM |