none
Upgrading Word automation mailmerge app from VB 6 to VB.net RRS feed

  • Question

  • Background: We have a VB 6 app that uses Word 2003 templates and merges data into the letters. We have MS Office 2010 on the machines, but that didn't pose a problem. The App simply opens a document and for each mergefield loops through a collection and using that mergefield name retrieves the correct value from the collection and inserts it into the mergefield.

    We are upgrading to .net and are having trouble with interop assemblies. Specifically casting com obljects to other data types. We need for instance to the the default name for a mergefield, but any interrogation of the object returns System.__object. The default for .net treatment for com objects seems to return a generic object for any property or method look for. Hence we cannot even get mergefield name.

    I’ve tried using a directcast, but that had no effect. Ctype didn't seem to work at all if I was using it correctly. I haven't tried using an interface as I'm not as familiar with how that would work. Maybe that's the solution?

    Here is the error text: Unable to cast COM object of type 'System.__ComObject' to class type 'System.String'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

    Here is the code:

    Imports Microsoft.Office.Interop
    
    Public Class Form1
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
        End Sub
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            
            Dim wdNewBlankDocument As Object
    
    
            Try
    
                Dim wdWordApp As New Word.Application
                Dim wdDoc As Word.Document
                Dim wdMergeField As Word.MailMergeField
    
                Dim sTemplateLocation As String
                Dim sTemplateName As String
    
                Dim colDataSources As New Collection
    
                colDataSources.Add("742 Evergreen Terrace Springfield", "Address")
                colDataSources.Add("7-15-2014", "CurrentDate")
                colDataSources.Add("Homer Simpson", "FullName")
                colDataSources.Add("742 Evergreen Terrace Springfield", "MailingAddress")
                colDataSources.Add("Mr. Simpson", "LastName")
                
                '---------------------------------------------------------------------------------------------------------
                ' This sub replaces Mail Merge.
                ' It deletes the mail merge field and writes the data in its place.
                '---------------------------------------------------------------------------------------------------------
    
                Dim sMergeFieldName As String
    
                Dim iIndex As Short
                sTemplateLocation = "C:\Users\vbacogallaj\Documents\"
                sTemplateName = "My_Doc.dot"
    
                'UPGRADE_WARNING: Couldn't resolve default property of object wdWordApp.Documents. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
                wdWordApp = New Word.Application
                wdDoc = wdWordApp.Documents.Add(sTemplateLocation & sTemplateName, False, wdNewBlankDocument, True)
                wdDoc.Select()
    
                'RAH UPGRADE_WARNING: Couldn't resolve default property of object wdDOC.MailMerge. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
                For Each wdMergeField In wdDoc.MailMerge.Fields
    
                    '    'UPGRADE_WARNING: Couldn't resolve default property of object oField.Code. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
                    sMergeFieldName = ExtractMailMergeFieldName(wdMergeField.Code)
                    '    'UPGRADE_WARNING: Couldn't resolve default property of object oField.Select. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
                    wdMergeField.Select()
                    '    'UPGRADE_WARNING: Couldn't resolve default property of object oField.Delete. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
                    wdMergeField.Delete()
                    '    'UPGRADE_WARNING: Couldn't resolve default property of object m_DataSources().Item(). Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
                    '    'UPGRADE_WARNING: Couldn't resolve default property of object wdWordApp.Selection. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
    
                    wdWordApp.Selection.TypeText(Replace(colDataSources(iIndex).Item(sMergeFieldName), """", ""))
                Next wdMergeField
    
    
    
                '        'UPGRADE_WARNING: Couldn't resolve default property of object wdWordApp.Visible. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
                wdWordApp.Visible = True
    
    
            Catch ex As Exception
                MsgBox(ex.Message & vbCrLf & ex.Source & vbCrLf & ex.StackTrace)
    
                Using myfile As New System.IO.StreamWriter("C:\Temp\ErrorFile.txt", True)
    
                    myfile.WriteLine(ex.Message)
                    myfile.WriteLine("")
                    myfile.WriteLine(ex.Source)
                    myfile.WriteLine("")
                    myfile.WriteLine(ex.StackTrace)
                    myfile.Close()
    
                End Using
            End Try
    
        End Sub
    
        Private Function ExtractMailMergeFieldName(ByRef sIN As String) As String
    
            '---------------------------------------------------------------------------------------------------------
            ' Parse the string passed in and extract the field name.  The format
            ' of this string is as follows:
            '** Name of Mail merge field: MERGEFIELD MailMergeFieldName \* MERGEFORMAT
            '---------------------------------------------------------------------------------------------------------
    
            Try
                Dim sOut As String
    
                sOut = Replace(sIN, "MERGEFIELD", "")
                sOut = Replace(sOut, "\* MERGEFORMAT", "")
                sOut = sOut.Trim
    
    
                ' remove double quotes
                sOut = Replace(sOut, Chr(34), "")
    
                ExtractMailMergeFieldName = sOut
    
            Catch ex As Exception
                MsgBox(ex.Message & vbCrLf & ex.Source & vbCrLf & ex.StackTrace)
    
            End Try
    
    
    
        End Function
    End Class

    Friday, May 29, 2015 7:51 PM

Answers

  • Hi C.Auguste

    Going through your code I see some issues:

    1. You use "New Word.Application" both when you declare wdWordApp and when you instantiate it. This can lead to problems. Remove "New" where you use it the first time, in the declaration.

    2. As you've apparently noticed, VB.NET is not as "forgiving" as VBA (or VB6, apparently - been a LONG time since I worked with that): You need to fully qualify the property you're looking for and you need to be very aware of which datatype something is. Sometimes, it will help you to type "." after the property that you THINK should be giving you something, but isn't.

    As far as the problem you mention with getting the merge field name: The CODE property returns a Word.RANGE object, not the field code string you're expecting. Stick. Text on the end of that and you'll have more success:

        sMergeFieldName = ExtractMailMergeFieldName(wdMergeField.Code.Text)


    Cindy Meister, VSTO/Word MVP, my blog

    Saturday, May 30, 2015 4:03 PM
    Moderator
  • Thanks Cindy!

    That worked.  Then if blew up a few lines down.  Interesting: in VB6 if a key wasn't found in a collection it didn't throw an error.  .NET apparently does. 

    I had to add this:

    if colDataSources(iIndex).contains(sMergeFieldName) then

       wdWordApp.Selection.TypeText(Replace(colDataSources(iIndex).Item(sMergeFieldName), """", ""))

    end if

    Regarding the duplicate word app declaration, bad cut and paste job. :)

    Joe.

    Tuesday, June 2, 2015 7:57 PM

All replies

  • Hi C.Auguste

    Going through your code I see some issues:

    1. You use "New Word.Application" both when you declare wdWordApp and when you instantiate it. This can lead to problems. Remove "New" where you use it the first time, in the declaration.

    2. As you've apparently noticed, VB.NET is not as "forgiving" as VBA (or VB6, apparently - been a LONG time since I worked with that): You need to fully qualify the property you're looking for and you need to be very aware of which datatype something is. Sometimes, it will help you to type "." after the property that you THINK should be giving you something, but isn't.

    As far as the problem you mention with getting the merge field name: The CODE property returns a Word.RANGE object, not the field code string you're expecting. Stick. Text on the end of that and you'll have more success:

        sMergeFieldName = ExtractMailMergeFieldName(wdMergeField.Code.Text)


    Cindy Meister, VSTO/Word MVP, my blog

    Saturday, May 30, 2015 4:03 PM
    Moderator
  • Thanks Cindy!

    That worked.  Then if blew up a few lines down.  Interesting: in VB6 if a key wasn't found in a collection it didn't throw an error.  .NET apparently does. 

    I had to add this:

    if colDataSources(iIndex).contains(sMergeFieldName) then

       wdWordApp.Selection.TypeText(Replace(colDataSources(iIndex).Item(sMergeFieldName), """", ""))

    end if

    Regarding the duplicate word app declaration, bad cut and paste job. :)

    Joe.

    Tuesday, June 2, 2015 7:57 PM