Solved: Breaking vbNewLine - Breaks - Too Many Continuous Lines

[IcePirates]

Hello,

This topic, somewhat relates to my previous thread located here
http://www.vbaexpress.com/forum/showthread.php?t=24075

Ive included coding below, and what Im trying to do is add & vbNewLine & _ after my last line where it says
"Sincerely, " & wks.Cells(ActiveCell.Row, 26)
And the error the error Im getting is
"Too many continuous lines"

Does any one know why that might be caused? I still need to add another
10 lines.

Sub TransferData()
'This macro transfers the data range to a Word Document
'
'Constants:
'docFullName = The full name of an already existing Word document
'
'Variables:
'doc = The Word document (assumed to be empty)
'i = A counter (for rows)
'j = A counter (for columns)
'tbl = A Word table
'wdRng = A Word range (the first paragraph of doc)
'wks = The worksheet "data" that contains the data range
'
'Const docFullName = "C:\OLE Automation\Word.doc" '
Dim doc As Object
Dim i As Long
Dim j As Long
Dim tbl As Object
Dim wdApp As Object 'Only if you require a new document each time
Dim wdRng As Object
Dim wks As Worksheet
'Assing Word objects 'Only if you require a new document each time
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
Set doc = wdApp.Documents.Add
'Assign variables and objects
Set wdRng = doc.Paragraphs(1).Range
Set wks = ThisWorkbook.Worksheets("Data")

'Transfer the data

If Sheets("Data").ComboBox1.Value = "General-Transmittal" Then

wdRng = " " & wks.Cells(ActiveCell.Row, 1) _
& vbNewLine & _
" Delivered Via Courier" & wks.Cells(ActiveCell.Row, 26) _
& vbNewLine & _
" Sale File # " & wks.Cells(ActiveCell.Row, 9) _
& vbNewLine & _
" " & wks.Cells(ActiveCell.Row, 2) _
& vbNewLine & _
" " & wks.Cells(ActiveCell.Row, 3) _
& vbNewLine & _
" " & wks.Cells(ActiveCell.Row, 4) _
& vbNewLine & _
" " & wks.Cells(ActiveCell.Row, 5) _
& vbNewLine & vbNewLine & vbNewLine & _
"Attention: " & wks.Cells(ActiveCell.Row, 6) _
& vbNewLine & vbNewLine & vbNewLine & _
"Enclosed for your retention is the following file information from EnCana Oil/Gas Partnership: " & wks.Cells(ActiveCell.Row, 26) _
& vbNewLine & _
" " & wks.Cells(ActiveCell.Row, 17) _
& vbNewLine & vbNewLine & vbNewLine & vbNewLine & vbNewLine & _
"Please acknowledge receipt by signing, dating and returning one copy of this letter to the writer " & wks.Cells(ActiveCell.Row, 26) _
& vbNewLine & vbNewLine & _
"Should you have any concerns please don't hesitate to contact us." & wks.Cells(ActiveCell.Row, 28) _
& vbNewLine & vbNewLine & _
"Sincerely, " & wks.Cells(ActiveCell.Row, 26)
End If

[Bob Phillips]

What it says on the box, too many line continuations.

Reduce them, for instance, change



wdRng = " " & wks.Cells(ActiveCell.Row, 1) _
& vbNewLine & _
" Delivered Via Courier" & wks.Cells(ActiveCell.Row, 26) _
& vbNewLine & _


to



wdRng = " " & wks.Cells(ActiveCell.Row, 1) & vbNewLine & Space(106) & _
"Delivered Via Courier" & wks.Cells(ActiveCell.Row, 26) & vbNewLine & vbNewLine & _


and so on

[IcePirates]

Well see the thing is, I can do that...But the problem is I need all the line spaces as shown in my code, because the "Deliveried Via Courier" part and the "Sale File#" part are way on the right handside of the document, to where as the "Address and other stuff is on the left side of the document.

Im still pretty new to this, so I can reduce the lines is there a way to align certain text to the left or the right of the word document?

I attached my work book as an example of you want to see it...The command buttons are in columns W, X

Ps, I thought a line continuation was represented by & _ and if that is present, in theory shouldn't you be able to make as many lines as you want?

Anyways, thanks for the help!

- Jeff

[IcePirates]

I just realized my last post might not have been clear enough, so I just wanted to add one other thing (the forum wouldn't let me edit my post above)

So when the Word document is created the "Delivered Via Courier" part needs to be aligned to the right, and all the other information (such as the company address, and so on) needs to be on the right.

It seems the big spces I have where the "Delivered Via Courier" part is causing me to have a "Too many continuous lines error" so is there a way I can get rid of the spaces and just use some sort of command to just align the "Delivered Via Courier" part to the right of the document.

- Thanks again,

[Bob Phillips]

I kept the spaces, Space(106)! The spaces are not the problem, I just made it more readable, the number of line continuations is the problem.

[mdmackillop]

Have you tried splitting it?
wdRng1 = " " & wks.Cells(ActiveCell.Row, 1) _
& vbNewLine & _
" Delivered Via Courier" & wks.Cells(ActiveCell.Row, 26) _
& vbNewLine & _
" Sale File # " & wks.Cells(ActiveCell.Row, 9) _
& vbNewLine & _
" " & wks.Cells(ActiveCell.Row, 2) _
& vbNewLine & _
" " & wks.Cells(ActiveCell.Row, 3) _
& vbNewLine & _
" " & wks.Cells(ActiveCell.Row, 4) _
& vbNewLine

wdRng2 = " " & wks.Cells(ActiveCell.Row, 5) _
& vbNewLine & vbNewLine & vbNewLine & _
"Attention: " & wks.Cells(ActiveCell.Row, 6) _
& vbNewLine & vbNewLine & vbNewLine & _
"Enclosed for your retention is the following file information from EnCana Oil/Gas Partnership: " & wks.Cells(ActiveCell.Row, 26) _
& vbNewLine & _
" " & wks.Cells(ActiveCell.Row, 17) _
& vbNewLine & vbNewLine & vbNewLine & vbNewLine & vbNewLine & _
"Please acknowledge receipt by signing, dating and returning one copy of this letter to the writer " & wks.Cells(ActiveCell.Row, 26) _
& vbNewLine & vbNewLine & _
"Should you have any concerns please don't hesitate to contact us." & wks.Cells(ActiveCell.Row, 28) _
& vbNewLine & vbNewLine & _
"Sincerely, " & wks.Cells(ActiveCell.Row, 26)

wdRng = wdRng1 & wdRng2

[IcePirates]

Hey,

I tried it...when I tried to create the transmittal - it created a word document that just said, "False"

I attached my script/excel work-book if youd like to take a gander at it.

[mdmackillop]

I'm not getting an error, but try Chr(13) instead of vbNewLine

[IcePirates]

Could it be something unique to my Excel environment?
This is what Ive altered my script too (just for a test version) to try and get rid of vbNewLine

Sub TransferData()
'This macro transfers the data range to a Word Document
'
'Constants:
'docFullName = The full name of an already existing Word document
'
'Variables:
'doc = The Word document (assumed to be empty)
'i = A counter (for rows)
'j = A counter (for columns)
'tbl = A Word table
'wdRng = A Word range (the first paragraph of doc)
'wks = The worksheet "data" that contains the data range
'
'Const docFullName = "C:\OLE Automation\Word.doc" '
Dim doc As Object
Dim i As Long
Dim j As Long
Dim tbl As Object
Dim wdApp As Object 'Only if you require a new document each time
Dim wdRng As Object
Dim wks As Worksheet
'Assing Word objects 'Only if you require a new document each time
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
Set doc = wdApp.Documents.Add
'Assign variables and objects
Set wdRng = doc.Paragraphs(1).Range
Set wks = ThisWorkbook.Worksheets("Data")

'Transfer the data

If Sheets("Data").ComboBox1.Value = "General Transmittal" Then

wdRng1 = " " & wks.Cells(ActiveCell.Row, 1) _
& vbNewLine & _
" Delivered Via Courier" & wks.Cells(ActiveCell.Row, 26) _
& vbNewLine & _
" Sale File # " & wks.Cells(ActiveCell.Row, 9) _
& vbNewLine & _
" " & wks.Cells(ActiveCell.Row, 2) _
& vbNewLine & _
" " & wks.Cells(ActiveCell.Row, 3) _
& vbNewLine & _
" " & wks.Cells(ActiveCell.Row, 4) _
& vbNewLine

wdRng2 = " " & wks.Cells(ActiveCell.Row, 5) _
& vbNewLine & vbNewLine & vbNewLine & _
"Attention: " & wks.Cells(ActiveCell.Row, 6) _
& vbNewLine & vbNewLine & vbNewLine & _
"Enclosed for your retention is the following file information from EnCana Oil/Gas Partnership: " & wks.Cells(ActiveCell.Row, 26) _
& vbNewLine & _
" " & wks.Cells(ActiveCell.Row, 17) _
& vbNewLine & vbNewLine & vbNewLine & vbNewLine & vbNewLine & _
"Please acknowledge receipt by signing, dating and returning one copy of this letter to the writer " & wks.Cells(ActiveCell.Row, 26) _
& vbNewLine & vbNewLine & _
"Should you have any concerns please don't hesitate to contact us." & wks.Cells(ActiveCell.Row, 28) _
& vbNewLine & vbNewLine & _
"Sincerely, " & wks.Cells(ActiveCell.Row, 26)
End If



When I split up the wdRng the command button in the excel document creates a new word document and the word document is just blank
Any ideas why that could be? Chr(13) in replacement of vbNewLine isn't working either...? Weird huh?


thanks!!

- Jeff

[Kenneth Hobs]

To see what ascii number constants like vbnewline use, do this in the Immediate window.
debug.print asc(vbnewline) This is the same as Chr(13). It is the same as vbCrLf though which is two codes, Chr(13)+Chr(10) even though they only show 13 in both these commands:
debug.print asc(vbCrLf)

It is easier though to just type the constant vbCrLf and press F1 to view the help which explains it all.

Suggestions:
1. Always use Option Explicit.
2. Always Compile code before testing.
3. Use the Selection method rather than the Range method to build your MSWord file as I demonstrated in your referenced thread.
4. Record the macro in MSWord to learn the commands needed to do things like Justify text.

As you can see from suggestion (4), if you can't make it work manually, I am not sure how you can make it work via a macro. Using space characters to align text is bound to cause many problems unless you use monospaced fonts. So, learn how to make the doc manually, then record a macro and do it from a blank doc. If you want left and right justification, you should probably look into columns and justification.

Based on (4), I think you will find that (3) makes sense and gives you more control than the Range object.

If you attached what the manually created doc looks like, I might be able to figure out what you need.

When you are concatenating strings to avoid spanning line limits, you can do something like this.
Sub Test
Dim s as String
s = "Hello "
s = s & vbcrlf
s = s & "World!"
MsgBox s
End Sub
Keep in mind that string concatenation like this takes longer than some other methods. For your needs, it probably won't be a factor.

[IcePirates]

Hey Kenneth,

Ok Im going to mess with some of these suggestions of yours...But are you saying that if I use

Dim s As String
s = "This is my text "


I can make as many lines as I need in my document?

I guess another question of mine is why does vbNewLine have limits? I thought anything that had & _ represents a line continuations, and in this tutorial I have, it states line continuations can continue on as long as needed.

Maybe Im mis-interpreting this part of this tutorial?

Anyway, regardless...I attached is the word document of how the transmittal is supposed to look.

Now using vbNewLine I can get the script to create a document all the way up to " Sincerely, "

But as you can see in the document attached there are a few lines after Sincerely that need to appear.

[Kenneth Hobs]

It is not vbNewLine limiting you. VBE compiles your code by line. If your line is too long, it can't compile it. It has to do with memory limitations, 32kb is the limit most likely.

[Kenneth Hobs]

If I was doing it, I would use FormFields unless you need those parts marked with special text formatting like bold and such. If that is the case, I would use bookmarks.

Another method is to create a one record xls or txt file for a standard mail merge. If one record data file is always the same, it is easy to do. It is even easier if you are merging based an SQL. One can use SQL to merge 1 to all your records. An all merge is what you see junk err bulk mailers doing.

I would suggest that you remove the ____ parts. In the old days, people printed forms by hand or typewriter. You have tools to make it seem as if you typed it all from scatch.

[IcePirates]

Hey,

I was initially thinking about using form fields...But by having one Excel sheet we can create transmittals from, it eliminates the need for employee's to have to create transmittals. Also, formfields is a good idea, but its been brought to our attention that the information in our spreadsheet says one thing, but the transmittal says something different than the data in our spreadsheet, by having a transmittal created based on excatly whats in the spreadsheet ensures we have the same information on both sheets, so this is why I cant use form fields, but thanks for the advice, because initially, I was thinking formfields was the best way to go...My next question:

Well, still my question from last week, but I didn't get a chance to check the forum, because most of us left early, due to extreme weather here in Calgary.

So instead of using vbNewLine, are you recommending I use
Sub Test
Dim s As String
s = "This can be the next part of my text "

Since my script already uses vbNewLine, is it possible just to incorporate
Sub Test
Dim s As String
s = "This can be the next part of my text "

some where near the botton of each wdRng in my script, so I dont have to go take out all the vbNewLines...Or

Would it be better to remove the vbNewLine and use the dim s as string
then define what s =?

Also, if Im going to use what's above...How would I apply this to my script I have already, would I in-corporate calling the data from cells as I need it? Just the same way as I do now, by using - ActiveCell.Row, 25 (as an example) ?


Talk to you soon!

Thanks,

[IcePirates]

Hello,


This issue has been solved, for those of you keeping up with it.

My last question regarding this project is, (see attached document)
The excel document successfully creates a transmittal in Microsoft Word. Inside the transmittal is a file number within the body of the document...That file number comes from column Q, in some cases the user might need to select multiple cells in column Q, because transmittal going out may include just the one file number, or the transmittal might include two or three file numbers.
So take a look at the example, try creating a transmittal based on Row2, once you have done that, look at column Q, what if row 2 & 3 need to be listed when the user creates a transmittal? If you notice rows 2, 3 have the same information, going to the same company, on the same date...So when a transmittal is created based on the information in row two and the user wants to include the file number from column Q in row 3...How would that work?
To create a transmittal go to columns X, Y- the command buttons are there

Does anyone have any suggestions on how I would allow the user to select the rows in column Q that are necessary?

Kenneth, you said to use (0, -1) to offset...But do you have any working examples of how this would work, so I could see it before implmenting it...Plus Id like to see a working example, or an explanation of how the offsetting works, if you had some time to explain it a bit?

Or this just came to me....What, what if when the user goes into column Q when filling out the row...a pop-up comes up and says, "What file numbers would you like to include and some how the user selects what rows in column Q he/she wants to include...

See even if there is two file numbers going to a specified company how users input on this form is one row at a time...So even if there is two files going to the same company there will be two identical entries on the excel sheet, the only thing that will be different is the file number in column "Q"

[IcePirates]

I just had an idea, it just came to me:

What about a check box on every line column Q, so that way, before users click the command button to create the transmittal, they can select, via check box, which lines in column Q they want to be listed in the transmittal?

Ideally it would be having a code that would create the check box and associate it with a cell based on a condition. So, if a cell in Column Q has a value (or is not blank) I'd like to have it associated with the command button so when the command button is clicked which ever check boxes are checked in column Q, that data would appear in my Word Document when the user seelects "Create Transmitta"

Anyone ever done anything like that? Im going to see if I can come up with something that works...I doubt it, but Im going to try

[mdmackillop]

Simpler just to insert an "x" and use that.

[IcePirates]

Simpler just to insert an "x" and use that.

What do you mean, 'insert an "x" '?

Let me know!

Thanks,

Jeff

[Benzadeus]

Hi I have a question.

You said that:
Debug.Print Asc(vbCrLf)
returns 13. I believe this happens because vbCrLf is a 2-character string and the Asc function return only the ASCII code of the first character.

I tried
Debug.Print Asc("Test")
And I got 84, the ASCII code of the first letter of the expression ("T").