dirtychinchilla
Board Regular
- Joined
- Jun 21, 2013
- Messages
- 234
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.
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.
Code:
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