Word 2010 Interop Print Avery Labels

[G_Waddell]

Hi
I had written an in VB.net that took addresses from a database put them into a csv file then took the csv file data and performed a mail merge in Word to allow the user to print them out on to an Avery L7163 label sheet.

The app was working fine until our company upgraded from Windows XP with office 2000 to Windows 7 with Office 2010.

Instead of getting the labels populated with the address data there are what appear to be form fields printed e.g. <>. Also they are not appearing in the order they should be but rather as a random mis-mash here is the code:

Sub PrintAvery(byref Printer as string, byref Tray as integer, byref CSVFile as String)
	Dim oWord As Object = CreateObject("Word.Application")
	Dim oDoc As Object  = oWord.Documents.add
	With oWord
		.visible = False
		.DisplayAlerts = wdAlertsNone
		.MailingLabel.DefaultPrintBarCode = False
	End With
	'suspect this part is not working as fields not coming in correct order
	With oDoc.MailMerge.Fields
		Dim MySelection = oWord.Selection.Range
                .Add(MySelection, "Address1")
                oWord.Selection.TypeParagraph()
                .Add(MySelection, "Address2")
                oWord.Selection.TypeParagraph()
                .Add(MySelection, "Address3")
                oWord.Selection.TypeParagraph()
                .Add(MySelection, "Address4")
                oWord.Selection.TypeParagraph()
                .Add(MySelection, "Address5")
                oWord.Selection.TypeParagraph()
                .Add(MySelection, "Address6")
                oWord.Selection.TypeParagraph()
                .Add(MySelection, "Address7")
                oWord.Selection.TypeParagraph()
                .Add(MySelection, "Country")
                oWord.Selection.TypeParagraph()
        End With
        Dim myContent = oDoc.Content
        oWord.NormalTemplate.AutoTextEntries.Add("MyLabelLayout", myContent)
        myContent.Delete() 'we no longer need this as we have added it as autotext or have we?	
	Dim ActiveDoc = oWord.ActiveDocument
 	With ActiveDoc.MailMerge
        	.MainDocumentType = 1
                .OpenDataSource(Name:=CSVFile, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
                                WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, Format:=0, Connection:="", SQLStatement:="", SQLStatement1:="")
        End With
	Dim oDoc2 As Object = oWord.MailingLabel.CreateNewDocument(Name:="L7163", Address:="", AutoText:="MyLabelLayout", LaserTray:=Tray) 	
	With ActiveDoc.MailMerge
                .DataSource.QueryString = "SELECT * FROM " & CSVFile
                .Destination = 0
                .MailAsAttachment = False
                .MailAddressFieldName = ""
                .MailSubject = ""
                .SuppressBlankLines = True
                With .DataSource
                    .FirstRecord = 1
                    .LastRecord = -16
                End With
                .Execute(Pause:=True) 'data not coming in
       End With
        If InStr(oWord.ActivePrinter, Printer) = 0 Then
            With oWord.Dialogs.Item(wdDialogFilePrintSetup)
                .printer = Printer
                .donotsetassysdefault = True
                .Execute()
            End With
        End If
        ActiveDoc.PageSetup.FirstPageTray = MainTray
        ActiveDoc.PageSetup.OtherPagesTray = MainTray
        ActiveDoc.PrintOut(Range:=wdPrintAllDocument, Item:=wdPrintDocumentContent, Copies:=1)
        'wait for print out
        System.Threading.Thread.Sleep(1000)

        For Each Doc As Object In oWord.Documents
            Doc.close(False)
        Next
        oWord.quit()	
End Sub

I suspect either the part where I store the field layout as autotext is not working Dim myContent = oDoc.Content
oWord.NormalTemplate.AutoTextEntries.Add("MyLabelLayout", myContent)
Or the part where I set it as the Autotext of the new label document Dim oDoc2 As Object = oWord.MailingLabel.CreateNewDocument(Name:="L7163", Address:="", AutoText:="MyLabelLayout", LaserTray:=Tray)
And the Merge doesn't seam to pick up the data form the csv file even though the file exists and is populated.

Can anyone see what is going wrong? Or suggest an atlernative method?

Thanks in advance

[Adak]

I would ask this in one of the MS usenet groups (in newsgroups). This issue must be affecting others. Your answer may be there waiting for you, already.

In any case, you can get good answers, rather quickly there.

Good luck.

[G_Waddell]

Hi,

Resolved the issue!
I added a connection string and the sql statement to the opendatabase object.
This populated the fields but they all came in mixed up

.OpenDataSource(Name:=srcFile, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
                                WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
                                Connection:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & srcDirectory & """;Mode=Read;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=96;Jet OLEDB:Database Locking Mode=" _
                                , SQLStatement:="SELECT * FROM Addresslist.csv", SQLStatement1:="", SubType:=wdMergeSubTypeOLEDBText)

I then took a look at the mailmerge fields and the autotext and I found an example with slightly different syntax so I tried a few different variations and eventually got the text to go where I wanted it.

With .Fields
                    Dim MySelection = oWord.Selection.Range
                    .Add(Range:=MySelection, Name:="""Address1""")
                    oWord.Selection.TypeParagraph()'inserts and selects a new paragraph
                    MySelection = oWord.Selection.Range
                    .Add(Range:=MySelection, Name:="""Address2""")
                    oWord.Selection.TypeParagraph()
                    MySelection = oWord.Selection.Range
                    .Add(Range:=MySelection, Name:="""Address3""")
                    oWord.Selection.TypeParagraph()
                    MySelection = oWord.Selection.Range
                    .Add(Range:=MySelection, Name:="""Address4""")
                    oWord.Selection.TypeParagraph()
                    MySelection = oWord.Selection.Range
                    .Add(Range:=MySelection, Name:="""Address5""")
                    oWord.Selection.TypeParagraph()
                    MySelection = oWord.Selection.Range
                    .Add(Range:=MySelection, Name:="""Address6""")
                    oWord.Selection.TypeParagraph()
                    MySelection = oWord.Selection.Range
                    .Add(Range:=MySelection, Name:="""Address7""")
                    oWord.Selection.TypeParagraph()
                    MySelection = oWord.Selection.Range
                    .Add(Range:=MySelection, Name:="""Country""")
                End With