How macro to change excel source file path-CodePudding

I'm Very New to Macro and VB. I recorded one 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 user to choose DataSource every time?

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

But have the following error:

CodePudding user response：

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