Label printing

[paulu]

How do I automate printing labels with multple font styles. I have a macro
which allows input of the number of sheets to be printed. However the
formating of the text is not maintained when the macro is run. How do I set
font style, size etc in the code of the macro?

 

[Doug Robbins]

If you print them manually is the formatting maintained? You imply that it
is the print macro that is removing the formatting. If that is the case,
you will need to show us the macro and give some information on how the
formatting was originally achieved.

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[paulu]

Hi Doug,
Thanks for the help.
Originally I I was printing 1 sheet at a time with text from a word
document. The text was formatted and I just swipe the text and it was
transfer to the label printing wizard with the formatting in tact.

When I created the macro the formatting was lost. The formatting consisted
of the first part of the text being 10 pt bold Arial font and the secod part
8 pt normal Arial font. With the macro it all came out as 10 pt normal
weight Times New Roman (I think).

The problem is that the macro doesn't swipe the text in the document, the
original text is permanent in the code. This presents the next problem.
That is I cant change the text for the label without re recording the macro.

Anyway here is the code;

Sub Print_Multiple_Sheets()
'
' Print_Multiple_Sheets Macro
' Macro recorded 17/11/2004 by Paul Ulcoq
'
Dim Message, Title, Default, MyValue
Message = "Enter the number of sheets to print" ' Set prompt.
Title = "Number of Sheets" ' Set title.
Default = "1" ' Set default.
' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)


For x = 1 To MyValue

Selection.WholeStory
Application.MailingLabel.DefaultPrintBarCode = False
Application.MailingLabel.PrintOut Name:="L7173", Address:= _
"Frozen Cooked Whole Black Tiger Shrimp" & Chr(13) & _
"Packs per Carton: 5 packs" & Chr(13) & _
"Pieces per Carton: 5 x 32 pieces" & Chr(13) & "Packed Date:
17/12/2004" _
& Chr(13) & "Best Before: 17/12/2004" & Chr(13) & "Est. No. 1495" &
Chr( _
13) & _
"Ingredients: whole shrimp, Sodium acetate, Acetic acid, Fumaric
acid" & _
Chr(13) & "Mono-Sodium fumarate, Dextrin, Sodium L-glutimate,
Cellulose" & _
Chr(13) & "Corn Starch, Hydrolysed Animal Protien, Yeast Extract,
Salt", ExtractAddress:=False, LaserTray _
:=wdPrinterManualFeed, SingleLabel:=False,
PrintEPostageLabel:=False, _
Vertical:=False

Next x
End Sub

The text I would like to be able to change and have it flow through are
packed dates, Est Numbers and ingredients.

Thanks again and cheers

 

[Doug Robbins]

Recording macros is good for learning a bit syntax, but is not suitable for
your purpose.

I would use the Labels dialog and select the 7173 label and click the New
Document button to get a document containing and empty sheet of those
labels. Then I would save that document as a template in your user
templates folder with the name 7173.dot. You can then close it.

Now, assuming that you have the formatted text that you want on the label in
the active document, if you select that text, and then run a macro
containing the following code:

Dim LabelText As Range, labeldoc As Document, i As Long, j As Long
Set LabelText = Selection.Range
Set labeldoc = Documents.Add("c:\documents and
settings\[username]\Application Data\Microsoft\Templates\7173.dot")
With labeldoc.Tables(1)
For i = 1 To 3 Step 2 'skip the space between the labels.
For j = 1 To 5
.Cell(j, i).Range.FormattedText = LabelText
Next j
Next i
End With
Dim Message, Title, Default, MyValue
MyValue = InputBox("Enter the number of sheets to print", "Number of
Sheets", "1")
labeldoc.PrintOut Copies:=MyValue

While I have not actually tested this, it should do what you want.
--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[paulu]

Doug,
This worked well. I just added a little more code to tidy up at the end.
The final routine looked like this:

Sub Print_Multiple_Sheets()
'
' Print_Multiple_Sheets Macro
' Macro recorded 17/11/2004 by Paul Ulcoq
'
Dim LabelText As Range, labeldoc As Document, i As Long, j As Long

Set LabelText = Selection.Range
LabelText.WholeStory 'select the text for the label
Set labeldoc = Documents.Add("c:\documents and
settings\Administrator\Application Data\Microsoft\Templates\7173.dot")
'retreive blank label document from templates
With labeldoc.Tables(1)
For i = 1 To 3 Step 2 'skip the space between labels.
For j = 1 To 5
.Cell(j, i).Range.FormattedText = LabelText 'insert formatted text
into each cell of the document
Next j
Next i
End With

Dim Message, Title, Default, MyValue
MyValue = InputBox("Enter the number of sheets to print", "Number of
Sheets", "1") 'input sheets to print
labeldoc.PrintOut Copies:=MyValue

'close the label document without saving
On Error GoTo errorHandler
labeldoc.Close SaveChanges:=wdDoNotSaveChanges
errorHandler:
If Err = 4198 Then MsgBox "Document was not closed"


End Sub


Thanks again

Recording macros is good for learning a bit syntax, but is not suitable for
your purpose.

I would use the Labels dialog and select the 7173 label and click the New
Document button to get a document containing and empty sheet of those
labels. Then I would save that document as a template in your user
templates folder with the name 7173.dot. You can then close it.

Now, assuming that you have the formatted text that you want on the label in
the active document, if you select that text, and then run a macro
containing the following code:

Dim LabelText As Range, labeldoc As Document, i As Long, j As Long
Set LabelText = Selection.Range
Set labeldoc = Documents.Add("c:\documents and
settings\[username]\Application Data\Microsoft\Templates\7173.dot")
With labeldoc.Tables(1)
For i = 1 To 3 Step 2 'skip the space between the labels.
For j = 1 To 5
.Cell(j, i).Range.FormattedText = LabelText
Next j
Next i
End With
Dim Message, Title, Default, MyValue
MyValue = InputBox("Enter the number of sheets to print", "Number of
Sheets", "1")
labeldoc.PrintOut Copies:=MyValue

While I have not actually tested this, it should do what you want.
--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

Hi Doug,
Thanks for the help.
Originally I I was printing 1 sheet at a time with text from a word
document. The text was formatted and I just swipe the text and it was
transfer to the label printing wizard with the formatting in tact.

When I created the macro the formatting was lost. The formatting
consisted
of the first part of the text being 10 pt bold Arial font and the secod
part
8 pt normal Arial font. With the macro it all came out as 10 pt normal
weight Times New Roman (I think).

The problem is that the macro doesn't swipe the text in the document, the
original text is permanent in the code. This presents the next problem.
That is I cant change the text for the label without re recording the
macro.

Anyway here is the code;

Sub Print_Multiple_Sheets()
'
' Print_Multiple_Sheets Macro
' Macro recorded 17/11/2004 by Paul Ulcoq
'
Dim Message, Title, Default, MyValue
Message = "Enter the number of sheets to print" ' Set prompt.
Title = "Number of Sheets" ' Set title.
Default = "1" ' Set default.
' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)


For x = 1 To MyValue

Selection.WholeStory
Application.MailingLabel.DefaultPrintBarCode = False
Application.MailingLabel.PrintOut Name:="L7173", Address:= _
"Frozen Cooked Whole Black Tiger Shrimp" & Chr(13) & _
"Packs per Carton: 5 packs" & Chr(13) & _
"Pieces per Carton: 5 x 32 pieces" & Chr(13) & "Packed Date:
17/12/2004" _
& Chr(13) & "Best Before: 17/12/2004" & Chr(13) & "Est. No. 1495" &
Chr( _
13) & _
"Ingredients: whole shrimp, Sodium acetate, Acetic acid, Fumaric
acid" & _
Chr(13) & "Mono-Sodium fumarate, Dextrin, Sodium L-glutimate,
Cellulose" & _
Chr(13) & "Corn Starch, Hydrolysed Animal Protien, Yeast Extract,
Salt", ExtractAddress:=False, LaserTray _
:=wdPrinterManualFeed, SingleLabel:=False,
PrintEPostageLabel:=False, _
Vertical:=False

Next x
End Sub

The text I would like to be able to change and have it flow through are
packed dates, Est Numbers and ingredients.

Thanks again and cheers