How to Skip Used Mailing Labels and Print Duplicates
Background:
One feature that Microsoft Word has, but Microsoft Access does not
have, is the ability to choose a starting label. However, moving your
ActiveX barcoding application to Word complicates matters because Word
does not support Data Binding. This means that there is no control source
property, and you cannot simply bind the control to a field in your Access
Database.
Solution:
There are 2 solutions to this problem. Both require some VBA
Code. The first and easiest solution is to follow the steps in this Microsoft
Knowledge Base article: Q95806.
This allows you to remain in Access, but also to choose your starting
label (and/or create duplicate labels). If you are more confident with
VBA or would rather use Word as your labelling tool, we have developed
some sample code for Microsoft Word that will loop through all of the
records in an Access table and create barcodes on a label document in
Word.
To use the code below:
 Download
the Word 97/2000 Template that will allow you to install this macro
and others and jump to step 6 or Copy the Sub
Main() Routine to the clipboard by highlighting it and pressing
Ctrl + C.- In Microsoft Word, Open the Visual Basic Editor by pressing Alt +
F11
- Insert a new Module, by clicking on Insert|Module
- Paste the code into the module
- You must create a reference to Microsoft DAO. Included below
is a Macro that can try to create the reference for you. - Copy the
AddReference macro to your module and run it. If you prefer, you can
manually add the reference by going to the Tools Menu in the visual
Basic Editor and click on References. Find Microsoft DAO on the list
and check the box beside it.
- Change a few details in the code to have the macro open the correct
database, table and field and also to the create the correct Labels
for your application. (The green comments below indicate where you should
make your changes.)
Sub Main()
'
'This is an example of a fairly simple Word macro that will loop 'through
all the records a table in MSAccess, taking the data 'from a field and
converting it to barcodes on a MS Word Label 'document
'NB - you must
create a reference to Microsoft DAO for this macro 'to work. If you installed
this macro using the Install Wizard 'that came with the other ActiveX
macros then the reference has
'already been created.
'
' Last updated April 2001.
' ---------------------------------------------------------------
Dim Db, RS, DBName As String, DBTable As String
Dim i As Long, j As Long, Rcount As Long, nRow, nCol
'Name with full path of database to open
DBName = "C:\Program files\Microsoft Office\Office\" _
& "Samples\Northwind.mdb"
'Name of table in Database
DBTable = "Shippers"
'open database (Must create Reference to Microsoft
DAO)
Set Db = OpenDatabase(Name:=DBName)
'open Shippers Table
Set RS = Db.OpenRecordset(Name:=DBTable)
'Get Record count (for number of labels)
Rcount = RS.RecordCount - 1
'loop through each record in the table
For i = 0 To Rcount
'take the value of the first field and pass it to
activex
'You may use the name of the field or its index number:
'e.g. frmMain.TALBarCd1.Message = RS.Fields("fieldname").Value
frmMain.TALBarCd1.Message = RS.Fields(1).Value
'save barcode with record number
frmMain.TALBarCd1.SaveBarCode AXMyPath$ & "Barcode" &
i & ".wmf"
'move to next record
RS.MoveNext
Next 'I
'close table, then close Database
RS.Close
Db.Close
'Create new empty labels document - change the Name:=
attribute 'to reflect the type of labels you wish to generate
Application.MailingLabel.CreateNewDocument Name:="8463", Address:="",
_
AutoText:="", LaserTray:=wdPrinterManualFeed
SetRow:
'Set starting column and Row
nRow = InputBox("Please indicate which Row you would like to start
on")
nCol = InputBox("Please indicate which Column you would like to start
on")
'Check validity of choices
If Selection.Information(wdMaximumNumberOfRows) < Val(nRow) Or Selection.Information(wdMaximumNumberOfColumns)
< Val(nCol) Or Val(nRow) <= 0 Or Val(nCol) <= 0 Then
If MsgBox("Row must be between 1 and" + Str(Selection.Information(wdMaximumNumberOfRows))
+ ". Column must be between 1 and" + Str(Selection.Information(wdMaximumNumberOfColumns))
+ ". Try again?", vbRetryCancel) = 4 Then
GoTo SetRow
Else
GoTo bye
End If
End If
'move to requested
label
While Selection.Information(wdStartOfRangeRowNumber) < Val(nRow)
Selection.MoveDown Unit:=wdLine, Count:=1, Extend:=wdMove
Wend
While Selection.Information(wdStartOfRangeColumnNumber)
< Val(nCol)
Selection.MoveRight Unit:=wdCell, Count:=1
Wend
For j = 0 To Rcount
'If current cell is padder then move to the next
cell
If Selection.Information(wdWithInTable) = True Then
Do While PointsToInches(Selection.Cells.Width) < 1
Selection.MoveRight Unit:=wdCell
Loop
End If
'Insert Barcode
Selection.InlineShapes.AddPicture FileName:=AXMyPath$ & "barcode"
& j & ".wmf", LinkToFile:= _
False, SaveWithDocument:=True
'move to next cell
Selection.MoveRight Unit:=wdCell
On Error Resume Next
'delete last barcode
Kill AXMyPath$ & "barcode" & j & ".wmf"
On Error GoTo 0
Next 'j
bye:
End Sub
The Following macro adds the Reference to Microsoft DAO:
Sub AddReference()
Dim j, FoundDAORef
'Check/AddReferences:
FoundDAORef = False
With Documents(ThisDocument.Name).VBProject
For j = 1 To .References.Count
If .References(j).Guid = "{00025E01-0000-0000-C000-000000000046}"
Then
FoundDAORef = True
End If
Next 'j
If FoundDAORef = False Then
'add reference to DAO 3.5 or later
.References.AddFromGuid "{00025E01-0000-0000-C000-000000000046}",
3, 5
End If
End With
End Sub
The following macro removes the reference to DAO:
Sub RemoveReference()
Dim j, FoundDAORef, RefObj
FoundDAORef = False
With Documents(ThisDocument.Name).VBProject
For j = 1 To .References.Count
If .References(j).Guid = "{00025E01-0000-0000-C000-000000000046}"
Then
FoundDAORef = True
End If
Next 'j
If FoundDAORef = True Then
'remove reference to DAO 3.5 or later
Set RefObj = .References("DAO")
.References.Remove (RefObj)
End If
End With
set RefObj = Nothing
End Sub
More labelling tools can be found in the ActiveX
Macro Library.
|
