Microsoft Word 12.0 vs  14.0  Object Libraries

NormanMitchell
NormanMitchell used Ask the Experts™
on
I have an Excel project that interacts with Word. The project needs to run in Excel 2007 and Excel 2010. The VBA also needs to reference the Word object library - 12.0 for Excel 2007 and 14.0 for Excel 2010.

The application works fine if it starts in 2007 and is then used in an Excel 2010 environment i.r. Excel successfully switches the reference from 12.0 to 14.0. However the reverse fails i.e. moving from Excel 2010 to Excel 2007 the reference becomes invalid.

I can manage this within code by re-setting the reference but this requires the user to over-ride the Microsoft security setting to allow VBA code to have access to the VBA Project Object Model.

Is there a way to ensure that both Word Object Library versions are available to both an Excel 2007 and an Excel 2010 workbook?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Retired
Top Expert 2012
Commented:
That is really what late binding is for. Since you haven't stressed that you need to use early binding, I guess that you haven't considered it.

Conversion is fairly easy. Mostly you need to Dim all the Word objects as Objects and not as word-specific objects. Also, if you use any constants, you would have to declare them (or use the value directly). So:

Sub MergeRecordEarly()
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim strSQL As String
    
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    
    Set wdDoc = wdApp.Documents.Open("C:\MYFiles\MyMailMergeMain.Doc")
    strSQL = wdDoc.MailMerge.DataSource.QueryString
    wdDoc.MailMerge.DataSource.QueryString = strSQL & " WHERE RecipientID = " & txtID
    With wdDoc.MailMerge
        .Destination = wdSendToEmail
        .Execute
    End With
    wdDoc.Close wdDoNotSaveChanges
    wdApp.Quit
End Sub

Open in new window

becomes

Sub MergeRecordLate()
    Dim wdApp As Object 'Word.Application
    Dim wdDoc As Object 'Word.Document
    Dim strSQL As String
    
    Const wdSendToEmail = 0
    Const wdDoNotSaveChanges = 2
   
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    
    Set wdDoc = wdApp.Documents.Open("C:\MYFiles\MyMailMergeMain.Doc")
    strSQL = wdDoc.MailMerge.DataSource.QueryString
    wdDoc.MailMerge.DataSource.QueryString = strSQL & " WHERE RecipientID = " & txtID
    With wdDoc.MailMerge
        .Destination = wdSendToEmail
        .Execute
    End With
    wdDoc.Close wdDoNotSaveChanges
    wdApp.Quit
End Sub

Open in new window


Don't forget to use Option Explicit and remove the reference when testing, in order to make sure that the compiler finds any forgotten Dims or Constants.

Author

Commented:
As always pointed me in the right direction. Many thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial