Solved: Word object question

[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.
[vba]With objApp
.ActiveDocument.SaveAs _
FileName:="c:\test\" & strPREVIOUSOrdernumber & ".doc"
.ActiveDocument.Close (wdDoNotSaveChanges)
End With[/vba]

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....
[vba]
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[/vba]
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
[vba]
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

[/vba]

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!!!!!!!!!!!!!!!!