Printing Mailing Labels

Any easy way to align many many pages of 3 accross.
I've managed the three accross by using variables to print each row at a time even if any column has more than 3 rows. I've worked thru about 6 pages and every so many labels I have to add a line.

Somewhere within the 386 labesl the alignment is off.

Isn't there some kind of layout?

Comments

  • : Any easy way to align many many pages of 3 accross.
    : I've managed the three accross by using variables to print each row at a time even if any column has more than 3 rows. I've worked thru about 6 pages and every so many labels I have to add a line.
    :
    : Somewhere within the 386 labesl the alignment is off.
    :
    : Isn't there some kind of layout?
    :

    If the pc has MS Word installed, its easy to automate a mail merge and use one of the built-in label templates.
  • : : Any easy way to align many many pages of 3 accross.
    : : I've managed the three accross by using variables to print each row at a time even if any column has more than 3 rows. I've worked thru about 6 pages and every so many labels I have to add a line.
    : :
    : : Somewhere within the 386 labesl the alignment is off.
    : :
    : : Isn't there some kind of layout?
    : :
    :
    : If the pc has MS Word installed, its easy to automate a mail merge and use one of the built-in label templates.
    :
    Yes, I have MS Word but how do I automate it?
  • : : : Any easy way to align many many pages of 3 accross.
    : : : I've managed the three accross by using variables to print each row at a time even if any column has more than 3 rows. I've worked thru about 6 pages and every so many labels I have to add a line.
    : : :
    : : : Somewhere within the 386 labesl the alignment is off.
    : : :
    : : : Isn't there some kind of layout?
    : : :
    : :
    : : If the pc has MS Word installed, its easy to automate a mail merge and use one of the built-in label templates.
    : :
    : Yes, I have MS Word but how do I automate it?
    :


    Below is the code, you'll need to set up a label mailmerge document for your label size [use the mail merge wizard...]
    Post an email address and I can send a working program w/ all files.


    [code]
    Option Explicit


    'PROJECT, REFERENCES..., must set a reference to
    'Microsoft Word 10.0 object library [for Word 2002/XP]
    '[the object library # will difer for each version of Office]

    Dim WordApp As Word.Application 'Word Object

    Const MyDoc = "AddressBlock.doc" 'MS Word 2002 label template
    Const MyDB = "Address.mdb" 'Access database file for mail-merge
    Const MyTable = "Table1" 'Access table with list of label addresses
    Const MyLabel = "MyLabels.doc" 'Word doc-filename to save labels to


    Private Sub Command1_Click()
    '***OPEN WORD***
    'init word object
    Set WordApp = New Word.Application
    'show word [set to false if you don't want to show]
    WordApp.Application.Visible = True
    'open word file
    WordApp.Documents.Open App.Path & "" & MyDoc
    End Sub


    Private Sub Command2_Click()
    '***LABEL MAIL MERGE***
    'mail merge to labels template
    WordMailMerge WordApp, App.Path & "" & MyDoc
    End Sub


    Private Sub Command3_Click()
    '***QUIT WORD***
    Dim i As Integer
    'close document(s) without saving
    For i = 1 To WordApp.Documents.Count
    WordApp.Documents.Item(i).Close False
    Next i
    'quit word
    WordApp.Quit
    'release resouces
    Set WordApp = Nothing
    End Sub


    'merge db field values to a word doc
    Public Sub WordMailMerge(ByVal WordApp As Word.Application, cWordFile As String)
    Dim sCon As String, Sql As String

    On Error GoTo erh:

    Sql = "SELECT * FROM `" & MyTable & "`"

    sCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & App.Path & "" & MyDB & ";" & _
    "Persist Security Info=False;"


    'setup and perform mail-merge
    With WordApp.ActiveDocument.MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource App.Path & "" & MyDB, False, False, True, False, False, , , , , , sCon, Sql, "", wdMergeSubTypeAccess
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
    End With
    .Execute
    End With

    'save and close merged doc
    WordApp.Documents.Item(1).SaveAs App.Path & "" & MyLabel
    WordApp.Documents.Item(1).Close False

    xit:
    Exit Sub

    erh:
    If Err.Number = 5631 Then
    MsgBox "No records for mail merge", vbInformation, "Error"
    Else
    MsgBox Err.Description, vbCritical, Err.Number
    End If
    Resume xit
    End Sub
    [/code]
  • : : : : Any easy way to align many many pages of 3 accross.
    : : : : I've managed the three accross by using variables to print each row at a time even if any column has more than 3 rows. I've worked thru about 6 pages and every so many labels I have to add a line.
    : : : :
    : : : : Somewhere within the 386 labesl the alignment is off.
    : : : :
    : : : : Isn't there some kind of layout?
    : : : :
    : : :
    : : : If the pc has MS Word installed, its easy to automate a mail merge and use one of the built-in label templates.
    : : :
    : : Yes, I have MS Word but how do I automate it?
    : :
    :
    :
    : Below is the code, you'll need to set up a label mailmerge document for your label size [use the mail merge wizard...]
    : Post an email address and I can send a working program w/ all files.
    :
    :
    : [code]
    : Option Explicit
    :
    :
    : 'PROJECT, REFERENCES..., must set a reference to
    : 'Microsoft Word 10.0 object library [for Word 2002/XP]
    : '[the object library # will difer for each version of Office]
    :
    : Dim WordApp As Word.Application 'Word Object
    :
    : Const MyDoc = "AddressBlock.doc" 'MS Word 2002 label template
    : Const MyDB = "Address.mdb" 'Access database file for mail-merge
    : Const MyTable = "Table1" 'Access table with list of label addresses
    : Const MyLabel = "MyLabels.doc" 'Word doc-filename to save labels to
    :
    :
    : Private Sub Command1_Click()
    : '***OPEN WORD***
    : 'init word object
    : Set WordApp = New Word.Application
    : 'show word [set to false if you don't want to show]
    : WordApp.Application.Visible = True
    : 'open word file
    : WordApp.Documents.Open App.Path & "" & MyDoc
    : End Sub
    :
    :
    : Private Sub Command2_Click()
    : '***LABEL MAIL MERGE***
    : 'mail merge to labels template
    : WordMailMerge WordApp, App.Path & "" & MyDoc
    : End Sub
    :
    :
    : Private Sub Command3_Click()
    : '***QUIT WORD***
    : Dim i As Integer
    : 'close document(s) without saving
    : For i = 1 To WordApp.Documents.Count
    : WordApp.Documents.Item(i).Close False
    : Next i
    : 'quit word
    : WordApp.Quit
    : 'release resouces
    : Set WordApp = Nothing
    : End Sub
    :
    :
    : 'merge db field values to a word doc
    : Public Sub WordMailMerge(ByVal WordApp As Word.Application, cWordFile As String)
    : Dim sCon As String, Sql As String
    :
    : On Error GoTo erh:
    :
    : Sql = "SELECT * FROM `" & MyTable & "`"
    :
    : sCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    : "Data Source=" & App.Path & "" & MyDB & ";" & _
    : "Persist Security Info=False;"
    :
    :
    : 'setup and perform mail-merge
    : With WordApp.ActiveDocument.MailMerge
    : .MainDocumentType = wdFormLetters
    : .OpenDataSource App.Path & "" & MyDB, False, False, True, False, False, , , , , , sCon, Sql, "", wdMergeSubTypeAccess
    : .Destination = wdSendToNewDocument
    : .SuppressBlankLines = True
    : With .DataSource
    : .FirstRecord = wdDefaultFirstRecord
    : .LastRecord = wdDefaultLastRecord
    : End With
    : .Execute
    : End With
    :
    : 'save and close merged doc
    : WordApp.Documents.Item(1).SaveAs App.Path & "" & MyLabel
    : WordApp.Documents.Item(1).Close False
    :
    : xit:
    : Exit Sub
    :
    : erh:
    : If Err.Number = 5631 Then
    : MsgBox "No records for mail merge", vbInformation, "Error"
    : Else
    : MsgBox Err.Description, vbCritical, Err.Number
    : End If
    : Resume xit
    : End Sub
    : [/code]
    :
    Thanks, I already found simular code on Microsoft but I appreciate the efforts. That code did not explain that you needed to go to Project References and set the WORD - but I figured it out.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories