CODEWITHSUNDEEP
vbams-word
123bmc
123bmc

Reputation: 27

How to choose mail merge source file path?

I recorded a macro in Word. Below is some portion of the code.

ActiveDocument.MailMerge.OpenDataSource Name:= _
    "Choosen/Folder/Path/file.xlsx", ConfirmConversions:= _
    False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:= _
    "User ID=Admin;Data Source=Choosen/Folder/Path/file.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database L" _
    , SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
    wdMergeSubTypeAccess

How can I allow the user to choose DataSource?

I tried the following:

Public Function ChooseFolder()
    Dim fldr As FileDialog
    Dim sItem As String

    Set fldr = Application.FileDialog(msoFileDialogFilePicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
        sItem = Replace(.SelectedItems(1), "\", "\\")
    End With

NextCode:
    ChooseFolder = sItem
    Set fldr = Nothing
End Function

Sub .....
..
    Dim fldr As String
    fldr = ChooseFolder()
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        fldr, ConfirmConversions:= _
....
End Sub

I have the following error:
error

Upvotes: 0

Views: 154

Answers (1)

jonsson
jonsson

Reputation: 1301

There's a Word dialog box specifically for opening a mailmerge data source.

e.g. at its simplest,

Sub openmmds1()
Dialogs(WdWordDialog.wdDialogMailMergeOpenDataSource).Show
End Sub

although some people say the "correct" way to use these dialogs is as follows:

Sub openmmds2()
Dim dlg As Word.Dialog
Set dlg = Dialogs(WdWordDialog.wdDialogMailMergeOpenDataSource)
dlg.Show
Set dlg = Nothing
End Sub

and you may find that closing any existing data source first avoids some problems. In modern versions of Word on both Windows and Mac you should be able to do that this way:

ActiveDocument.MailMerge.DataSource.Close

but in older versions there is no .Close method and you have to remove all the MailMerge info using, e.g.

ActiveDocument.MailMerge.MainDocumentType = WdMailMergeMainDocType.wdNotAMergeDocument

Upvotes: 1

Related Questions