Solved: How do I get rid of that pesky last newline character?

[BillHamilton]

Hi,

I have some Word VBA code that creates a label document and loops round writing data onto each label. I'm doing a series of TypeText instructions to write lines on the labels, but the trailing newline character inserted by 'the system' on the last line is effectively taking up an extra line on the label which I shall need in the next incarnation of this system. I can go into each label of the completed document and delete it manually, but there are hundreds and that's not an option. I want the code not to put it in in the first place. Here's the code fragment that inserts the text onto each label (after selecting the label cell in the table and doing a With for it):

              .TypeText Text:=stTitle1 & vbCr
              .TypeText Text:=stTitle2 & vbCr
              .TypeText Text:=stTeam & vbCr
              .TypeText Text:="Division  " & d% & "       " & d% & vbCr
              .TypeText Text:="Team      " & t% & "       " & t% & vbCr
              .TypeText Text:="Round     " & r% & "       " & r% & vbCr
              .TypeText Text:="Last date for shooting: " & stRoundDates(r%)

It then selects the next label, gets new data into the variables, writes it out and repeats until the end.

Note that the last TypeText does not have a vbCr at the end. However, one does appear in the finished labels. How do I suppress it?

I'm using Word 2007 under Win7 (Home). The data are extracted from an Excel workbook. These labels are to be stuck onto targets for a shooting competition.

I'm much more used to writing in Excel VBA and this is my first foray into Word VBA, so maybe there's some functionality I am not aware of. Hope someone can help. Please.

Bill

[macropod]

Hi Bill,

So how come you don't simply set this up as a label mailmerge in Word? No code required.

Re the "vbCr at the end":
Are you referring to the paragraph that follows the table? If so, it cannot be deleted. You could minimise it's effects by formatting it in a 1pt font with 0 before & after spacing.

[BillHamilton]

Hi. Thanks for the response.

I think this is far too complex for a mail merge. The values of the variable StTeam is read in turn from a list in a worksheet and the originating values of d%, t% and r% are taken from cells in that workbook. This code in the Word doc uses those values as loop counters to produce a series of labels for each team member (m%, not used in the snippet) in each team (t%) in each round (r%) in each division (d%). So there might be ten rounds for six divisions of eight teams, each with five team members. The array of dates in stRoundDates(r%) is read in from a table in the current Word doc earlier in the code. Next year there could be only 5 divisions and that's very easy to change in the workbook.

I don't think a mail merge could really handle all that (stand to be corrected though). Anyway I'm doing this for a non-computer person who can just about set up the teams in the workbook and enter the dates in the table, and if I only give him a doc file to open, insert a few dates into a table and press one button to make the labels it will get to the limits of his expertise. Asking him to do a mail merge would be a few steps too far, especially as I haven't a clue about how to do it myself.

Regarding the vbCr at the end: I guess I do mean what you say. But I am mighty confused. If I open the label doc and turn on Paragraph Marks, I can see that there is a para marker at the end of each line, including the last one where there is NO vbCr output by the TypeText command. I opened the label doc, took an empty label cell and recorded the macro below. I inserted an 'a' on each of four lines, following each with a Return except the last one, and then tabbed to the next cell. There is no para marker after the last line. I can replay that macro and it repeats exactly what I typed. No newline after the last 'a'.

 
Selection.TypeText Text:="a"
Selection.TypeParagraph
Selection.TypeText Text:="a"
Selection.TypeParagraph 
Selection.TypeText Text:="a"
Selection.TypeParagraph
Selection.TypeText Text:="a"
Selection.MoveRight Unit:=wdCell

I run my original macro and a newline is magically inserted at the end of the last line. Maybe you can tell me what the difference between the two macros is. Both of them insert lines of text followed by a newline except for the last one. My original code produces a last line with a newline appended, the recorded code does not.

As I said, I'm a bit confused by that apparent anomaly.

[BillHamilton]

I'm even more confused now.

I commented out the TypeText for the last line (the one that prints the "Last date for shooting"), removed the vbCr from the line before ("Round") and reran the macro so it produced one line less with no vbCr on the (new) last line.

It worked. No newline on the last line, which is exactly what I want, except it's the wrong last line. Why should the number of lines being printed make any difference?

              .TypeText Text:=stTitle1 & vbCr
              .TypeText Text:=stTitle2 & vbCr
              .TypeText Text:=stTeam & vbCr
              .TypeText Text:="Division  " & d% & "       " & d% & vbCr
              .TypeText Text:="Team      " & t% & "       " & t% & vbCr
              .TypeText Text:="Round     " & r% & "       " & r%
''''              .TypeText Text:="Last date for shooting: " & stRoundDates(r%)

I'm using Avery J8160 labels, which at the 10pt Arial size I want should take 9 lines.

[Frosty]

Well, obviously the number of lines being printed doesn't make a difference. It seems most likely that the value
stRoundDates(r%) has a vbcr at the end of it.

As you have only shown a very small bit of your actual code, it is impossible to actually answer your question. But I think you're close to solving it on your own.

Next step: see how you actually build your array.

Also, as a small point-- you have a very strange variable naming convention for VBA. It is more helpful (especially when posting code snippets) to give naming conventions to indicate what kind of variables you're working with. You are using Option Explicit, right? And you aren't doing
Dim r%
but rather,
Dim r% As Integer
or some such, right?

[BillHamilton]

Frosty, you are a GENIUS!

First, though, to answer your picky points. I'm kinda old school and grew up in the times when Basic (ye olde original Basic, for the venerable BBC Model B (give it a Google if you don't know it) in the first instance, then Mr Gates' version which was basically (sorry) similar) didn't require variables to be explicitly typed by specifying 'integer' or 'string' or anything else. In fact that wasn't possible in any case as variables did not need to be declared. Any variables with a '%' suffix were by definition integers and those with a '$' suffix were string variables. VBA carries on that convention for backwards compatibility or to keep old farts like me happy, or something, so my loop counters here are all defined as 'Dim d%' etc. No need to add 'as Integer' as that would be redundant. Saves typing all round. I use 'em all the time. Apologies if anyone was confused.

Sorry, you probably knew all that.

And, yes, I do use Option Explicit. Doesn't everyone?

Anyway, to the matter. It has nothing to do with my TypeTexts and everything to do with the date strings as you suggest. It turns out that the dates the code extracts from the table in the Word doc do have a newline incorporated into the string. It's not obvious, and in fact they are invisible. My original code to extract them just did a Mid function on the values to lose the trailing character which was some unprintable thing that debug showed as a little square. I changed the code to use the Clean method on the date string in each of the cells instead and bingo! No more newlines. Debug showed the value exactly the same with both methods. What's a boy to think?

I often tell myself when looking for a bug that if it's not where I'm looking then it must be somewhere else. Thank you for pointing me at the somewhere else. I'd never have thought of that (well, maybe after a century or so).

Case closed I think, and thanks for the ideas.

Regards,

Bill

[Paul_Hossler]

Bill, it's been an interesting thread. I always enjoy Frosty's very explainatory posts.

Don't forget to use the 'Thread Tools' menu above your post #1 to mark it 'Solved'

Paul