labels using macros

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vandy - 24 May 2007 18:17 GMT

Hi All,

I have a very peculiar requirement. There is a set template in word which
has details of drwg no, desc, item qty etc. which I have to pick up and
arrange it as labels:

Project Desc :
Item Qty:
Components:

The problem they are in a set format . I have to create a macro which when
used can create the label tags automatically by any user filling the form.

Any help in the right direction will be appreciated. Right now I dont have
any clue.

Reply to this Message

Doug Robbins - Word MVP - 24 May 2007 19:03 GMT

I am not sure what it is that makes your requirement peculiar. How is the
information arranged in this so-called template? Is the information already
in this template or is it provided by some other process or what?

Tell us all and everything about it and we can probably help.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Hi All,
>
[quoted text clipped - 11 lines]
> Any help in the right direction will be appreciated. Right now I dont have
> any clue.

Reply to this Message

vandy - 24 May 2007 19:25 GMT

Hello Doug,

I will try to explain my requirement better. I have a prefixed template
which has sets of values which needs to be extrated and product tags or
labels created.

Since the data is not arranged in any fixed order when i do mail merge it
gives me all the records as lines.

For eg.

Project Desc: RR 1 Components: AB2

Dwg No. 21R909

Then I have a table listing data

Item no Oper no Dwg qty Total Qty

005 34 4 4

006 44 3 5

007 343a 5 4

I am able to get all the details of the above 2 rows like proj des. ,
components etc. i need to capture all the numbers too. how to mail merge.

I have done it with data logically arranged as rows in a table and using XL
. Here data is all scattered and data has to be captured from different
sections and printed off as a product tag by any user filling up the
instruction sheet.

I hope I am making sense. I do appreciate your help and would give more
details if required.

> I am not sure what it is that makes your requirement peculiar. How is the
> information arranged in this so-called template? Is the information already
[quoted text clipped - 17 lines]
> > Any help in the right direction will be appreciated. Right now I dont have
> > any clue.

Reply to this Message

Doug Robbins - Word MVP - 24 May 2007 22:18 GMT

Question 1

So, this:

Project Desc: RR 1 Components: AB2

Dwg No. 21R909

Then I have a table listing data

Item no Oper no Dwg qty Total Qty

005 34 4 4

006 44 3 5

007 343a 5 4

is what you are starting with?

Question 2

What do you want to produce? It would be best if you answered that by
showing examples using all relevant parts of the above starting data. For
example, if it is mutliple labels, show what is to be on each label. But if
that is not representative enough, give a better example of what you are
starting with and the result that you want based on that starting data.

Question 3

Is that above starting data produced from some other source that might
possible be a better source of data for producing the labels? It seems to
me like it is probably a report formatted by some other system and you could
be better off starting with the raw data.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Hello Doug,
>
[quoted text clipped - 60 lines]
>> > have
>> > any clue.

Reply to this Message

vandy - 25 May 2007 14:21 GMT

Hi Doug,
I apologise if i am not making myself clear. I have attempted to explain my
source file, target output. I am not very good with Macros and not good at VB
too. If you could point me in the right direction it would be great. I find
this forum very helpful in solving my issues.

Answer to your questions:
Question 3

Is that above starting data produced from some other source that might
possible be a better source of data for producing the labels? It seems to
me like it is probably a report formatted by some other system and you could
be better off starting with the raw data.

Ans: The data source is a word document.

Question 1 & 2

I will explain with an apt example and define what the out put label tag
should read.

PLATFORM #4    Prepared by:     Date:    Proj. #: Accepted

 Accepted as noted

 Rejected
Date:
Initials:
    XYZ    2006/5/2    1588

Project Description:    Component:     MPI #:    MPI Rev #:
RETUBE PLATFORM ASSEMBLY    BRACE CLEVIS    26C    0
Drawing #:     Rev. #:    Assembly Drawing #:     Rev. #:
21RT-87270-4034-01-DD-D    0    21RT-87270-4033-01-GA-E    0
ItemNo.    OperNo.    WitnessPoints Instruction StockNo. Dwg.Qty.    TotalQty.
complete by date inspect / date

8 005    Release material; CSA G40.21-92 GRADE 44W 3 3

8 A015    Sawcut material as required         4    4

THIS IS THE DOCUMENT I START WITH . AN INSPECTION SHEET WHICH HAS DATA
FILLED WITH PREDEFINED FORMATING CELLS AND MERGED CELLS and table formats.

OUTPUT TO BE GENERATED - PRODUCT WORK ID TAGS

OUTPUT LABEL ID: 01

PROJECT: 1588

COMPONENT: BRACE CLEVIS

DWG. REF: 21RT-87270-4034-01-DD-D

PROJ DESC.: RETUBE PLATFORM ASSEMBLY

DWG. ITEM NO. Release material; CSA G40.21-92 GRADE 44W

OPER #- 005

DWG. QTY: 3

OUTPUT LABEL ID: 02

PROJECT: 1588

COMPONENT: BRACE CLEVIS

DWG. REF: 21RT-87270-4034-01-DD-D

PROJ DESC.: RETUBE PLATFORM ASSEMBLY

DWG. ITEM NO. Sawcut material as required

OPER# : A015

DWG. QTY: 4

PROBLEM: WHEN I RUN A LABEL MERGE SINCE THE FIRST 2 ROWS FIELDS LIKE PROJECT
DESC, COMPONENT NO, MPI, DWG # ARE IN A TABLE FORM I AM ABLE TO GET THAT
INFORMATION BUT THE OTHER ROWS WHICH CONTAIN OPER # , ITEM DESC, ITEM QTY,
DWG QTY WHICH DIFFER FROM ONE ROW TO ANOTHER I AM FINDING IT DIFFICULT TO
CAPTURE.

I HAVE THE OPTION OF REDESIGNING THE FORM TO OPTION THE DESIRED FIELD ORDER
IN THE DATA SOURCE. I HAVE TO CREATE A LABEL MERGE MACRO SUCH THAT ONCE
ANYONE FILLS IN THE DETAILS OF THE REPORT AND ADDS IN OTHER DETAILS ,
SELECTED FIELDS AND ITS VALUE SHOULD BE PRINTED OUT AS SHOWN IN THE LABEL
PRODUCT ID TAG.

Thank you for patiently reading this data. I would appreciate your help as
to how to proceed.

> Question 1
>
[quoted text clipped - 95 lines]
> >> > have
> >> > any clue.

Reply to this Message

vandy - 25 May 2007 15:13 GMT

HI Doug,

Here I am again. I found that by using a creating a customized address list
of the data I need i can print the desired format. This datasource list gets
created in access. Only problem we are talking about tons of data and tons of
reports.

Is there any way where the user is prompted to enter data based on which he
creates his Prouduct ID tag. Ideally once the inspection report is filled the
label merge should be automatic, pick up selective feilds and create labels
as listed in the label tag.

I hope I not deviating from the desired output.

> Hi Doug,
> I apologise if i am not making myself clear. I have attempted to explain my
[quoted text clipped - 188 lines]
> > >> > have
> > >> > any clue.

Reply to this Message

Doug Robbins - Word MVP - 25 May 2007 16:04 GMT

I think that you should take a look at
http://www.mousetrax.com/techpage.html#autoforms

Especially the part about storing your information in a database.

If I were you, that is definitely what I would be doing.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> HI Doug,
>
[quoted text clipped - 236 lines]
>> > >> > have
>> > >> > any clue.

Reply to this Message