[Mail Merge] 'Word was unable to open the data source.'

[GeminiRift]

Hi everyone,

I work for a non-profit organization that deals with mail merges frequently. As such, we've created an automated program that downloads files from our off-site date house's FTP, then creates SQL tables from the data.

That being said, I'm attempting to have the automated program create PDFs for certain files that come down. I've got all the housekeeping things done (creating mail merge document templates, making sure it only triggers when necessary, calls the correct SQL table, etc), but I'm receiving an error after the document is opened and it attempts to open the data source. The data source (.odc file) is stored on a separate server (the program has access to the server, as it can open other files from it without any issues).

strDocName = "(name of the document it's opening)"
strSql = "SELECT * FROM (name of the table) ORDER BY (a 2 up sequence number field) DESC"
goWord = CreateObject("Word.Application")
odoc = goWord.Documents.open("\\servername\etc\Mail Merge Templates\" & strDocName)
MsgBox(strSql)
MsgBox(strDataSource)
odoc.MailMerge.OpenDataSource(Name:=strDataSource, SQLStatement:=strSql)

As you can see, I've got MBs set up to make sure I'm attempting to open the correct file and that there isn't an error with my SQL string, and as far as I can tell, nothing is wrong with the data the .OpenDataSource command is being given.

I hope I was descriptive enough to explain the problem, but please let me know if I've left out any important details. Thanks for any help.

[GeminiRift]

Bump. Any ideas? Still having the issue.

[jdc2000]

Can you get the following code to work with no errors (when added to your code)?

Dim strFileName As String

strFileName = "\\servername\etc\Mail Merge Templates\" & strDocName
Open strFileName For Binary as #1
Close #1

[GeminiRift]

Can you get the following code to work with no errors (when added to your code)?

Dim strFileName As String

strFileName = "\\servername\etc\Mail Merge Templates\" & strDocName
Open strFileName For Binary as #1
Close #1

Hi, thanks for the reply!

However, that doesn't appear to be valid VB code within my program. Were you short-handing?

EDIT: If you're wanting me to check if there is an issue opening the Word document, I am able to set the word object to visible and see that it is opening fine. However, it's throwing the issue as soon as it attempts to read/open that data source.

[jdc2000]

If you open Word, can you manually navigate to the file in question and open it?

[GeminiRift]

If you open Word, can you manually navigate to the file in question and open it?

Yessir, that is correct. When I open this Word document, it asks me to run the SQL query in question, and displays results. Please refer to attached (with sensitive information blacked out).

[jdc2000]

What actual line of your code are you getting the error on?

[GeminiRift]

What actual line of your code are you getting the error on?

odoc.MailMerge.OpenDataSource(Name:=strDataSource, SQLStatement:=strSql)

The odd thing is, the strDataSource is pulled directly from the same location as another program we use (it's manual; wanting this one to be automated), but it gives errors while the manual one does not. They both have it listed in Settings as an Application String with the same name as well.

I also had a thought that the issue may not be with the source, but the SQL statement in question. However, I've MsgBox'd the SQL string and it runs fine from SQL Server Management Studio. Displays all results.

[jdc2000]

Check permissions for the applicable user in SQL Server for access to the database.

[GeminiRift]

Check permissions for the applicable user in SQL Server for access to the database.

That would be my network account. I'm listed as a sysadmin/dbowner for all of these.

Side note, thank you for all of your suggestions. I really appreciate any help.

[jdc2000]

I am out of ideas at this point. You could try using VBA to automate the mail/merge, just to see if that works.

[GeminiRift]

I am out of ideas at this point. You could try using VBA to automate the mail/merge, just to see if that works.

Yeah, it's definitely confusing. I know there are some backdoor ways of getting around all of this, but I'm attempting to make this work for all future mail merges as well.

Thanks again for the help!