1

I'm using mail merge function to merge data from excel sheets to word letter. and I use the following connection and command.

Sub WriteInWord()
    Dim WdApp As Object
    Dim WdDoc As Object
    result = Path.GetFileName(TextBox1.Text)
    Dim BatchNumber = Mid(result, 18, 5)

    WdApp = CreateObject("Word.Application")
    WdDoc = CreateObject("Word.Document")

    WdDoc = WdApp.Documents.Open(TextBox2.Text, ConfirmConversions:=
    False, ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="",
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
    WritePasswordTemplate:="", XMLTransform:="")

    WdDoc.MailMerge.OpenDataSource(Name:=TextBox1.Text,
    ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
    AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="",
    WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, Connection:=
    "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin; Mode=Read; Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database=""""; Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0; Jet OLEDB:Global", SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="")

    ' .Destination  0 = DOCUMENT, 1 = PRINTER
    WdApp.ActiveDocument.MailMerge.Destination = 0 'wdSendToNewDocument
    WdApp.ActiveDocument.MailMerge.SuppressBlankLines = True
    With WdApp.ActiveDocument.MailMerge.DataSource
        .FirstRecord = 1     'wdDefaultFirstRecord
        .LastRecord = -16  'wdDefaultLastRecord
    End With
    WdApp.ActiveDocument.MailMerge.Execute(Pause:=False)

    WdDoc.Close(savechanges:=False) 'Close the original mail-merge template file.
    Dim outputFilename As String = Path.ChangeExtension(inputFilePath + "\" + BatchNumber + ".docx", "pdf")
    Dim fileFormat As Object = WdSaveFormat.wdFormatPDF
    WdApp.ActiveDocument.SaveAs(outputFilename, fileFormat)
    WdApp.Quit()
    WdDoc = Nothing
    WdApp = Nothing
    MsgBox("FINISHED WITH MERGE")
End Sub

the excel value is this Merged value from excel to word is this

As you can see, the value from excel to word has changed. How can I fix this? Thank you.

macropod
  • 12,757
  • 2
  • 9
  • 21
Wind
  • 27
  • 4
  • Is this really a VB.NET question? Regardless, maybe you could show all the relevant code. That doesn't even look like a complete line of code. – John Jun 06 '22 at 07:48
  • Can you share word file , excel file? – D T Jun 06 '22 at 09:37
  • Maybe reaching a little here, but think this might be to do with the overly helpful Excel data type formatting. If so your best start might be to edit the cell format(s) in the excel file to be Text rather than the default of General – Hursey Jun 06 '22 at 21:27
  • To format the output correctly, see the relevant topics in the **Mailmerge Tips and Tricks** page at: https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html – macropod Jun 08 '22 at 21:49

1 Answers1

0

Thanks for the inspiration.

I know where's my problem. I changed the SQL statement from

SQLStatement:="SELECT * FROM `Sheet1$`"

To

SQLStatement:="SELECT * FROM `SheetName$`"

The "SheetName" is excel sheet name, because my spreadsheet has a name.

Wind
  • 27
  • 4