Run-time error '5922' - Mail Merge

[dirtychinchilla]

Morning all,

I am attempting to automate a mail merge in word, with the source being an excel spreadsheet. I've included my code below, but it always give me the error "Run-time error '5922': Word was unable to open the data source."

I think it may have something to do with the format being set incorrectly, but I'm really unsure. Also, I haven't yet managed to have it generate the dialogue from which you can choose the source within the excel document.

Any help you guys could give me would be much appreciated.

Sub MailMerge()

    Dim SourceDoc As String
    Dim SelectedItems(1) As String


    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        If .Show <> 0 Then
            SelectedItems(1) = SourceDoc
        Else
            Exit Sub
        End If
    End With
    
    ActiveDocument.MailMerge.OpenDataSource Name:=SourceDoc, _
        ConfirmConversions:=True, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=H:\Jonathon Hunter Hill\Mail Merge\AirMaster V 0.9.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:" _
        , SQLStatement:="SELECT * FROM `MailMerge$`", SQLStatement1:="", SubType _
        :=wdMergeSubTypeAccess
        
End Sub

 

[Macropod]

In your SQL string you have references to both SourceDoc and H:\Jonathon Hunter Hill\Mail Merge\AirMaster V 0.9.xlsm. You should have one or the other; otherwise there's a risk of ambiguity and conflict.

 

[Macropod]

Cross-posted at: Run-time error '5922' - Mail Merge
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters