Hello All,
I'm trying to do a Word MailMerge using a Word document template and Excel as the data source.
I can successfully create an Excel workbook from my Access VBA, but when I try to do the merge, I get an error saying:
"The Microsoft Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data."
This error appears in the Word application when I switch to it.
The Excel file is not open, and I already set its permissions and the enclosing folder so "Everyone" has full access. The Word template and the Excel sheet are both on my local drive.
Here is a snippet of my Access VBA code which is automating word:
Code:
If isExcel Then sql = "Select * from " & SheetName & "$"
With wordDoc.MailMerge
.OpenDataSource Name:=m_DataFile, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:="", _
Revert:=False, Format:=0, Connection:="", SQLStatement:=sql, SQLStatement1:="", SubType:=wdMergeSubTypeWord2000
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
.DataSource.FirstRecord = 1
.Execute Pause:=False
End With
The error occurs when I try to execute the OpenDataSource command.
If I instead use a CSV file for my data source, it seems to work fine. I use a slightly different SQL statement, and I don't include the SubType argument, but the code is basically the same.
Incidentally, if I open a new Word document by double-clicking the Word template, then I select data manually by browsing to the Excel workbook, the Word merge seems to work fine, and I can get preview results.
Any idea why it won't work in code? Maybe the arguments are wrong for Excel data source?
Thanks...