dcsimg
Results 1 to 12 of 12

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

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    7

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

    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.

  2. #2

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    7

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

    Bump. Any ideas? Still having the issue.

  3. #3
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,210

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

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

    Code:
    Dim strFileName As String
    
    strFileName = "\\servername\etc\Mail Merge Templates\" & strDocName
    Open strFileName For Binary as #1
    Close #1

  4. #4

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    7

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

    Quote Originally Posted by jdc2000 View Post
    Can you get the following code to work with no errors (when added to your code)?

    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.

  5. #5
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,210

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

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

  6. #6

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    7

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

    Quote Originally Posted by jdc2000 View Post
    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).Name:  Capture.jpg
Views: 26
Size:  21.7 KB

  7. #7
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,210

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

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

  8. #8

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    7

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

    Quote Originally Posted by jdc2000 View Post
    What actual line of your code are you getting the error on?
    Code:
    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.

  9. #9
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,210

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

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

  10. #10

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    7

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

    Quote Originally Posted by jdc2000 View Post
    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.

  11. #11
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,210

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

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

  12. #12

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    7

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

    Quote Originally Posted by jdc2000 View Post
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width