Solved: Word object question

[Mr Doubtfire]

I have the following which works well and I would like to add new "With objApp" based on certain condition(s).
How I could add new "With objApp" after the closing of the first one "End with".
Thanks.

Option Explicit

Private Sub Form_Load()

Dim objApp As Word.Application
Dim strToday
Dim strCustomer
Dim strReference
Dim variable1

Set objApp = New Word.Application

strToday = Date
strCustomer = "Killian"
strReference = "Order notice"
variable1 = "blah blah"

Clipboard.Clear
Clipboard.SetData Picture1.Image, vbCFBitmap

With objApp
.Documents.Add , , , True
.Visible = False
.Selection.Paste
.Selection.TypeText Text:=Chr(3)
.Selection.TypeText Text:=strToday & vbCrLf
.Selection.TypeText Text:=strCustomer & vbCrLf
.Selection.TypeText Text:=strReference & vbCrLf
.Selection.TypeText Text:="variable1" & vbCrLf

With .ActiveDocument.Paragraphs(1)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(2)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphRight
End With
With .ActiveDocument.Paragraphs(3)
.Range.Font.Size = 12
.Range.Font.Bold = False
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(4)
.Range.Font.Size = 14
.Range.Font.Bold = True
.Alignment = wdAlignParagraphCenter
End With
With .ActiveDocument.Paragraphs(5)
.Range.Font.Size = 12
.Range.Font.Bold = False
.Alignment = wdAlignParagraphRight
End With
End With

If condition1 is True then
With objApp
......................
End With
End if

If condition2 is True then
With objApp
......................
End With
End if


objApp.ActiveDocument.SaveAs FileName:="c:\test\doc1.doc"
Set objApp = Nothing
End Sub

[MOS MASTER]

Hi,

Your question is a little vague.

If you wish to do something to the same document that the other code between the with block is doing then you can put your If Then Else code between the existing with block. (Theres no need to start a new one)

Perhaps you could explain a bit more what you're trying to do?

:whistle:

[Mr Doubtfire]

Sorry for not being clear.
The format is in pair(s) for "Selection" and "With".

Do Until adors1.EOF


.Selection.TypeText Text:="variable1" & vbCrLf
.Selection.TypeText Text:="variable2" & vbCrLf
.Selection..............

With .ActiveDocument.Paragraphs(1)
With .ActiveDocument.Paragraphs(2)
Wtih ..........

adors1.MoveNext
Loop
How could I apply "If' condition within a loop and in between the pair(s)?
Thanks!

[MOS MASTER]

Hi, :yes

I'm very sorry but I still have a hard time understanding what you mean.

But if I'm reading you correct you have to run repetitions of some code?

If so Build a function that does the repetition for you and have as optional parameters the App on which you execute and the other parameters you need.

You can call the UDF as many times in your code as you like.

But for a better answer I need to no exactly what you doing.
Perhaps someone else has a idea of what your trying to do...

Later..:whistle:

[Mr Doubtfire]

The top part is a header (name address, city, country), the second (with condition) is a flexible part that is based on the item(s) the customer orders. The condition put is based on the number of item(s) required ( one to thousands items therefore it probably turns out more than one page). ie. the same header could have more than one Word document to accomodate all the item(s) needed.

Thanks and apologize for not being detailed.

[MOS MASTER]

Hi, :yes

Please don't apologize sometimes people just don't understand each other..This is one of those times.

I hope someone comes by who does get the picture..I'll leave it at this because this isn't helping you that much! :whistle:

[fumei]

OK, first of all, I am also trying to figure out exactly what is happening. So I am going to tightend up your code. I first have to comment that it is NOT a good idea to leave your declaration as undefined data types. ALL your variables are Variants.

Further, normally a variable starting with "str" indicates a String Data type. Dates are normally indicated by dte - so dteToday is recognized by others as a Date data-type. For information purposes, here are the byte quantity assigned for each data type.

Byte 1 byte
Boolean 2 bytes
Integer 2 bytes
Long (long integer) 4 bytes
Single (single-precision floating-point) 4 bytes
Double (double-precision floating-point) 8 bytes
Currency (scaled integer) 8 bytes
Decimal 14 bytes
Date 8 bytes
Object 4 bytes
String (variable-length) 10 bytes + string length
String (fixed-length) Length of string
Variant (with numbers) 16 bytes
Variant (with characters) 22 bytes

Total memory address assigned = 88 , total required = 36.

Option Explicit

Private Sub Form_Load()

Dim objApp As Word.Application
Dim strToday (22 bytes assigned - Date type w/b 8)
Dim strCustomer (22 bytes assigned - String type w/b 7 bytes)
Dim strReference (22 bytes assigned = 12 bytes needed)
Dim variable1 (Also a String - 22 bytes asigned, 9 needed)

Set objApp = New Word.Application

strToday = Date
strCustomer = "Killian"
strReference = "Order notice"
variable1 = "blah blah"

Clipboard.Clear
Clipboard.SetData Picture1.Image, vbCFBitmap

With objApp
.Documents.Add , , , True
.Visible = False
With .Selection
.Paste
.TypeText Text:=Chr(3) & strToday & vbCrLf & _
strCustomer & vbCrLf & _
strReference & vbCrLf & -
"variable1" & vbCrLf
end with

With .ActiveDocument.Paragraphs(1)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(2)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphRight
End With
With .ActiveDocument.Paragraphs(3)
.Range.Font.Size = 12
.Range.Font.Bold = False
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(4)
.Range.Font.Size = 14
.Range.Font.Bold = True
.Alignment = wdAlignParagraphCenter
End With
With .ActiveDocument.Paragraphs(5)
.Range.Font.Size = 12
.Range.Font.Bold = False
.Alignment = wdAlignParagraphRight
End With
End With

If condition1 is True then
With objApp
......................
End With
End if


If condition2 is True then
With objApp
......................
End With
End if


objApp.ActiveDocument.SaveAs FileName:="c:\test\doc1.doc"
Set objApp = Nothing
End Sub

OK, that being said, you could please clarify in better detail again what you are doing?

You mention "header", but there is no action on a header object. You state"the same header could have more than one Word document to accomodate all the item(s) needed." I am quite confused by what you mean by the same header could have more than one Word document. Huh? You are putting whiole Word documents into the Header????? Thise seems very very odd.

[Mr Doubtfire]

Thank you fumei for your help cause I scare MOS Master for NOT being clear.
Your advice is good. The variables used are just examples, String variables should be declared as String, date .....as Date....
I have two tables which are Customers and Orders. (just be simple in explanation)
I would like to create Word document(s) the orders that customers place.
My final documents' format would be -

Customer1
Address1

Re: order #xxxxxx

Sku1 Qty1 ....
Sku2 Qty2 ....

I have placed a loop to go through the database, and a condition to print only the "Sku Qty" line(s) if the order is the same, if not print header. The "header" means the address part.

I just do not how to put condition within the "With objApp End With" to selectively print with " With .Selection and With .ActiveDocument.Paragraphs(x) ".
Hopefully it is clearer. I have tried to put two "With objapp" but system does not like it.
Thanks to all.





.Se

[fumei]

I have placed a loop to go through the database, and a condition to print only the "Sku Qty" line(s) if the order is the same, if not print header. The "header" means the address part.

I just do not how to put condition within the "With objApp End With" to selectively print with " With .Selection and With .ActiveDocument.Paragraphs(x) ".


I think you must take greater care with your terms. Now you are using the word "print". You state "print the SKU line if the order is the same", if not, print header - which for some VERY strange reason means the address.

Why on earth do you refer to the address as "header'?

In any case, I am still not getting what you are trying to do. I am positive we can help with this...but I am not sure what "this" is!

You want to do something With the Selection, and something With paragraphs.

Please try again and explain exactly what you are trying to do, what it is you want to happen.

I can see you are trying to do some conditional logic on something, but I can not quite get exactly what, or where. Walk it through step by step.

And please clarify "selectively print ". Are you really printing???

[Mr Doubtfire]

Thank you for the patience.
"Print" does not mean going to printer. It means the content going to the Word document.
It might better to give an example. (This example assumes the Word document has ten lines only)

I have the format -

Customer1
Address1

Re: Order #xxxxx

Sku1 Qty1
Sku2 Qty2
...

Sku100 Qty100

Thank you!

From the above example, the part of the "Sku.... Qty" varies from order to order. Therefore some orders might form more than one page, which has the same Customer information but different "Sku ....Qty".
Programatically, I use a "Do until .............................Loop" to go through the database for all the orders and customers' info.
The problem I have is when the maximum lines of a page reaches its max per page I have to go to the second, third. .............last page of documents although the customer's info is the same, until the next order comes in place I would start a fresh document. Otherwise the same order would store its own contents NO matter how many skus are there.
Hopefully, by now you get what I am saying.

Thank you again for the time.

[fumei]

The problem I have is when the maximum lines of a page reaches its max per page I have to go to the second, third. .............last page of documents although the customer's info is the same, until the next order comes in place I would start a fresh document. Otherwise the same order would store its own contents NO matter how many skus are there.
Hopefully, by now you get what I am saying.

Thank you again for the time.

Sorry, but you are still not being clear.

I asked, a second time, for you to walk through what you want to happen, step by step. Your last post does not do that. It does not walk through step by step. It does not clearly state what you want to happen. I hope I am being clear myself.

So, again. Walk it through step by step. State what it is you want to happen.

Let's use your "example" above.

1. There are a enough lines of "Sku....Qty", so it goes to another page. OK...so? Why is this a problem? maybe it is a problem for you...but you do NOT state what the problem is. You do NOT state what it is you want to happen.

2. "the next order comes in place I would start a fresh document" What does this mean? Why is this a problem? It makes sense to me that the next order would be in a fresh document. Unless you want the order for a specific customer to be appended to a previous document. If so, you have never stated this.

3. "Otherwise the same order would store its own contents NO matter how many skus are there." I have no clue whatsoever what you mean by this. Are you saying that an order with 4 SKU should be treated differently from an order with 90 SKU? Why is the number of SKU significant? Maybe it is for you....but you do NOT state why that is, or what you want to happen if it IS a large number of SKU. What is the cutoff. If there are 49 SKU...do you want it to process differently from an order with 50 SKU? "NO matter how many skus are there" implies that the number of sku means something....but you do NOT state what that something means, or what you want to happen.

REPEAT!!!! State what you want to happen, step by step.

Step by step. If a specific customer order is to go to a specific customer document....say so.

We can not help unless you tell what is happening, what you want to happen, and what those things are in a logical step by step process.

Again, I am positive that there is a (probably) easy solution to this...what ever it is. However, I am still not clear what the problem is. Or what you want to happen.

Be patient. Perhaps write it down first. This is always a good idea. Think about what EXACTLY, no fuzzy assumptions...we can not read minds. You must state clearly EXACTLY what you want to happen.

Sorry, but - "Otherwise the same order would store its own contents NO matter how many skus are there." is not clear.

Same order? - Does that means there is a situation that you could get an order twice? Three times? During the same processing? At a later date? Do you need to check existing document to see if it is the same order?

"Own contents"...Huh? I have no idea what you mean by that.

Then there is the number of skus...my comments are above.

Would like to help. Try again.

[Mr Doubtfire]

Other than THANK YOU, nothing I could express myself for your patience.
Hopefully the following is simple.

I have the format for a Word document with ten lines ONLY (keep simple) -

Customer1
Address1

Re: Order #xxxxx

Sku1 Qty1
Sku2 Qty2

Thank you!

From the above I do not have problem to create a document from the database as long as the number of skus ordered is always kept to two or one.
My question is the maximum of lines per document is ten ONLY.
When the number of skus ordered by the customer exceeds 2, then I need to go the second page (with same customer information).
I have the following two pages Word doc.

Page 1

Customer1
Address1

Re: Order #12345

Sku1 Qty1
Sku2 Qty2

Thank you!

Page 2

Customer1
Address1

Re: Order #12345

Sku3 Qty3
Sku4 Qty4

Thank you!

The above is what I want working with a "Word.Application" object, the same document in mulitple pages for the same order.
How to put condition with that "objapp" AND within the database loop?
Thx!:think:

[fumei]

OK, first off, i will rant, then we can start to work on a solution.

Your post is finally giving details that are....hmmmmm. rather important.

10 lines per page - never mentioned before, or rather a vague thing of "ten lines only"...this does not mention that you need 10 lines only PER PAGE - rather critical point.
2 SKU per page? - never mentioned before, and rather critical
A REPEAT of customer information - never mentioned before, and absolutely critical.

Do you see what I mean? You have to tell us EXACTLY what the situation is and what you want to happen. We are not mind readers.

OK. I am not a database person much at all, but I do know Word. So I will concentrate on that.

1. Start with the data. Pick up the data and dump it into public variables...maybe you could use private variables...have to check for that.

3. Build arrays of the data, customer, address, and have a counter of the number of SKU determined for that customer. This counter is built up BEFORE doing anything with the document.

4. Alternately (and I think this is preferable), have the text "Customer" and Addres - in other words the repetative stuff as AutoText. Say, "Customer" is an AutoText fired by the text "cus", and "Address" is fired by an AutoText ""adr".

5. OK, say the counter is 13...there are 13 SKUs involved. Divide by 2. 6.5. What does that mean> You need 7 pages.

(By the way, this seems to be poor document design...but that is another issue....)

6. The counter is an declared integer named SKUCount. There is a declared variable of var

Dim SKUCount() As Integer
Dim i As Integer
Dim var

ReDim Preserve SKUCount(i)
'skucount(i) = ...from database
i = i + 1

' reset i
i = 0
On Error Resume Next
for var = 1 to SKUCount
With ActiveDocument.Range
.Text = "cus"
.InsertAutoText
.Text = vbCrLf
.Text = "adr"
.InsertAutoText
.Text = vbCrLf
.Text = "SKU " & SKUCount(i)
' etc etc until you have completed that page
.InsertBreak Type:=wdPageBreak
i = i + 1
End With
Next

Something like that.

With the autotext you can insert the text by code and complete it by code.

The count of the loop is based on the number of SKU chosen, divided by 2.

This is not complete but I have to run right now. Hopefully this may help to get you rolling. You loop based on a counter of the SKU.

[Mr Doubtfire]

Thank you again.

One of my problem is to put fonts (size, bold, italic....).
How could it plug them in (between/among) the condition.
Thanks!:bow:

[Mr Doubtfire]

Sorry, I forget to ask you where I should insert the "Word.application" and "Documents.add" in that loop?
Thhhhhhhhanks!!

[fumei]

Back up! Back up!

Whoa, wait a second. This is bouncing out of control.

Uh, if you are asking about how to place Word.Application, and Document.Add....you are starting at the beginning; you are starting to ask about creaing a document in the first place.

Let's start again.

What are you trying to do, and when are you trying to do it?

Word.Application, in the loop????? No, no, you are are going to make an instance, or USE an instance of Word once. You are not going to loop it!

You are not going to loop adding documents either. Unless that is what you want to do. I am still not clear.

On one hand it seems like you are trying to automate information from the database - on the other you seem to have user input.

The SKUCount routine simply figured out what to do with the document based on the number of SKU chosen. Previously it seemed that this was one document, with multiple orders. Is it supposed to make individual files?

Remember you can work with the SAME Word.Application object to create, write to, and save a number of documents. I think you are perhaps confusing the application object with a document object.

Please try and walk it through again.

The user makes their choices on a form, and clicks a button MakeDocument. This is ONE event. This event can be made to take the information on the form and format it however you want (really, just about any way you want). It can then write and save a document file.

If this document making command does JUST THAT, then the form is still active, and when finished the user has still got the form. They go to the next recordand choose the number of SKU.

When they are done, they click a Finish button and they are done, the form unloads.

Does the user choose the record, or are you looping through the records? I am getting a headache, but here is what I think is happening.

You have a form that displays database records. Number of which...I don't care. Say it is 10.

1. Form loads.
2. Word instance created.
3. Record 1 is displayed on your form. Also on the form, are user choices of SKU.
4. User chooses X number of SKU.
5. User clicks a command to create the document.
6. Routine figures out, based on the number of SKU how to write the document.
7. Document is created (Add.New)
8. Document is written
9. Document is saved.
8. Control is passed back to form, for the user to deal with the next record.

Correct?

If you are having user intervention for each record, then control has to be passed back and forth. Will EVERY record have a file written for it? If so, then yes, you could add that as a loop. But it would use the same objApp.

[Mr Doubtfire]

With the many questions and hints I got from you, I finally got what I want.

Thanks!
But the only thing I do not get is

Customer1
Add1

Re:Order confirmation xxxxxxxxxxxxx

Sku1 Qty1
Sku2 Qty2

Thank you!

On the first page the "Re:Order confirmation..." is aligned centrally. And on the second page it is "Left"-aligned.
This is the only thing I could not get. Others are working perfectly in both retrieving and saving.
Thank you!:bow: for the patienceeeeeeeeeeeeeeeeeeeeee........

[Mr Doubtfire]

I have the following code further to my previous sample reply.


Option Explicit
Dim strOrdernumber As String
Dim strPREVIOUSOrdernumber As String
Dim strCustomer As String
Dim strAddress1 As String
Dim strSKU As String
Dim intQTY As Integer
Dim strSTATUS As String
Dim strPO As String
Dim strReference As String
Dim intSKUcount1 As Integer
Dim intTOTALSKUcount1 As Integer
Dim blnFRESHDocument As Boolean
Dim objApp As Word.Application
Private Sub Form_Load()

Dim strquerySelect, strqueryFrom, strqueryWhere, strqueryOrderby, strQuery
Dim adoconn1 As New ADODB.Connection
Dim adors1 As New Recordset
adoconn1.Open "DSN=dsn1"
strquerySelect = "SELECT * "
strqueryFrom = "FROM (.......) "
strqueryWhere = "WHERE ............... "
strqueryOrderby = "ORDER BY .....; "
strQuery = strquerySelect & strqueryFrom & strqueryWhere & strqueryOrderby
adors1.Open strQuery, adoconn1, adOpenDynamic, adLockReadOnly, adCmdText
strReference = "ORDER CONFIRMATION"
intSKUcount1 = 0
blnFRESHDocument = True
intTOTALSKUcount1 = 0

Do Until adors1.EOF
erase_header_variables
erase_SKU_variables
strOrdernumber = adors1("ordernum")
strCustomer = adors1("customer")
strAddress1 = adors1("address1")
strPO = adors1("ponum")
strSKU = adors1("item")
intQTY = adors1("qty")
strSTATUS = "good"
'HEADER
If strPREVIOUSOrdernumber = "" Or strOrdernumber <> strPREVIOUSOrdernumber Then
If strPREVIOUSOrdernumber <> "" Then
'END
With objApp
.Selection.TypeText Text:=vbCrLf
.Selection.TypeText Text:="Thank you!"

With .ActiveDocument.Paragraphs(1)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(2)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
End With
intSKUcount1 = 0
objApp.ActiveDocument.SaveAs FileName:="c:\test\" & strPREVIOUSOrdernumber & ".doc"
objApp.Quit False
Set objApp = Nothing
End If
Set objApp = New Word.Application
With objApp
.Documents.Add , , , True
.Visible = False
End With

blnFRESHDocument = True
Else
If intSKUcount1 = 2 Then
With objApp
.Selection.InsertBreak Type:=wdPageBreak
End With
intSKUcount1 = 0
intPAGEPerorder = intPAGEPerorder + 1
blnFRESHDocument = True
End If
End If

If blnFRESHDocument = True Then

With objApp
.ActiveDocument.PageSetup.TopMargin = CentimetersToPoints(1)
.Selection.TypeText Text:=strCustomer & vbCrLf
.Selection.TypeText Text:=strAddress1 & vbCrLf
.Selection.TypeText Text:=vbCrLf
.Selection.TypeText Text:=strReference & " : " & strPO & vbCrLf

With .ActiveDocument.Paragraphs(1)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(2)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(3)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(4)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphCenter
End With
End With
blnFRESHDocument = False
End If

'CONTENT
With objApp
.Selection.TypeText Text:=" ITEM QUANTITY STATUS" & vbCrLf
.Selection.TypeText Text:=strSKU & " " & intQTY & " " & vbCrLf

With .ActiveDocument.Paragraphs(5)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(6)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
End With

intSKUcount1 = intSKUcount1 + 1
intTOTALSKUcount1 = intTOTALSKUcount1 + 1
strPREVIOUSOrdernumber = strOrdernumber
adors1.MoveNext

Loop
If intTOTALSKUcount1 > 0 Then
With objApp
.Selection.TypeText Text:=vbCrLf
.Selection.TypeText Text:="Thank you!"

With .ActiveDocument.Paragraphs(1)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(2)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
End With
objApp.ActiveDocument.SaveAs FileName:="c:\test\" & strPREVIOUSOrdernumber & ".doc"
objApp.Quit False
Set objApp = Nothing
End If
Set adors1 = Nothing
Set adoconn1 = Nothing
End Sub
Function erase_header_variables()
strOrdernumber = ""
strCustomer = ""
strAddress1 = ""
strPO = ""

End Function
Function erase_SKU_variables()
strSKU = ""
intQTY = 0
strSTATUS = ""

End Function



The above example is supposed to print only two skus per page (keep simple). Therefore, for every two skus one page..eg. five skus has three pages...
The only thing that does not work properly is the reference "Re: Order confirmation ............." which is supposed to be center-alligned is correctly alligned on the first page, BUT not after the first page (where applied).
Please comment.
Thanks.

[fumei]

Aaaaaacccckkkkkk! First off....

Please use the underscore character to truncated your lines of code better. Having the code stretch out across the screen makes things difficult to read. For example, rather than making one long line (thus stretching the screen so we have to scroll...)

If strPREVIOUSOrdernumber = "" _
Or strOrdernumber <> strPREVIOUSOrdernumber Then
OR:
objApp.ActiveDocument.SaveAs _
FileName:="c:\test\" & _
strPREVIOUSOrdernumber & ".doc"

It makes itmuch easier for the rest of us. Thank you.

2. What is with:
Selection.TypeText Text:=" ITEM QUANTITY STATUS" & vbCrLf

????? Inserting text with lots of spaces like is bad form. Hard coding spaces like that is poor design, and can easily get skewed out of whack. Design, and set up a table, and use it.

Further, if you used a properly dsigned table, INCLUDING Styles to fit the text within the table, there would be no issue with paragraph alignment. That is what Styles are for.

Regarding for alignment...well, that is what you get when you hard code pargraph formats. Use Styles. Have one style for Order#_FirstPage, another for Order#_OtherPages.

I look at:
With .ActiveDocument.Paragraphs(5)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
and I know nothing. I have no idea what the fifth paragraph is, and if I understand correctly, depending on the number of SKU, paragraph 5 could, in fact be different.

Use Styles!

3. You have a comment ' HEADER. This is still confusing for me. Do you mean a heading? As in, text at the top of a list? Again, use a table.

4. It would really really help if you commented your code, stating what you think is happening. Obviously, you are not totally inexperienced, but it would make things easier to point out improvements, if you commented your code. Even the obvious stuff.

For example you have:
intTOTALSKUcount1 = intTOTALSKUcount1 + 1
strPREVIOUSOrdernumber = strOrdernumber
adors1.MoveNext

Loop
If intTOTALSKUcount1 > 0 Then
I am not clear on how intTOTALSKUcount1 could EVER be 0. If you have intTOTALSKUcount1 = intTOTALSKUcount1 + 1 , how can If intTOTALSKUcount1 > 0 ever be false?. it will always be true. Perhaps if you commented more it would be clearer what is going on.

You asked specifically about Order # alignment on page 2. I can't tell, because you are hardcoding paragraph formats. Is it #5, #6, # 2?

5. I do not understand why you are doing:
objApp.Quit = False
objApp = Nothing
You are NOT quitting the application, butyou ARE killing the instance of it. Then you, of course, have to make another instance with Set objApp = New Word.Application.

You do not have to do this.

[Mr Doubtfire]

Thank you for the comments.
1. I would use "_" for long statements.
2. The first four paragraph are four the "Header"/"Heading" - customer name, address....and empty lines.. That's why I use Paragraph(5) for the first line of "Content" - Sku, qty, status. "Header" means order header information.
4. intTOTALSKUcount1 COULD be zero when there is nothing to create - NO orders at all. I would add comments in the future.
"hardcoding paragraph" - Please tell me how NOT to hardcode still with styles and fonts.
5.

objApp.Quit = False
objApp = Nothing
I have put them there just because without them I found out the Word file(s) were still opened.

I am here to learn from Everyone.
Please comment!

[fumei]

No problem, I am not being critical...OK, perhaps critical, but I am not trying to be mean...and I AM trying to help.


5.

objApp.Quit = False
objApp = Nothing
I have put them there just because without them I found out the Word file(s) were still opened.

Then close them! At the end of each action on a specific document...save and close it.
With objApp
.ActiveDocument.SaveAs _
FileName:="c:\test\" & strPREVIOUSOrdernumber & ".doc"
.ActiveDocument.Close (wdDoNotSaveChanges)
End With

You explicitly saveas, then close the file. objApp is still alive, ready to process the next file. You THEN do a Documents.Add. There is no point in re-instancing Word.

4. intTOTALSKUCount1 is initialized = 0. OK.....the heck with it....

Option Explicit
Dim strOrdernumber As String
Dim strPREVIOUSOrdernumber As String
Dim strCustomer As String
Dim strAddress1 As String
Dim strSKU As String
Dim intQTY As Integer
Dim strSTATUS As String
Dim strPO As String
Dim strReference As String
Dim intSKUcount1 As Integer
Dim intTOTALSKUcount1 As Integer
Dim blnFRESHDocument As Boolean
Dim objApp As Word.Application
Private Sub Form_Load()

Dim strquerySelect, strqueryFrom, strqueryWhere, strqueryOrderby, strQuery
Dim adoconn1 As New ADODB.Connection
Dim adors1 As New Recordset
adoconn1.Open "DSN=dsn1"
strquerySelect = "SELECT * "
strqueryFrom = "FROM (.......) "
strqueryWhere = "WHERE ............... "
strqueryOrderby = "ORDER BY .....; "
strQuery = strquerySelect & strqueryFrom & strqueryWhere & strqueryOrderby
adors1.Open strQuery, adoconn1, adOpenDynamic, adLockReadOnly, adCmdText
strReference = "ORDER CONFIRMATION"
intSKUcount1 = 0
blnFRESHDocument = True
intTOTALSKUcount1 = 0

' START OF LOOP
Do Until adors1.EOF
erase_header_variables
erase_SKU_variables
strOrdernumber = adors1("ordernum")
strCustomer = adors1("customer")
strAddress1 = adors1("address1")
strPO = adors1("ponum")
strSKU = adors1("item")
intQTY = adors1("qty")
strSTATUS = "good"
'HEADER blah blah
' IF previous IS Blank, OR current order = previous
' call sub to handle
If strPREVIOUSOrdernumber = "" _
Or strOrdernumber <> strPREVIOUSOrdernumber Then
' call the SUB
IfBlankORPrevious
' have to question why following is run
' if the IF expression (see SUB) does not
' return a TRUE
With objApp
.Documents.Add , , , True
.Visible = False
End With
blnFRESHDocument = True
Else
' which means previous is NOT blank, OR
' current order is NOT the previous order

' I can not find why this could be 2 ????
If intSKUcount1 = 2 Then
With objApp
.Selection.InsertBreak Type:=wdPageBreak
End With
intSKUcount1 = 0
intPAGEPerorder = intPAGEPerorder + 1
blnFRESHDocument = True
End If
End If

' End of first IF statement !!!!
' if first IF is TRUE then the document IS
' savedas
' in which case, I do not understand
' the run of the rest of this code

' I believe that this should be a separate SUB
' and it is THERE that a new document is created
' but I have not done so, because I am still trying
' figure what is happening
If blnFRESHDocument = True Then
FreshDoc
End If
' above means if blnFreshDocument = False
' it still is false

'CONTENT HUH?
' Totally lost on what is going on here
' if the order was = previous or NOT blank,
' the document is written and saved
' original code does not close the file
' so WHAT is the document here????
' it "seems' it would be the previous document...I think

With objApp
' I hate this text input!
.Selection.TypeText _
Text:=" ITEM" & _
"QUANTITY " & _
"STATUS" & vbCrLf
.Selection.TypeText _
Text:=strSKU & " " & _
intQTY & " " & vbCrLf
' this DOES seem to be working on the previous
' document, as there is this hard coded numbers
' in which case, why was the document SaveAs???

With .ActiveDocument.Paragraphs(5)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(6)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
End With

intSKUcount1 = intSKUcount1 + 1
intTOTALSKUcount1 = intTOTALSKUcount1 + 1
strPREVIOUSOrdernumber = strOrdernumber
adors1.MoveNext
' END OF LOOP
Loop

If intTOTALSKUcount1 > 0 Then
With objApp
.Selection.TypeText Text:=vbCrLf
.Selection.TypeText Text:="Thank you!"
With .ActiveDocument.Paragraphs(1)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(2)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
End With
objApp.ActiveDocument.SaveAs FileName:="c:\test\" & strPREVIOUSOrdernumber & ".doc"
objApp.Quit False
Set objApp = Nothing
End If
Set adors1 = Nothing
Set adoconn1 = Nothing
End Sub
Function erase_header_variables()
strOrdernumber = ""
strCustomer = ""
strAddress1 = ""
strPO = ""

End Function
Function erase_SKU_variables()
strSKU = ""
intQTY = 0
strSTATUS = ""

End Function

Sub IfBlankORPrevious()
' runs if previous IS Blank,
' OR current order = previous

' start of IF previous order NOT blank
' which means current order MUST be = previous
' for this to run
If strPREVIOUSOrdernumber <> "" Then
'END HUH? don't know what this means
With objApp
With Selection
.TypeText Text:=vbCrLf
.TypeText Text:="Thank you!"
End With
With .ActiveDocument.Paragraphs(1)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(2)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
End With
intSKUcount1 = 0
With ActiveDocument
.SaveAs FileName:="c:\test\" & _
strPREVIOUSOrdernumber & ".doc"
.Close (wdDoNotSaveChanges)
End With
End If
End Sub

Sub FreshDoc()
With objApp
.ActiveDocument.PageSetup.TopMargin = CentimetersToPoints(1)
.Selection.TypeText Text:=strCustomer & vbCrLf
.Selection.TypeText Text:=strAddress1 & vbCrLf
.Selection.TypeText Text:=vbCrLf
.Selection.TypeText Text:=strReference & " : " & strPO & vbCrLf

With .ActiveDocument.Paragraphs(1)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(2)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(3)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(4)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphCenter
End With
End With
blnFRESHDocument = False
End Sub
I had to start breaking this up into subs, and putting comments. I highly recommend that down the road you do this. Having long convoluted code is difficult to read. Break it up into logical chunks.

So I am still looking at this, but have to stop for a bit.

RE: paragraphs, I would actually suggest a couple of things.

1. Other than the number of SKU, the other data information is fixed in number. Name, addres etc. Use formfields to take the information. That way, the data is inserted, rather than typed. The paragraphs holding the formfields should have a specific style (alignment, font, size etc). that way, you do EVER have to concern yourself with format.

I am working on a sample file to demonstrate this. In the meantime, could you please reorganize this into logical subs, so i can see a bit better what is going on. I can;t see where you initialize the objApp in the first place, for one.

[Mr Doubtfire]

Thank you again!
objApp is initialized at the top.
"intSKUcount1 = 2 " is just an example I would like to keep the number of skus printed per document is two (actual no of skus is 20). Therefore after every two skus printed I would have a fresh document added to the same Word file under the same order number.
' I hate this text input! -
We share the same, but how I could improve it !?
The code quoted is just a little part of the original code.
I have myself partitioned the Word document into three parts-
Header - logo, date, customer information, reference
Content - sku, qty, status
End - "Thank you for ............."
All of them have their own "Sub"s.
I totally agree with you for the comments.
PLEASE continue to be critically critical, because that's what I want.

Questions:
For
" With Selection
.Selection.TypeText Text:=vbCrLf
End with
With .ActiveDocument.Paragraphs(x)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphCenter
End With
"
...Paragraphs(x) - does that x correspond with ..selection.Typetext..
If in each of the "sub"s, do I need to restart from "(1)" !?

The original code is working and the only problem I have is aligment is NOT the same as the first page when there is more than one page.

THANKS!

[fumei]

I have myself partitioned the Word document into three parts-
Header - logo, date, customer information, reference
Content - sku, qty, status
End - "Thank you for ............."
All of them have their own "Sub"s.
Huh? Then why is your code all in a bunch? Where are the Subs?

[Mr Doubtfire]

Here is the code with Subs

Option Explicit
Dim strOrdernumber As String
Dim strPREVIOUSOrdernumber As String
Dim strCustomer As String
Dim strAddress1 As String
Dim strSKU As String
Dim intQTY As Integer
Dim strSTATUS As String
Dim strPO As String
Dim strToday As Date
Dim strReference As String
Dim intSKUcount1 As Integer
Dim intTOTALSKUcount1 As Integer
Dim blnFRESHDocument As Boolean
Dim objApp As Word.Application
Private Sub Form_Load()

Dim strquerySelect, strqueryFrom, strqueryWhere, strqueryOrderby, strQuery
Dim adoconn1 As New ADODB.Connection
Dim adors1 As New Recordset
adoconn1.Open "DSN=dns1"
strquerySelect = "SELECT * "
strqueryFrom = "FROM (......................."
strqueryWhere = "WHERE ......................."
strqueryOrderby = "ORDER BY ............r; "
strQuery = strquerySelect & strqueryFrom & strqueryWhere & strqueryOrderby
adors1.Open strQuery, adoconn1, adOpenDynamic, adLockReadOnly, adCmdText
strReference = "ORDER CONFIRMATION"
strToday = Date
intSKUcount1 = 0
blnFRESHDocument = True
intTOTALSKUcount1 = 0


Do Until adors1.EOF
erase_header_variables
erase_SKU_variables
strOrdernumber = adors1("ordnumber")
strCustomer = adors1("cust1")
strAddress1 = adors1("address1")

strPO = adors1("ponumber)
strSKU = adors1("sku1")
intQTY = adors1("qty1")
strSTATUS = "HIGH"
'HEADER - here is the header formation
If strPREVIOUSOrdernumber = "" Or strOrdernumber <> strPREVIOUSOrdernumber Then
If strPREVIOUSOrdernumber <> "" Then
'END - here is the ending
create_END

intSKUcount1 = 0
objApp.ActiveDocument.SaveAs _
FileName:="c:\test\From\" & strPREVIOUSOrdernumber & ".doc"
objApp.Quit False
Set objApp = Nothing
End If
Set objApp = New Word.Application
With objApp
.Documents.Add , , , True
.Visible = False
End With

blnFRESHDocument = True
Else
If intSKUcount1 = 2 Then
With objApp
.Selection.InsertBreak Type:=wdPageBreak
End With
intSKUcount1 = 0

blnFRESHDocument = True
End If
End If
' when blnFRESHDocument is True then create the Header and reference
If blnFRESHDocument = True Then
create_header
create_REF
End If

'CONTENT - here is the place to create the sku line
create_SKU

intSKUcount1 = intSKUcount1 + 1
intTOTALSKUcount1 = intTOTALSKUcount1 + 1
strPREVIOUSOrdernumber = strOrdernumber
adors1.MoveNext

Loop
'This is where to save the last file when there is at least one sku line
If intTOTALSKUcount1 > 0 Then
create_END
objApp.ActiveDocument.SaveAs _
FileName:="c:\test\" & strPREVIOUSOrdernumber & ".doc"
objApp.Quit False
Set objApp = Nothing
End If
Set adors1 = Nothing
Set adoconn1 = Nothing
End Sub

Function erase_header_variables()
strOrdernumber = ""
strCustomer = ""
strAddress1 = ""
strPO = ""

End Function

Function erase_SKU_variables()
strSKU = ""
intQTY = 0
strSTATUS = ""

End Function

Function create_header()
With objApp
.ActiveDocument.PageSetup.TopMargin = CentimetersToPoints(1)
.Selection.TypeText Text:=strCustomer & vbCrLf
.Selection.TypeText Text:=strAddress1 & vbCrLf
.Selection.TypeText Text:=vbCrLf

With .ActiveDocument.Paragraphs(1)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(2)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(3)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphCenter
End With

End With

blnFRESHDocument = False
End Function

Function create_REF()
With objApp
.Selection.TypeText Text:=strReference & " : " & strPO & vbCrLf
With .ActiveDocument.Paragraphs(4)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphCenter
End With
End With
End Function

Function create_SKU()
With objApp
.Selection.TypeText _
Text:= " ITEM " QUANTITY STATUS" & vbCrLf
.Selection.TypeText _
Text:=strSKU & " " & intQTY & " " & vbCrLf

With .ActiveDocument.Paragraphs(5)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(6)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
End With
End Function

Function create_END()
With objApp
.Selection.TypeText Text:=vbCrLf
.Selection.TypeText Text:="Thank you!"

With .ActiveDocument.Paragraphs(1)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
With .ActiveDocument.Paragraphs(2)
.Range.Font.Size = 12
.Range.Font.Bold = True
.Alignment = wdAlignParagraphLeft
End With
End With
End Function



Thank you!

[fumei]

I'm sorry, but I have to take a break from this thread.

1. I asked, as a courtesy, for you to use the underscore character ( _ ) to break up your code. Otherwise, the long lines make it difficult to read. You are not doing so.

2. I mentioned that you do have to set your objApp to nothing. You do not have make new instances. Just close your file, and make a new document. It not needed, and not good programming to be making those multiple instances. Your code still contains this.

3. Please try and learn some proper terms regarding Word. "Header" is a specific term. Your use of it in your posts, and in the comments (what little there are) is distracting. What you are inserting is NOT a header.

4. You state it is broken into "Subs". No it is not. You made them Functions, as in "Function create_header". First of all, it is not a header, Second it is not a Sub, if you declare it as a function. Third, while yes, Functions certainly can run multiple instructions (like a Sub), so yes, they are procedures, general practice has Functions returning a value, usually a single value. Subs are called subs, because it is a sub-routine. Functions are called functions because, mathematically, they generally return a unique value. They are usually declared as a specific data type, as in Function tryThis() As Boolean.

5. I'm tired.

I will attach a demo file later.

Basically, make your common text a style, put your SKU lists in a table, figure out how many pages you need, make those new pages if needed, copy the table, dump the sku list into it(them).

Later.

[Mr Doubtfire]

Thank you for the time and patience.
1. I did put "_" for the long lines which I could see (may be I am using 1024)
2. Sorry for this, VB6 is not my area and I only do it occasionally because I am writing mainframe program.
3. "Header" to me (within my program) means Order header (I believe that is what called in the industry).
4. I would use "Subs" instead of "Functions" although they do not return anything.
5. Sorry to make you tired, but I would not be AND still waiting for your guidance for better programming. I would love to learn how to create tables. Awaiting for your demo.
I would love to know why the pages after the first one DO not have the same alignment as the original settings, in my case the reference line is NOT centered after the first one page.
A BIG THANKS to a BIG PERSON!!!!!!!!!!!!!!!!