none
Can I avoid Word 2007 displaying a pointless Dialog when I use OpenDataSouce to open a defined Sheet in an Excel Spreadsheet? RRS feed

  • Question

  • I am using:

                ActiveDocument.MailMerge.OpenDataSource _
                    Name:=s_ExcelDataSourcePathname, _
                    Connection:=g_s_ExcelSheetName, _
                    ConfirmConversions:=False

    to always open exactly the same ExcelSheetName in a particular ExcelDataSourcePathname, yet Word insists on prompting me with a Dialog box asking me to confirm the sheet name.  Is there any way this can be avoided?  I've looked at all the Arguments of OpenDataSource and none of them seem relevant.

    Thursday, July 2, 2015 6:09 AM

Answers

All replies

  • OOPS!  I know the answer to this myself see: http://support.microsoft.com/kb/289830/

    I suddenly remembered not having this problem when we open a Word Document and Open its DataSource.  A quick look back at our original code referred me to that KB Article.  I'll post the code here when I've got it going.  :-)

    Thursday, July 2, 2015 10:33 AM
  • Here's the code:

                s_WorkSheetName = "[Mail-Merge Addresses - E-mail$]"   'NOTE: use of "[....$]"
    
                'From WORD 2003 on, the Word Object behaves differently.  Reference:
                '   http://support.microsoft.com/kb/289830/
                '
                'Word 2003 on automatically uses OLEDB to access the DataSource Object and not DDE.
                'OLDB expects you to use:  SQLStatement:=
                '                and NOT:  Connection:=
                'as it ignores the Connection argument.
                'The lack of an SQLStatement causes it to display the 'Select Table' dialog box.
                '
                'So this works for ALL Versions of Word
                If WordApplication.Version > 10 Then
                    'We need to use SQLStatement:=
                    WordDocument.MailMerge.OpenDataSource _
                        Name:=s_DataSourcePathname, _
                        ReadOnly:=False, _
                        SQLStatement:="SELECT * FROM " & s_WorkSheetName
    
                Else
                    'For earlier versions we can use Connection:=
                    WordDocument.MailMerge.OpenDataSource _
                        Name:=s_DataSourcePathname, _
                        ReadOnly:=False, _
                        Connection:="SELECT * FROM " & s_WorkSheetName
                End If
    

    Thursday, July 2, 2015 11:28 PM