Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opening Word/Search (Office 2003)

    I have some code that is within MS Access 2003. It has two listboxes that get cycled. The first listbox contains a persons name. The second listbox contains word document names. I am trying to cycle through the names list, open up the relevant documents in the documents list, and log those that have a match. I have it working but its very slow. Any ideas of a faster method, thanks.

    For TY2 = 0 To Me.Athletes.ListCount - 1
    Me.Athletes.SetFocus
    Me.Athletes.ListIndex = TY2
    LWordDocB = Me.Athletes.Value

    For TY = 0 To Me.DocsList.ListCount - 1
    Me.DocsList.SetFocus
    Me.DocsList.ListIndex = TY
    LWordDocA = Me.DocsList.Value


    LWordDoc = "c:LoggingFootball" & LWordDocA

    Set WD = CreateObject(Class:="Word.Application")
    WD.Visible = False 'True

    'Open the Document
    WD.Documents.Open Filename:=LWordDoc, ReadOnly:=True

    WD.Selection.Find.ClearFormatting
    With WD.Selection.Find
    .Text = LWordDocB
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = True
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False

    End With


    WD.Selection.Find.Execute


    If WD.Selection.Find.Found Then
    rsDump.AddNew
    rsDump!Athlete = LWordDocB
    rsDump!Document = Me.DocsList.Value
    rsDump.Update

    Else
    ' Me!FOUNDIT.AddItem "NOT FOUND"
    End If
    DoEvents

    WD.Quit
    Me.results.Requery
    Next
    Next

    Set WD = Nothing

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening Word/Search (Office 2003)

    You start and quit Word for each document, that takes a lot of time. It's more efficient to start Word at the beginning of the code, open and close each document in turn, then quit Word at the end. See the following code; I have also changed the beginning so that you don't have to select items in the list boxes.
    <code>
    Set WD = CreateObject(Class:="Word.Application")
    For TY2 = 0 To Me.Athletes.ListCount - 1
    LWordDocB = Me.Athletes.ItemData(TY2)
    For TY = 0 To Me.DocsList.ListCount - 1
    LWordDocA = Me.DocsList.ItemData(TY)
    LWordDoc = "c:LoggingFootball" & LWordDocA
    'Open the Document
    Set doc = WD.Documents.Open(FileName:=LWordDoc, ReadOnly:=True)
    With WD.Selection.Find
    .ClearFormatting
    .Text = LWordDocB
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = True
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    If .Execute Then
    rsDump.AddNew
    rsDump!Athlete = LWordDocB
    rsDump!Document = Me.DocsList.Value
    rsDump.Update
    Else
    ' Me!FOUNDIT.AddItem "NOT FOUND"
    End If
    End With
    doc.Close SaveChanges:=False
    DoEvents
    Me.Results.Requery
    Next TY
    Next TY2
    WD.Quit
    Set WD = Nothing
    </code>
    The code uses an extra variable doc of type Word.Document.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening Word/Search (Office 2003)

    Many, Many thanks Hans for taking the time doing that. You certainly did a good job speeding the operation up. There should be an awards star system on this site to highlight appreciated thanks. Have a good weekend and thanks again

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening Word/Search (Office 2003)

    Your kind words are thanks enough! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •