Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-23-2018, 07:26 PM
stevenel stevenel is offline Word MergeField Code Text issue Windows 7 64bit Word MergeField Code Text issue Office 2013
Novice
Word MergeField Code Text issue
 
Join Date: Jul 2018
Posts: 26
stevenel is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 09-23-2018, 07:51 PM
macropod's Avatar
macropod macropod is offline Word MergeField Code Text issue Windows 7 64bit Word MergeField Code Text issue Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,349
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Quote:
Originally Posted by stevenel View Post
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.
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
[MS MVP - Word]
Reply With Quote
  #3  
Old 09-23-2018, 07:57 PM
stevenel stevenel is offline Word MergeField Code Text issue Windows 7 64bit Word MergeField Code Text issue Office 2013
Novice
Word MergeField Code Text issue
 
Join Date: Jul 2018
Posts: 26
stevenel is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 09-23-2018, 08:02 PM
macropod's Avatar
macropod macropod is offline Word MergeField Code Text issue Windows 7 64bit Word MergeField Code Text issue Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,349
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
[MS MVP - Word]
Reply With Quote
  #5  
Old 09-23-2018, 08:26 PM
stevenel stevenel is offline Word MergeField Code Text issue Windows 7 64bit Word MergeField Code Text issue Office 2013
Novice
Word MergeField Code Text issue
 
Join Date: Jul 2018
Posts: 26
stevenel is on a distinguished road
Default

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 ?
Reply With Quote
  #6  
Old 09-23-2018, 08:38 PM
macropod's Avatar
macropod macropod is offline Word MergeField Code Text issue Windows 7 64bit Word MergeField Code Text issue Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,349
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
[MS MVP - Word]
Reply With Quote
  #7  
Old 09-24-2018, 05:06 PM
stevenel stevenel is offline Word MergeField Code Text issue Windows 7 64bit Word MergeField Code Text issue Office 2013
Novice
Word MergeField Code Text issue
 
Join Date: Jul 2018
Posts: 26
stevenel is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 09-24-2018, 06:00 PM
macropod's Avatar
macropod macropod is offline Word MergeField Code Text issue Windows 7 64bit Word MergeField Code Text issue Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,349
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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.
Attached Files
File Type: docx Master - Garanties et Options - UW - 020.docx (22.6 KB, 4 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #9  
Old 09-24-2018, 06:32 PM
stevenel stevenel is offline Word MergeField Code Text issue Windows 7 64bit Word MergeField Code Text issue Office 2013
Novice
Word MergeField Code Text issue
 
Join Date: Jul 2018
Posts: 26
stevenel is on a distinguished road
Default

Weird ! You are not able to see this with password 60515 in Excel ?

Capture.JPG
Reply With Quote
  #10  
Old 09-24-2018, 07:20 PM
macropod's Avatar
macropod macropod is offline Word MergeField Code Text issue Windows 7 64bit Word MergeField Code Text issue Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,349
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

I can see the modules; I just can't access them due to a 'DLL could not be loaded' error.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #11  
Old 09-24-2018, 08:06 PM
stevenel stevenel is offline Word MergeField Code Text issue Windows 7 64bit Word MergeField Code Text issue Office 2013
Novice
Word MergeField Code Text issue
 
Join Date: Jul 2018
Posts: 26
stevenel is on a distinguished road
Default

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
I am just a little bit confuse how integrated in my code :

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
Reply With Quote
  #12  
Old 09-24-2018, 08:21 PM
macropod's Avatar
macropod macropod is offline Word MergeField Code Text issue Windows 7 64bit Word MergeField Code Text issue Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,349
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Quote:
Originally Posted by stevenel View Post
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:
...
I am just a little bit confuse how integrated in my code
I can't see how that is at all relevant to your current process. In any event, you already have a functional Word document, so there should be no need to create a new one.

And, as I said before, if you name the he 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
[MS MVP - Word]
Reply With Quote
  #13  
Old 09-25-2018, 05:00 PM
stevenel stevenel is offline Word MergeField Code Text issue Windows 7 64bit Word MergeField Code Text issue Office 2013
Novice
Word MergeField Code Text issue
 
Join Date: Jul 2018
Posts: 26
stevenel is on a distinguished road
Default

I am pretty sure you are right !

Take #2
Here is my code :

Document Source MAAX - Ver9.5 FR - UW1-008a.xlsm
Reply With Quote
  #14  
Old 09-25-2018, 06:01 PM
macropod's Avatar
macropod macropod is offline Word MergeField Code Text issue Windows 7 64bit Word MergeField Code Text issue Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,349
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
could be reduced to:
Code:
    With Sheets("MergePreFinal")
      .UsedRange.Value = .UsedRange.Value
      .Rows("1:2").Delete
    End With
Your code also has a lot more circumlocution, too; it seems to me it could all be reduced to:
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
[MS MVP - Word]
Reply With Quote
  #15  
Old 09-25-2018, 07:23 PM
stevenel stevenel is offline Word MergeField Code Text issue Windows 7 64bit Word MergeField Code Text issue Office 2013
Novice
Word MergeField Code Text issue
 
Join Date: Jul 2018
Posts: 26
stevenel is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Word MergeField Code Text issue Mail Merge Code Issue Andrewwill Mail Merge 6 01-06-2018 10:25 PM
Word MergeField Code Text issue 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
Word MergeField Code Text issue 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 09:01 AM.


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