I have this code in an Excel VBA project.
Code:
Set objWord = CreateObject("Word.Application")
With objWord
.DisplayAlerts = False
.Visible = True
Set oDoc = .Documents.Open(Filename:=fName, ConfirmConversions:=False, _
ReadOnly:=True, AddToRecentFiles:=False, Visible:=True)
With oDoc
With .MailMerge
.MainDocumentType = wdFormLetters
.Destination = wdSendtToNewDocument
.SuppressBlankLines = True
.OpenDataSource Name:=StrSrc, AddToRecentFiles:=False, LinkToSource:=False, ConfirmConversions:=False, _
ReadOnly:=True, format:=wdOpenFormatAuto, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"User ID=Admin;Data Source=" & StrSrc & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";", _
SQLStatement:=StrSQL, SQLStatement1:="", SubType:=wdMergeSubTypeAccess
.Execute Pause:=False
End With
.Close False
End With
I use this code to facilitate the mail merge feature of Word from an event in my Excel VBA project. I am getting an error with the line in red.
"The OpenDataSource method or property is not available because this command is not available for reading."
A few interesting observations (to me anyway) that may help diagnose the problem ...
This code was written last year on my work computer and worked flawlessly. I opted to clean up some of my mail merge documents (formatting etc) from home last night and in my testing there did not get that error, and things worked as expected. This morning at work, I get this error.
I have similar different mail merge documents that rely on this code. They all exhibit this behaviour.
- If I try to open the mail merge document, unlike working with them at home last night, I get an error opening them ... "An operation cannot be completed because of database engine errors."
- Checking on the DataLink Properties, I notice that the Data Source is referring to an old nonexistent file. A check with the "Test Connection" came up with this: "Test connection failed because of an error in initializing provider. Unspecified error"
- I manually directed the mailmerge to an appropriate datasource. The merge completed, wrong information in it though, but that may be because I hadn't isolated the recipients. I saved the report and tried executing the code to open it, but got the same initial message.
This is a major setback for my application and my mail merge documents (for a novice) are complex. I am really hesitant to monkey around with stuff I have no idea about fearing damaging the work that so many have helped me with.
I hope someone will be kind enough to walk me through a diagnosis (using information they need to help track it down) to resolve this.