Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MailMerge datasource directory (WindowsXP / Word2003 NL)

    I've searched the Lounge, but that didn't come up with anything useful. What I want to accomplish in VBA code is:
    1. load a Word document
    2. set this document to be used as the main document for a mailmerge
    3. let the user pick a (comma separated) TXT file in a specific directory on the network (put there by a third party application)
    4. set that textfile to be used as the datasource file for the mailmerge
    5. do other stuff (change printer etc.)
    6. execute the mailmerge.
    The things I cannot get right are 2/3/4. The main purpose of trying to do this in VBA is that the path to the datasource files is very long and using the wizzard forces the user to click his way down to the desired file each time, because the wizzard always starts in the MySources directory. I tried recording a macro when going through the steps manually, but that failed because virtually nothing was recorded... Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MailMerge datasource directory (WindowsXP / Word2003 NL)

    Try this:

    Sub MailMergeTest()
    Dim strFile As String

    With Application.FileDialog(msoFileDialogFilePicker)
    ' Substitute appropriate path
    .InitialFileName = "serversharefolder*.txt"
    .Filters.Add "Text files", "*.txt"
    If .Show = False Then Exit Sub
    strFile = .SelectedItems(1)
    End With

    With ActiveDocument.MailMerge
    .MainDocumentType = wdFormLetters
    .Destination = wdSendToNewDocument
    .OpenDataSource _
    Name:=strFile, _
    LinkToSource:=True, _
    AddToRecentFiles:=False, _
    Format:=wdOpenFormatText
    .Execute
    End With
    End Sub

    You must insert code for #5.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MailMerge datasource directory (WindowsXP / Word2003 NL)

    Thanks Hans. I'll give that a try.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MailMerge datasource directory (WindowsXP / Word2003 NL)

    Thanks Hans, worked like a charme, as usual. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MailMerge datasource directory (WindowsXP / Word2003 NL)

    Two more questions about this mailmerge.
    1. <LI> Every time the mailmerge is executed with a different CSV textfile, a dialogpanel shows up, asking for the field delimiter. As the default, "(None)" is showing. Is it possible to pre-set this delimiter to "," in VBA code, thus avoiding this dialogpanel?

      <LI> Normally each record consists of 5 fields, separated bij ",". But every now and then somebody has typed a value in a field that includes 1or more ",". So Word's mailmerge thinks that record contains more than 5 fields and generates a message "Too many fields in record X". And because this CSV textfile is the result of an automated process, this is very annoying. Is it possible to tell Word to ignore the extra field(s) or is there some workaround for this?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MailMerge datasource directory (WindowsXP / Word2003 NL)

    1) I don't know.
    2) If fields in a delimited text file (such as a .csv file) contain the delimiter character, the fields should be enclosed in quotes. Otherwise, there is no way for the application that processes the text file to know what is what. The application that created the .csv files should have an option for this.

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MailMerge datasource directory (WindowsXP / Word2003 NL)

    Thanks Hans.
    About 2): I've mailed the third party about our problem and your suggestion of putting the value of each field in quotes. They immediately mailed back with another solution: alter a parameter for their application, to use a semicolon instead of a comma (their default) to separate the fields. I tried this just now and it works fine. I can't believe they "forgot" to mention this in their documentation... <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •