WORD Automation: Apparent Timing Problems

I have a VB6 app that builds a WORD doc from an Access database. Usually it works fine, but sometimes -- apparently because of timing considerations -- it does not. When it fails, it fails because the sequence of items that appears in the generated WORD doc does not reflect the sequence in which these items are generated in the VB6 app -- apparently asynchronism associated with automation is biting me. In these failure situations, the order of the data being generated by the VB app is not the order in which the data is being accepted by WORD.  For example, elements from the n+1 record in Access will sometimes appear in WORD before elements of the n record.

By sprinkling DoEvents -- either standalone or within a FOR LOOP -- at places within the VB6 app, I've eventually been able to get the app to work much more reliably, but I'm not at all sure that I've handled all likely timing glitches.

Indeed, I don't understand why the problem exists even without the DoEvents.  I would expect that WORD receives all the msgs from my VB6 app via a single ordered queue, so how are things sometimes getting out of order?  Am I missing something?

Any ideas what is happening and what's required to fix it?

lennyhAsked:
Who is Participating?
 
Leigh PurvisConnect With a Mentor Database DeveloperCommented:
(I confess to disliking delays as a solution - as you mentioned yourself, what's to stop them varying from pc to pc).

You could go to http://www.ee-stuff.com to upload your document for viewing (I'll take a look).
0
 
Leigh PurvisDatabase DeveloperCommented:
I think you need to give a lot of detail on what you're doing.
The actual code involved - to see *how* it's being inserted into the Word document (let alone why it might be out of sequence).
0
 
lennyhAuthor Commented:
LPurvis, thank you for your response

Here is the code.  Much of it I pieced together from macro recordings.  Some, especially that related to the Selection and Range properties, is largely a mystery to me.  In spite of this, often it works fine.  But sometimes -- with the identical data -- I get the out of sequence condition.

Private Sub Command1_Click()
' Here's where we begin process of creating the WORD doc
   
    Command1.Caption = "Please Wait"

    'Start Word
    Set oWord = CreateObject("Word.Application")
    oWord.Visible = True
    Set oDoc = oWord.Documents.Add

    ' testdb is the main processing loop of retrieving info from the db and inserting it into the doc
    testDB    

    Form1.Hide
    MsgBox ("WORD Document Complete")
    End             '  All Done
End Sub

' here's the module
' Globals
Dim DBName As String
Dim AgencyName As String
Dim OrgDescription As String
Dim CareerOps As String
Dim Locations As String
Dim Requirements As String
Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim rs As Recordset
Dim db As Database

Sub DoHeading()
'Print Doc Heading
ActiveDocument.Paragraphs.Alignment = wdAlignParagraphCenter
 Selection.EndOf
    'oDoc.Paragraphs.Alignment = wdAlignParagraphCenter
    Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
    Selection.Font.Bold = True
    Selection.Font.Size = 26
    Selection.TypeText Text:="PROFILES OF PARTICIPATING EMPLOYERS"
  'Set myRange = ActiveDocument.Range(Start:=0, End:=0)
    'myRange.Font.Bold = True
    'myRange.Font.Size = 26
    'myRange.InsertBefore "PROFILES OF PARTICIPATING EMPLOYERS"
    Selection.TypeParagraph
    Selection.Font.Size = 11
    Selection.TypeText Text:="(DESCRIPTIONS SUBMITTED BY EMPLOYERS)"
    Selection.TypeParagraph
    Selection.TypeParagraph
End Sub


Sub jobfairtest()

' For ech database record, produce next portion of document
 Dim myRange As Range
 Dim aRange As Range
   
    Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
    Selection.Font.Bold = True
    Selection.Font.Size = 20
    With ActiveDocument.Bookmarks
        .Add Range:=Selection.Range, Name:="KeepStart"
        .DefaultSorting = wdSortByName
        .ShowHidden = False
    End With
    DoEvents
    Selection.TypeText Text:=AgencyName
    Selection.TypeParagraph
    Selection.MoveUp Unit:=wdLine, Count:=1
    Selection.ParagraphFormat.Shading.Texture = wdTexture20Percent
    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.TypeParagraph
    DoEvents
   
    Selection.Font.Bold = False
    Selection.Font.Size = 12
    Selection.TypeText Text:=OrgDescription

    tableset      'insert table

    With ActiveDocument.Bookmarks
        .Add Range:=Selection.Range, Name:="KeepEnd"
        .DefaultSorting = wdSortByName
        .ShowHidden = False
    End With
    DoEvents
    Set aRange = ActiveDocument.Range(Start:=ActiveDocument.Bookmarks("KeepStart").Start, End:=ActiveDocument.Bookmarks("KeepEnd").End)
    aRange.Paragraphs.keeptogether = True
    Set aRange = ActiveDocument.Range(Start:=ActiveDocument.Bookmarks("KeepStart").Start, End:=ActiveDocument.Bookmarks("KeepEnd").End)
    aRange.Paragraphs.KeepWithNext = True
    Set aRange = Nothing
    ActiveDocument.Bookmarks("KeepStart").Delete
    ActiveDocument.Bookmarks("Keepend").Delete
    Selection.Paragraphs.keeptogether = False
    Selection.Paragraphs.KeepWithNext = False
    Selection.TypeText Text:=" "
    Selection.TypeParagraph
End Sub

Public Sub testDB()
'Retrieve information from database to be inserted into document
Dim sql As String
Dim i As Double
DBName = CurDir() & "\SUJobFair.mdb"
DoHeading
Set db = OpenDatabase(DBName)
sql = "SELECT * FROM Registrations where datepart('yyyy',PayDate)=2006 order by agency"
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
Do Until rs.EOF            'do for each record
    'pick up info from database
    AgencyName = rs.Fields("agency")
    OrgDescription = CollapseParagraph(rs.Fields("OrganizationDescription"))
    CareerOps = CollapseParagraph(rs.Fields("PositionsHiring"))
    Locations = CollapseParagraph(rs.Fields("PositionLocations"))
    Requirements = CollapseParagraph(rs.Fields("PreferredMajors"))
   
    jobfairtest            'insert collected data into document
    For i = 1 To 60000
        DoEvents
    Next
    rs.MoveNext
Loop

ActiveWindow.ActivePane.View.ShowAll = False
rs.Close
db.Close
'ActiveDocument.SaveAs FileName:="jobfairtemp.doc", FileFormat:= _
        wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
        True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
        False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
End Sub

Function CollapseParagraph(str As String) As String
'Remove all CRLFs from data retrieved from database
Dim i As Integer
Dim NewStr As String
For i = 1 To Len(str)
    If Mid(str, i, 2) <> vbCrLf Then
        NewStr = NewStr & Mid$(str, i, 1)
    Else
        i = i + 1
    End If
Next
CollapseParagraph = NewStr
End Function

Sub tableset()
' Insert table in document
    ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=3, NumColumns:= _
        2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitWindow
    DoEvents
    With Selection.Tables(1)
        If .Style <> "Table Grid" Then
            .Style = "Table Grid"
        End If
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = True
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = True
    End With
    DoEvents

    'set first column width of table to 25%
    Selection.MoveDown Unit:=wdLine, Count:=2, Extend:=wdExtend
    Selection.Columns.PreferredWidthType = wdPreferredWidthPercent
    Selection.Columns.PreferredWidth = 25
    Selection.MoveUp Unit:=wdLine, Count:=1
    Selection.MoveDown Unit:=wdLine, Count:=1

    DoEvents

    ' fill in table
    'row 1 col 1
    Selection.Font.Bold = True
    Selection.TypeText Text:="Career Opportunities:"

    row 1 col 2
    Selection.MoveRight Unit:=wdCell
    Selection.Font.Bold = False
    Selection.TypeText Text:=CareerOps
    DoEvents

    row 2 col 1
    Selection.MoveRight Unit:=wdCell
    Selection.Font.Bold = True
    Selection.TypeText Text:="Location of Positions:"
    DoEvents

    row 2 col 2
    Selection.MoveRight Unit:=wdCell
    Selection.Font.Bold = False
    Selection.TypeText Text:=Locations
    DoEvents

    row 3 col 1
    Selection.MoveRight Unit:=wdCell
    Selection.Font.Bold = True
    Selection.TypeText Text:="Requirements:"
    DoEvents

    row 3 col 2
    Selection.MoveRight Unit:=wdCell
    Selection.Font.Bold = False
    If rs.Fields("AllMajorsConsidered") Then
        Selection.TypeText Text:="All Majors Considered. "
    End If
    Selection.TypeText Text:=Requirements
    DoEvents

    ' move out of table
    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.TypeParagraph
End Sub
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Leigh PurvisDatabase DeveloperCommented:
Is agency a unique field in your table/query?
So ordering by it - means that there's no ambiguity in the recordset?

Do the DoEvents make this work in the correct order as you'd want?
0
 
lennyhAuthor Commented:
Yes, agency is a unique field.  

Before I added the DoEvents loop in testDB (right before the rs.MoveNext), the app failed consistently.  If I remember correctly, even with a relatively low loop count (500) it still failed.  After bumping the loop count, it failed much less frequently.  After sprinkling the other DoEvents in the code, it performed even better.  Now it seems to fail approximately 1 out of 4 times.  I've tried it on 2 different machines with the same results.  That is, on both it usually it works, but sometimes it doesn't.  It often fails in the same place.  At one point (before adding the extra DoEvents), it typically failed when it printed the OrganizationDescription field for record n and immediately followed that with the OrganizationDescription Field for record n+1.  Then, a bit later down in the doc, the PositionsHiring field for record n (which should have immediately followed the OrganizationDescription field for record n) finally appeared.???   During the period when it was failing consistently, it always failed at the same point in this same way -- about half-way through the doc.
0
 
lennyhAuthor Commented:
One thing I suspected is that perhaps, under certain conditions, an error in moving the WORD insertion point is causing information from the n+1 record to overlay the information from the n record that had already been inserted in the WORD doc.  Unfortunately, my understanding of the selection and range objects is so sketchy that I really don't know how to more thoroughly scope that out.
0
 
lennyhAuthor Commented:
Another point: Isn't there just a single queue of msgs between the VB6 app and WORD?  If this is the case, how could there ever be a mis-ordering?  I assume that queue can't overflow in any way.  But if this is the case, why does the DoEvents help?
0
 
Leigh PurvisDatabase DeveloperCommented:
Well, for the most part you're right - it could do with a cleanup.
A lot of it relies on cursor movement - which is very much of the ilk of recorded macros.
However - you say it can work - it just doesn't always.
So, for now - just try this version of essentially the same commands.
(Major alterations could come later).

' Globals
Dim DBName As String
Dim AgencyName As String
Dim OrgDescription As String
Dim CareerOps As String
Dim Locations As String
Dim Requirements As String
Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim rs As Recordset
Dim db As Database

Sub DoHeading()
'Print Doc Heading
With oWord
    .ActiveDocument.Paragraphs.Alignment = wdAlignParagraphCenter
 Selection.EndOf
    'oDoc.Paragraphs.Alignment = wdAlignParagraphCenter
    With .Selection
          .ParagraphFormat.Alignment = wdAlignParagraphCenter
          .Font.Bold = True
          .Font.Size = 26
          .TypeText Text:="PROFILES OF PARTICIPATING EMPLOYERS"
        'Set myRange = ActiveDocument.Range(Start:=0, End:=0)
          'myRange.Font.Bold = True
          'myRange.Font.Size = 26
          'myRange.InsertBefore "PROFILES OF PARTICIPATING EMPLOYERS"
          .TypeParagraph
          .Font.Size = 11
          .TypeText Text:="(DESCRIPTIONS SUBMITTED BY EMPLOYERS)"
          .TypeParagraph
          .TypeParagraph
    End With
End With
End Sub


Sub jobfairtest()

' For ech database record, produce next portion of document
 Dim myRange As Range
 Dim aRange As Range
 
With oWord
    With .Selection
          .ParagraphFormat.Alignment = wdAlignParagraphLeft
          .Font.Bold = True
          .Font.Size = 20
    End With
    With .ActiveDocument.Bookmarks
        .Add Range:=Selection.Range, Name:="KeepStart"
        .DefaultSorting = wdSortByName
        .ShowHidden = False
    End With
    DoEvents
    With .Selection
          .TypeText Text:=AgencyName
          .TypeParagraph
          .MoveUp Unit:=wdLine, Count:=1
          .ParagraphFormat.Shading.Texture = wdTexture20Percent
          .MoveDown Unit:=wdLine, Count:=1
          .TypeParagraph
          DoEvents
   
          .Font.Bold = False
          .Font.Size = 12
          .TypeText Text:=OrgDescription
    End With

    tableset     'insert table

    With .ActiveDocument.Bookmarks
        .Add Range:=Selection.Range, Name:="KeepEnd"
        .DefaultSorting = wdSortByName
        .ShowHidden = False
    End With
    DoEvents
    Set aRange = .ActiveDocument.Range(Start:=.ActiveDocument.Bookmarks("KeepStart").Start,

End:=.ActiveDocument.Bookmarks("KeepEnd").End)
    aRange.Paragraphs.keeptogether = True
    Set aRange = .ActiveDocument.Range(Start:=.ActiveDocument.Bookmarks("KeepStart").Start,

End:=.ActiveDocument.Bookmarks("KeepEnd").End)
    aRange.Paragraphs.KeepWithNext = True
    Set aRange = Nothing
    .ActiveDocument.Bookmarks("KeepStart").Delete
    .ActiveDocument.Bookmarks("Keepend").Delete
    With .Selection
          .Paragraphs.keeptogether = False
          .Paragraphs.KeepWithNext = False
          .TypeText Text:=" "
          .TypeParagraph
    End With
End With
End Sub

Public Sub testDB()
'Retrieve information from database to be inserted into document
Dim sql As String
Dim i As Double

DBName = CurDir() & "\SUJobFair.mdb"
DoHeading
Set db = OpenDatabase(DBName)
sql = "SELECT * FROM Registrations where datepart('yyyy',PayDate)=2006 AND agency Is Not Null order by agency"
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF          'do for each record
    'pick up info from database
    AgencyName = rs.Fields("agency")
    OrgDescription = CollapseParagraph(rs.Fields("OrganizationDescription"))
    CareerOps = CollapseParagraph(rs.Fields("PositionsHiring"))
    Locations = CollapseParagraph(rs.Fields("PositionLocations"))
    Requirements = CollapseParagraph(rs.Fields("PreferredMajors"))
   
    jobfairtest          'insert collected data into document

    'For i = 1 To 60000 you're finding this necessary?  60,000 DoEvents is madness surely :-S
        DoEvents
    'Next
    rs.MoveNext
Loop

oWord.ActiveWindow.ActivePane.View.ShowAll = False
rs.Close
db.Close

'oWord.ActiveDocument.SaveAs FileName:="jobfairtemp.doc", FileFormat:= _
        wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
        True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
        False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
End Sub


Function CollapseParagraph(varVal) As String
'Remove all CRLFs from data retrieved from database

If IsNull(varVal) Then varVal = ""

CollapseParagraph = Replace(CStr(varVal),vbCrLf,"")

End Function


Sub tableset()
' Insert table in document
With oWord
    .ActiveDocument.Tables.Add Range:=.Selection.Range, NumRows:=3, NumColumns:= _
        2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitWindow
    DoEvents
    With .Selection.Tables(1)
        If .Style <> "Table Grid" Then
            .Style = "Table Grid"
        End If
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = True
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = True
    End With
    DoEvents

    'set first column width of table to 25%
    With Selection
          .MoveDown Unit:=wdLine, Count:=2, Extend:=wdExtend
          .Columns.PreferredWidthType = wdPreferredWidthPercent
          .Columns.PreferredWidth = 25
          .MoveUp Unit:=wdLine, Count:=1
          .MoveDown Unit:=wdLine, Count:=1

          DoEvents

          ' fill in table
          'row 1 col 1
          .Font.Bold = True
          .TypeText Text:="Career Opportunities:"

          'row 1 col 2
          .MoveRight Unit:=wdCell
          .Font.Bold = False
          .TypeText Text:=CareerOps
          DoEvents

          'row 2 col 1
          .MoveRight Unit:=wdCell
          .Font.Bold = True
          .TypeText Text:="Location of Positions:"
          DoEvents

          'row 2 col 2
          .MoveRight Unit:=wdCell
          .Font.Bold = False
          .TypeText Text:=Locations
          DoEvents

          'row 3 col 1
          .MoveRight Unit:=wdCell
          .Font.Bold = True
          .TypeText Text:="Requirements:"
          DoEvents

          'row 3 col 2
          .MoveRight Unit:=wdCell
          .Font.Bold = False
          If rs.Fields("AllMajorsConsidered") Then
              .TypeText Text:="All Majors Considered. "
          End If
          .TypeText Text:=Requirements
          DoEvents

          ' move out of table
          .MoveDown Unit:=wdLine, Count:=1
          .TypeParagraph
    End With
End With
End Sub
0
 
lennyhAuthor Commented:
Thank you for the updated version.

I ran your version 3 times.  Each time it failed in the identical manner and in the identical spot. (Is there a way I can send you a copy of the generated WORD doc showing the error? It clearly demonstrates the "out-of-sequence" situation.) The error was not the same as the one I had previously been occasionally getting, but it was the same type.  I guess the fact that the error now appears solid and repeatable is some progress.

I then uncommented the 60,000 For loop surrounding the one DoEvents statement (Yes, the 60000 seems a bit much). I reran the test another 3 times.  Each time it ran perfectly.??

I await any further insights.
0
 
lennyhAuthor Commented:
At the risk of being acused of belaboring the obvious, I've formulated a couple alternative hypotheses:

1) The document items are appearing out of sequence because of some race conditions between the 2 components that cause commands from VB to be executed by WORD in an out of sequence order. I no longer favor this explanation -- particularly since I've found no reference or comments about the existence of such a race condition.

2.Commands are being received in order, but something is causing the insertion point to be moved back over already generated verbiage.  This currently is my more favored explantion.  Particularly since the app is using the Selection objection (of which I guess there can be multiple), perhaps -- in some cases -- multiple selection objects are existing simultaneously (for a short period) causing the insertion point to be moved erroneously relative to an "older" selection object.  After a period of time the multiple selection disappears and that is why the long Doevents helps.

Just a theory.  Any thoughts.  If this seems reasonable perhaps using range rather than selection would improve things (Now if I only really knew how to use range??)
0
 
Leigh PurvisDatabase DeveloperCommented:
Generally speaking - it is better (more controlled) using a range object that the selection and type text.
However - you need to move around the document in a more controlled fashion also - and requires a bit of a rewrite.

I'll have a look at this a bit later on - see what I can come up with.

What seems so odd is that it needs this huge processing catchup.  Is the process not all but done when you fire the DoEvents loop?
Do you fire this routine in rapid succession?
0
 
Leigh PurvisDatabase DeveloperCommented:
Incidentally - is it now totally reliable albeit using the DoEvents?
(I hate the idea of so many - or even sat looping though any).

You could try something like this API to create a break in execution to allow Word to complete processing...
http://www.mvps.org/access/api/api0021.htm

See if that does the job too.
0
 
lennyhAuthor Commented:
Thank you for your comments.

Regarding reliability, I ran the app another 3 times this morning (total of 6), and again it ran perfectly (again with the 60000 FOR loop).  Whether it would continue to run properly in another environment (different PC, different RAM, etc.) is, of course, an open question since there is clearly something not completely predictable going on.

Regarding the big For loop, although it is real clumsy, this app would be run infrequently, and the wasted cycles are not at all a consideration.  Understanding why it's required (and if it is a 100% fix) is the bigger concern.

Regarding the firing of the Doevents loop, I'm not certain I understand your question.  The loop is fired prior to requesting the next record from the database.  The db is not large.  There are about 100 records in the db and the SQL statemnet retrieves about 1/2 of them.  (2 years of data in the db; only 1 year's worth requested via the SQL).
0
 
lennyhAuthor Commented:
I  tried the Sleep API with a value of 500 and 1000.  In both cases, the app failed -- again with the out of sequence condition.  The 1000  case, especially, injected a greater delay than the 60000 For loop, so the inclusion of the DoEvents (rather than just a delay) appears necessary (at least with the current code).

BTW, in all failing cases, the app generates a runtime error '4608' Value out of range on the first "set arange" statement after the tableset call.  I don't believe this is the cause of the problem.  Rather, I believe this is merely a fall-out of the out-of-sequence condition.
0
 
lennyhAuthor Commented:
I have uploaded docs.zip to:
https://filedb.experts-exchange.com/incoming/ee-stuff/33-docs.zip

It contains 2 doc files.  Each represents the first few pages of the app output: good1.doc and err1.doc.  When you compare the 2 you will see how verbiage from the ending record is being overlayed into the verbiage associated with the immediately preceding.  In this particular case, information is being overlayed into a column of the table associated with the previous record.  Note, however that the failures are not necessarily associated with the tables.  Typically the failures do not show up in the tables, but rather in other out-of-sequence situations.
0
 
lennyhAuthor Commented:
Some Success: By including a couple  break points, I was able to establish that, under some circumstances, the insertion point was being moved (or left) at a point that was not the end of the document.  I forced the insertion point to the end of document with a "Selection.EndOf Unit:=wdStory, Extend:=wdMove" statement at 3 places in the app (after the agency name was sent to word and before and after tableset).  Now I've run it a number of times (w/o the FOR loop) and it has worked perfectly. I'm not at all sure what it is that caused the insertion point to mess up, or why the big DoEvents loop vastly improved the situation, but for the time being, I'm happy.

Thank you for all your help.
0
 
lennyhAuthor Commented:
As an additional test, I eliminated all DoEvents statements, and it still works perfectly.
0
 
Leigh PurvisDatabase DeveloperCommented:
Hi

Glad you're sorted and working (and glad that DoEvents loop has gone ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.