No problem, I am not being critical...OK, perhaps critical, but I am not trying to be mean...and I AM trying to help.
Then close them! At the end of each action on a specific document...save and close it.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.
[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.