Do not use on any
shared computer
July 24, 2008 01:14pm pdt
 
[x]
Attachment Details

Creating mailing labels in Word from VB.Net

Hello Experts!
I am working ona sub routine that will open a word template and fill an existing table with text from a dbase to create mailing labels...I know Crystal would be easier but I am doing it in Word! The template page I am using is the Avery 5261 (attached), which starts with a single sheet of labels, table columns are 4", .19" and 4".

Here is what I have so far.

Private Sub btnLabels_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLabels.Click

        Dim oWord As Word.Application
        Dim oDoc As Word.Document
        Dim oTable As Word.Table
        Dim oRng As Word.Range
        Dim Pos As Double

        'Start Word and open the label document template.
        oWord = CreateObject("Word.Application")
        oWord.Visible = True
        oDoc = oWord.Documents.Open("c:\temp\labels")
     
       Dim rowcount as 23 'hardcoded for example purposes
       rowcount = rowcount / 2 ' two labels per row
       rowcount = Format(rowcount, "####") ' To make rows a whole number
       
        Dim r As Integer = 1, c As Integer = 1
        oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, rowcount, 3)
        oTable.Range.ParagraphFormat.SpaceAfter = 0

        strsql 'to get data from database
       
        Do While dbread.Read

            If c = 2 Then

                c = c + 1

            End If

            Dim lblatt = field1
            Dim lbldist = field2
            Dim lblstreet = field3
            Dim lblcity = field4
            Dim lblstate = field5
            Dim lblzip = field6
            If Len(lblzip) > 5 Then
                Dim lblzip6 = lblzip.substring(0, 5)
                Dim lblzip4 = lblzip.substring(5, 4)
                lblzip = lblzip6 & "-" & lblzip4
            End If

            Dim lblline2 = lblcity & ", " & lblstate & " " & lblzip

            oTable.Cell(r, c).Range.Text = lbldist & vbCrLf & lblatt & vbCrLf & lblstreet & vbCrLf & lblline2

            c = c + 1
            If c < 4 Then
                r = r
            End If

            If c = 4 Then
                c = 1
                r = r + 1
            End If

        Loop
       
    End Sub

This works EXCEPT...rather than use the template table for ALL labels, it only uese it for the first 20 and then goes to the table which is added in the code. That table is not formatted correctly.

I guess the question is instead of creating a new table at run time after opening the template, how do I identify the cells in the existing table so I can add more than 20 at run time and keep them formatted correctly?

I got my code examples from here: http://support.microsoft.com/kb/316383

Thanks!
Attachments:
 
Avery 5261 Blank
 
Start your free trial to view this solution
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

Question Stats
Zone: Programming
Question Asked By: webkiwi1
Solution Provided By: raja_ind82
Participating Experts: 1
Solution Grade: A
Views: 37
Translate:
Loading Advertisement...
 
[+][-]Accepted Solution by raja_ind82

Rank: Master

Accepted Solution by raja_ind82:

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
20080723-EE-VQP-34 / EE_QW_2_20070628