#  Other Applications & Softwares  > Word Formatting & General >  >  Merge Excel2Word - Labels

## EXLent

So I'm trying to print label from Excel using the mail merge.
I can do it manually.
I record macro as I was doing it.
I copy the code and put it in the source so that when open application it would do automatically but it doesn't.
Is it possible?

----------


## EXLent

Reasked my question.

----------


## macropod

You usually don't need a macro for a mailmerge. Do you really understand what you're doing? Have you inserted any mailmerge fields into the first label your document? Did you then tell Word to update the labels?

See:http://support.microsoft.com/kb/294684

----------


## EXLent

I was able to make it work.  Purpose of enabling macro is so that with a push of a button it does it automatically.
I had posted in Excel Thread about getting the labels labeled and satisfied with the help.

So what I did was record macro of what I did manually in Words merging labels from excel.



```
Please Login or Register  to view this content.
```


When I run the code, it printed label per page instead of all labels in a page.  Somehow it didn't get record of the "update merge" part in Words.
Any help with this code or different is very much apppreciated.

----------


## macropod

I suggest you have a look at both your mailmerge main document to see that you have it configured properly and, before printing, check the output document too to make sure it contains what you expect. I suspect you haven't used the 'update labels' function to propagate the label data to all labels on the page.

If you configure the mailmerge main document properly, much of your code becomes unnecessary. Indeed, if you're running this from Word, I can't see the point of a macro as all it would save the user doing is clicking on the Finish & Merge > Print Documents buttons, since it's evident you're not even saving the mailmerge output. In that respect, your code takes a round-about route. Why not simply merge to printer and skip the document generation?

----------


## EXLent

As I mentioned. That code was generated by me recording the macro.
Yes I DID 'update labels' and successfully printed all the labels in one page.

Somehow the macro did not record the update labels part.  How can I add that manually?

I didn't skip the document generation?  I just followed Microsoft site how to merge.

Only if I can click on a button and it does what I just did manually successfully.

----------


## macropod

As I said before, all your code achieves is reducing the number of clicks to merge labels to the printer - and that's by only one click - then exit Word - one more click!!

If you set the document up as a mailmerge main document beforehand - with the propagated labels - all of this code:



```
Please Login or Register  to view this content.
```


would be unnecessary. So all you've achieved so far is substituting a macro warning for an SQL prompt.

Similarly, all of this code:



```
Please Login or Register  to view this content.
```


would be unnecessary if you sent the output direct to the printer. You evidently did not record doing so, since your code first creates a new document, then sends it to the printer as a separate action.

Accordingly, with the document set up beforehand for a mailmerge, the code could be reduced to:



```
Please Login or Register  to view this content.
```


With all that, you save one click compared to doing it manually, as you'll now have both an SQL prompt and a macro warning.

Having said the above, if you're wedded to having the macro turn a normal labels document into a mailmerge document, then execute a merge to print, you could use something like:



```
Please Login or Register  to view this content.
```


And all you've achieved compared to executing a mailmerge to printer manually from a mailmerge main document then closing it is two clicks!!

*Note*: You'll need to save the document before running the macro, otherwise the macro won't be saved with it. For testing purposes, you might also want to disable the lines '.Close SaveChanges = False' and 'Application.Quit' and change the line '.Destination = wdSendToPrinter' to '.Destination = wdSendToNewDocument'.

FWIW, the macro recorder cannot record everything you can do through the GUI.

----------


## EXLent

That's my issue, I don't know how to do it.  All I can think of record macro of me doing it.  If there's an easy way, please enlight me.

label.xlsm

----------


## macropod

If the issue is the 'update labels' part, it's in the code I posted ... If the issue is that you don't know how to set up a mailmerge document without using the macro recorder, simply don't use the recorder ... After all, all you end up saving is at best two clicks.

Update: I see you have an attachment to your post. Evidently all you're trying to do is to generate some numbered labels in Word. You don't need either Excel or a mailmerge for that. A Word macro can do the lot on its own.

----------


## EXLent

I know how to do the mailmerge manually(click by click).
I dont know how to code it to do it automatically (reason why record of me doing it).
It can be couple, triple click if it's just a button.  It's not me, I'm trying to make it easy for others. Ex: retires.

by the result of the code I recorded, i think it's the "update labels" because like I mentioned before, it printed one on each page, instead of all on one page.

----------


## macropod

As mentioned in the update to my previous post, you don't need mailmerge for this at all. Word can do it on it's own. Or, if you're more comfortable using Excel, have you considered formatting your Excel workbook with the label dimensions then simply using your code to update that and print it? Seems to me you're making this a whole lot harder than it needs to be.

----------


## EXLent

Now I'm understanding what you are saying.




> Word can do it on it's own.



I have not done much on Word coding(userform and buttons) stuff.
So I just check, i shouldov check before. Yes I can do userform in Word too.

Now, I'll just have to start over and toss my excel part.

----------


## macropod

See attached.

I imported your userform into Word and made a few changes.

Note: the code will work with however many labels/page you might have and, if the selected # of labels on the userform doesn't fill a whole sheet, the whole sheet will be filled anyway (so as to not leave empty labels). There is also no need for the user to input 5-digit numbers.

----------


## EXLent

Now we're talking.  This is just what i'm looking for.  Short and simple. Well, not really simple for me.
I'm looking at the code I dont see any label template selected.  

How can I use label avery 5267, that's the reason for max of 80

I'm also trying to understand the code.  How it actually knows how many to print.

**Oh i see it. Loop command there.

----------


## EXLent

nevermind, i think that was template 5267, i dont know where I counted 80 labels per page.

----------


## macropod

Nothing needs to be selected! All you should need to do to make the code work with whatever label stationery you're using is to replace the table in the document I posted with the appropriate label table (e.g. Avery 5267) and, perhaps, change the paper size from A4 to Letter.

The code really doesn't care how many labels per page your stationery uses - it'll simply spit out however many pages are needed to account for the input #. That also means you can use the same code on multiple label templates. Given that, you could also make the maximum # whatever you're comfortable with.

----------


## EXLent

Ok, how can I not have it print the whole page?  That was the purpose of having textbox2.



```
Please Login or Register  to view this content.
```


1 + whatever's in textbox2(1finish), but it if text box2 is 10 and it prints 80, this code is not workign right?





> See attached.
> 
> I imported your userform into Word and made a few changes.
> 
> Note: the code will work with however many labels/page you might have and, if the selected # of labels on the userform doesn't fill a whole sheet, the whole sheet will be filled anyway (so as to not leave empty labels). There is also no need for the user to input 5-digit numbers.

----------


## macropod

The macro never produces a page #2 - it simply reuses the same page over & over with new numbers.

As I said before, I wrote the code so that it would always generate an entire page of numbered labels. It didn't seem to make much sense to me to number only a few on a page - especially since it'd be difficult for Word to work out where to start numbering the remaining labels later on. But, if that's what you want, simply insert:
If lStart = lCount Then Exit For
after:
lStart = lStart + 1

----------


## EXLent

lol sorry.  "page2" was refer to this thread.  I couln't get to page two to see your response until I reply and saw my own and with your previously.

Thanks I'll try that

----------


## EXLent

how would I make the font bigger to fit the label? 
do I do it via coding or setting in the page itself.

----------


## macropod

You change the font size on the page itself - the macro contains no font-related code.

----------


## EXLent

Thanks, got that working.
What if I want to save the last number printed?

So, I created textbox3.
Can I code something like this



```
Please Login or Register  to view this content.
```


here



```

```

----------


## macropod

If you want to save the last # printed, you'd need to store that in the document using, say, a custom document property. Your macro code could then read that and populate TextBox1 with that to get the starting # for the next set of labels. See attached example, which uses a custom document property named 'Number'.

----------


## EXLent

That's pretty close.  It gave me the next number instead of the last number printed.
Could i do this:



```
Please Login or Register  to view this content.
```

----------


## EXLent

Ok, that -1 works.

----------


## macropod

I'd have thought you'd want the next label to print to be 1 more than the last one printed, hence the coding (which is for updating TextBox1).

BTW, if you replace 'Unload Me' with 'UserForm_Initialize' in the 'CommandButton1_Click' sub, it remains on screen and you don't need TextBox3.

----------


## EXLent

cool, thanks again, Kudos

----------


## EXLent

Ok, so the label layout did not work as intended so I start over with Avery 5167 template and try to copy over the Userform and code.  When I run it I got error.  Is there a correct way of copying it other than copy and paste?

Or can I change template that is already chosen?

Which ever is easier.

----------


## macropod

> I start over with Avery 5167 template and try to copy over the Userform and code.  When I run it I got error.



What was the error? Did you also re-create the custom document property named 'Number' in the new document? That has to be done manually.

As for the layout, it would probably be easier to change the labels than to transfer the form, code, etc. to a new document.

----------


## EXLent

The error is pictured
error.png

Debug show the blinker at:



```
Please Login or Register  to view this content.
```


before:



```
Please Login or Register  to view this content.
```


It was the label that you had setup for me, not sure what that was.  I tried to see if I can change the format/template, but unsuccessful.

----------


## macropod

Are you sure you copied & pasted all the code, exactly as it appeared in the original?

----------


## macropod

Are you sure you copied & pasted all the code, exactly as it appeared in the original? Or, better still, that you exported the code module & form from the source document then imported them into the target document?

----------


## EXLent

let me try export and import

----------


## EXLent

No, ex/import didn't work.
still got error
error.png

----------


## EXLent

wow, i just noticed there's a different in KB, from 57 to 75kB, hmmmm.

----------


## macropod

You haven't re-created the custom document property named 'Number' in the new document!!

----------


## EXLent

sorry I dont understand

----------


## macropod

Go to File>Info>Properties>Advanced Properties>Custom. There, input:
Name: Number
Type: Number
Value: 0
Click 'Add', then OK.

----------


## EXLent

Ok, i was actually there but wasn't sure what to change.
So since I'm using a template, is that what I need to change to number?
if so, it wont' let me, but it let me add.
number.jpg

----------


## macropod

Your image already shows a custom document property named 'Number', so no further action is required for that document.

----------


## EXLent

No, i added it after you told me.

----------


## macropod

OK, now that you've added it, the macro should run correctly.

----------


## EXLent

I think the best way is to change the template which I dotn know how.  I went into the Create-labeling and change, but options are to print or cancel.  I noticed current is normal template but I want the avery 5167/5267.  

For some reason, i'm going the wrong direction and getting frustrated.

I'd tried creating avery template and copy over the code update property with numbers and it works.  Issue is it has addresses with 3 lines.  I just want 1 line.  Instead of addresses, I want a series of number.

The first one was coded good, but wrong template.

----------


## macropod

You shouldn't need a Word template - simply create a new Avery 5167/5267 labels document and use that. Once you add the code and the custom document property to it, everything should work fine. It really doesn't matter that the labels are designed for 3-line addresses - simply erase the contents and format the label layouts the way you want them to appear once the numbers have been applied.

The reason for not using a template is that the code updates the 'Number' custom document property. If you then save the document, you should get the new starting number next time you open it; with a template, that wouldn't happen.

----------


## EXLent

progress made.
1. Open new avery template
2. Add Number to property
3. Import all the code
Text/Number lined up property except little issue see picture.
IMG_20131104_212644.jpg
One on left is written from excel/words
On right side is what i'm wokring on.
As you can see the number 2 is in the second box which should be empty.
How can I make it skip that or disable that box?

----------


## macropod

Because the labels only occur in every 2nd cell, you'll need to change the 'CommandButton1_Click' code to:



```
Please Login or Register  to view this content.
```


I also note that your Excel sheet formats the numbers with 6 digits, whereas Word is using 5. If needed, change 'Format(lStart, "00000")' to suit.

----------


## EXLent

hmmm, got an error with editing.  copy the whole thing still error  :Frown: 

edited: Copy the code from here instead of email.  works

Now i have to study the code how it works.

----------


## EXLent

Can I make it populate before actually printing?
Incase I want to double check?
I tried to move 


```
Please Login or Register  to view this content.
```


but wasn't successful.

----------


## macropod

> Can I make it populate before actually printing?
> Incase I want to double check?



That's what it already does. The reason you can't see what's happening is because it's all over in much less than a second. If you want to test, I'd suggest using a pdf 'printer' or, perhaps, a message box before the 'ThisDocument.PrintOut' line.

----------


## EXLent

Actually after the ' .PrintOut' works.

I'd also added a button(over text) to activate Userform1.  It works but it disappear after finished printing.  
I guess when it clears the page, it clears my button too.
How can I make it stays even after I saved it?

----------


## macropod

> Actually after the ' .PrintOut' works.



No, it doesn't, because that displays the content _after_ the command to print it has been sent, whereas you wanted to check _before_ printing.




> I'd also added a button(over text) to activate Userform1.  It works but it disappear after finished printing.  
> I guess when it clears the page, it clears my button too.
> How can I make it stays even after I saved it?



What do you want to keep - the button or the label data?

----------


## EXLent

button, i created a button floating above text.

----------


## macropod

You'll need to post a copy of the document with the button (and the related code) so I can see what's going on with it.

----------


## EXLent

Here's the file with button for activating macro. words 2010.

----------


## macropod

The problem is that your 'Print Again' button is anchored to a table cell. Consequently, it gets deleted when the cells are cleared. Drag the anchor to the empty paragraph that follows the table and everything should be fine.

FWIW, you document also has an unnecessary VBE reference to 'rtnad.dotx', which prevents the code running on my system.

----------


## EXLent

Where?  I thought I anchored it above the table cell.  
The whole page is used by template 5167.  You mean the next page?

Sorry I dont know what that rtnad.dotx is.  Can i just get rid of it?

----------


## macropod

See attached update - relocated anchor and reference gone.

----------


## EXLent

still dont understand.  visually look the same. 
urgeeee

----------


## macropod

> still dont understand.  visually look the same.



Of course they _look_ the same, but check where the button is anchored.

----------


## EXLent

is it the absolute position that you change, cuz thats the only thing i see differ.?

----------


## macropod

No, all I changed is the anchor location. Right-click on the button and see where the anchor is - you'll need to scroll all the way down to the bottom of the page and have Word set up to display the formatting marks.

----------


## EXLent

Oh my goodness, you are so cool.  I mean cool cool, where do you get the patience?
Thank you for that.

Let's ask you stupid question.
Click the button, assuming in develope mode. Do you mean right click to go to Format Control or Properties?  Otherwise, just clicking it does nothing.


You should start thread "Course for noobies"
Man I would follow every day.

----------


## macropod

If you're in design mode, clicking the button won't do any more than give access to its properties and code (including the ability to see where it's anchored). Otherwise, when you're not in design mode, right-clicking on the button will still let you see where it's anchored.

----------


## EXLent

here is what i see anchored.jpg in develope mode,
right click does nothing in regular mode.

----------


## macropod

There is no 'develope' mode - only a 'design' mode. Your image tells me you also don't have Word configured to display the formatting marks. Click on the ¶ symbol on the Home tab to display them. Right-clicking when you're not in design mode isn't meant to 'do' anything; it merely lets you see where the anchor is - if you follow the instructions.

----------


## EXLent

here is a video capture of me trying to right click



```
Please Login or Register  to view this content.
```

----------


## macropod

You'd have a lot more success if you turned on the formatting display and followed the instructions in post 61 (http://www.excelforum.com/word-forma...ml#post3465180) instead of making videos ...

----------


## EXLent

> Right-click on the button and see where the anchor is



I did, nothing appears only in design mode options apppear.





> Click on the ¶ symbol on the Home tab to display them



I did(with/without designmode), all the cell fill with a sign that look likes aiming gun site as I tried to show on video

video is to show I did as directed, unless I misunderstood somwhere.

----------


## macropod

But you never did scroll down to the bottom of the document with the formatting marks displayed and the button selected ...

----------


## EXLent

ank.jpg

Is this the symbol you are talking bout?
When I click on the ¶ symbol and saw all those populate in the cell, I thought it was messed up.
so what you did was drag the anchor down there?

----------


## macropod

Yep, that's all it took to stop it being deleted. Compare that against your original.

----------


## EXLent

Yep, I also add it to my version and tested it works.  

Sorry, I could tell you almost lost it and log off for the night.

Thanks for not giving up on me.

----------


## macropod

> Sorry, I could tell you almost lost it and log off for the night.



??? It's only 3:20pm here ...

----------


## EXLent

10:30pm here.  
Anyway, thank you again.
I think enough of coding for the night.  
I think I pop'ed all the zits on face working on this.

----------


## EXLent

Ok, so user doesn't want the button to be shown when printed.
I added the document code



```
Please Login or Register  to view this content.
```


No luck.



```
Please Login or Register  to view this content.
```


No luck.

Does it only works on excel or words too?

----------

