Hi All,
We are in the process of creating labels for our assets and we want to have a macro which willcreate labels for us based on the excel data that we have.
For ex, Assuming our excel sheet contains data as shown below:
Asset No. S/R No. Asset Type Asset Description
COMP/01856 2021032 Modem ASM 31 64kbps modem;G703
COMP/01857 9010430 Modem RockFell
COMP/01858 5000004 Modem ASM 31 64kbps modem;G703
COMP/01149 10010328 Switch Switch 443
COMP/01150 2000051220 Switch Switch 234
COMP/01151 2000011992 Switch Switch 234
COMP/01152 2000070523 Desktop ASM 31 64kbps modem;G703
COMP/01153 2000022917 Desktop ASM 31 64kbps modem;G703
COMP/02053 210241925 Switch D-link Des 24 Ports Switch
COMP/00363 HOP5115003912 Router CISCO XXX 10/100
COMP/02451 3102410088 Router CISCO 1/100 AAA
COMP/00377 JMX0441JD7T Router CISCO 1750 10/100
we want to allow the user to select, say the first five records, and then on click of a button create labels based on the selected data. The labels should preferable be copied to a word document.
I hope that i have made myself clearon whatwe want to achieve.
Thanks for any help.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
sajuks,
Here's a macro that I've just recorded in Word to produce labels using data from a specific Excel file. I suggest you print out my instructions (above) and then record your own Word macro to produce labels. Anyhow here's my macro.
Sub LabelMacro()
ActiveDocument.MailMerge.M
ActiveDocument.MailMerge.O
"C:\Documents and Settings\Patrick & Thuc-Nghi\My Documents\Patrick's\Excel files\Wedding\Antonia&Dave
, ConfirmConversions:=False,
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="",
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OL
, SQLStatement:="SELECT * FROM `Addresses$`", SQLStatement1:="", SubType _
:=wdMergeSubTypeAccess
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""Forenames"""
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""Surname"""
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""Address1"""
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""Address2"""
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""Address3"""
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""Town"""
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""County"""
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""Post_Code"""
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""Country"""
Selection.MoveUp Unit:=wdLine, Count:=2
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.MoveLeft Unit:=wdCharacter, Count:=6
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.TypeParagraph
Selection.MoveRight Unit:=wdCharacter, Count:=10
Selection.TypeParagraph
Selection.MoveRight Unit:=wdCharacter, Count:=10
Selection.TypeParagraph
Selection.MoveRight Unit:=wdCharacter, Count:=12
Selection.MoveLeft Unit:=wdCharacter, Count:=2
Selection.TypeParagraph
Selection.MoveRight Unit:=wdCharacter, Count:=7
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.TypeParagraph
Selection.MoveRight Unit:=wdCharacter, Count:=20
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.TypeParagraph
Selection.MoveUp Unit:=wdLine, Count:=6
Selection.TypeText Text:=" "
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.MoveLeft Unit:=wdCharacter, Count:=2
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.TypeText Text:=" "
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.MoveLeft Unit:=wdCharacter, Count:=2
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.TypeText Text:=" "
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.TypeText Text:=" "
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.TypeText Text:=" "
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.TypeText Text:=" "
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.TypeText Text:=" "
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=9
Selection.MoveLeft Unit:=wdCharacter, Count:=2
Selection.TypeText Text:=" "
Selection.MoveUp Unit:=wdLine, Count:=5
Selection.MoveLeft Unit:=wdCharacter, Count:=10
Selection.MoveDown Unit:=wdLine, Count:=7, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1
WordBasic.MailMergePropaga
ActiveWindow.ActivePane.Ve
WordBasic.MailMergePropaga
WordBasic.MailMergePropaga
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
ActiveWindow.ActivePane.La
ActiveWindow.ActivePane.Ve
Application.PrintOut FileName:="", Range:=wdPrintCurrentPage,
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages,
ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0
End Sub
Hope that helps.
Patrick
Business Accounts
Answer for Membership
by: patrickabPosted on 2007-03-24 at 03:03:21ID: 18784786
sajuks,
MAILMERGE
Yes, this can be done very easily as a Word mailmerge and using Excel as the database. The trick is to produce the output as labels in MSWord. This allows the repeating of the same pattern of data on one page. If you are not acquainted with mailmerge labels it can take quite a bit of getting used to as it is not intuitive. In fact MSWord appears to make it hard but after you have done it a few times it does get easier.
The stages are:
1. Prepare Excel database with column headings such as Title, Givenname, Familyname, Address1, Address2 and so on. Save and close the file.
2. In MSWord press the NewBlankDocument button. Select Tools/Letters and Mailings/MailMergeWizard/ and in the Taskpane select Labels. If the TaskPane is not visible go to View and tick Task Pane.
3. In the Task Pane press the Starting Document link
4. Press the LabelOptions link in the Task Pane
5. Select the label size that will allow you get all the data of one person onto one label - press OK
6. Press SelectRecipients/Browse and find the Excel data file. If you get it right you will see a dialogue box entitled Mail Merge Recipients - press OK
7. Press the link Arrange your labels in the Task Pane and in the next Pane select More Items
8. Making sure the cursor is in the first blank label, select and insert every field that you want in the label. Having done that press Cancel.
9. Re-arrange the fields in the FIRST label only, making sure that every field is still surrounded with <<fieldx>>. Format that one label as required.
10. In the Task Pane under the heading Replicate Labels press the Update labels button (it doesn't look like a conventional button). All the labels will then be filled with the fields as laid out in the first label. Ignore the <<Next Record>> entries and DO NOT edit any of the labels.
11. In the Task Pane press the Preview Labels link
12. If the labels look OK then you can press the Complete the Merge link in the Task Pane and then press the Edit Individual Labels link to see the result. It's at that stage you can edit the labels if you want - as it's a separate document. Save it or print it.
You will find it easier if you have the MailMerge Toolbar visible for this whole task - View/Toolbars/MailMerge as you will then be able to do most stages witout using the Task Pane.
I know it's many stages but after a bit you will get used to it. You can of course save the set up and re-use it so that you only need to create the label merge once. I personally find that difficult so I just go through the routine each time - about 2 minutes - that's all.
Hope that helps
Patrick