Search-all-files-in-a-folder - Userform opens attached to wrong document

[John 4]

I’m trying to write a code (or rather add to Macropod’s code) that will search through files in a given folder, and if the search is successful a Userform opens asking whether I want to stop the macro (i.e. because what has been found looks relevant), or continue the search (because what has been found looks irrelevant). The code enters a ‘Do-nothing/DoEvents’ loop while awaiting an answer from the Userform. It works fine except that when the Userform opens it is ‘attached’ to the document that was active at the time of starting the macro, instead of attached to the document in which was the successful ‘Find’.

This certainly doesn’t make the macro useless, but it is annoying. I’ve tried making the wdDoc (in which is the successful ‘Find’) the Active document before the Userform is called, but it doesn’t make any difference – the Userform still opens ‘attached’ to the document that was open when the macro was started (i.e. StrDocNm). Obviously I would prefer the document that appears on screen in the event of a successful find be the wdDoc, with the selection highlighted, not the (usually completely irrelevant) document that was open at the time of starting the code.



It also means that if I minimise that irrelevant document (so that I can look at the target document), the Userform disappears from view. All of this can tolerated if necessary, but naturally I would prefer if it worked better. Any ideas?

Sub AllFolderDocs_Search()
  'So the user doesn't get an epileptic fit with all the screen changes,
  'though there is still quite a bit of changing, just not as much:
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, strDocNm As String, wdDoc As Document
Dim UF3 As Uform_PauseCode
Set UF3 = Uform_PauseCode
strDocNm = ActiveDocument.FullName: strFolder = GetFolder

If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.docx", vbNormal)
While strFile <> ""
  If strFolder & "\" & strFile <> strDocNm Then
    Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False)
    
    With wdDoc
          'Call your other macro or insert its code here, i.e:
      With Selection.Find
        .Text = "My search"
          'etc…
      End With
      Selection.Find.Execute
        
      If Selection.Find.Found Then
  'So I can see and work on the doc during the DoEvents loop b4
  'I decide whether to continue the search or exit the macro:
          Application.ScreenUpdating = True
          UF3.Show vbModeless
            Do
              DoEvents
            Loop
          Application.ScreenUpdating = False
      End If

    End With
        DoEvents
    wdDoc.Close SaveChanges:=True
  End If
  strFile = Dir()
Wend
Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub
'====================

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

[Charles Kenyon]

First, macropod (Paul Edstein) is a far better and more experienced coder than am I and will probably be answering here. He is a busy man, though.
It occurs to me that you might want

wdoc.activate

before

Application.ScreenUpdating=True

Note, I have not tried this but it should not hurt.

[John 4]

I had already tried that. No difference unfortunately.

Thanks anyway Charles

[gmaxey]

John,


It is all tied to when you initilize the userform. Try:

Sub AllFolderDocs_Search()
Dim strFolder As String, strFile As String
Dim oFrm As Uform_PauseCode
Dim wdDoc As Document
Dim oRng As Range
  Application.ScreenUpdating = False
  strFolder = GetFolder
  If strFolder = "" Then Exit Sub
  strFile = Dir(strFolder & "\*.doc*", vbNormal)
  While strFile <> ""
    Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False)
    With wdDoc
      .Activate
      Set oRng = .Range
      With oRng.Find
        .Text = "my search"
        If .Execute Then
          Application.ScreenUpdating = True
          Set oFrm = New Uform_PauseCode
          oFrm.Show vbModeless
          Do
            DoEvents
          Loop
          Application.ScreenUpdating = False
        End If
      End With
      DoEvents
      .Close SaveChanges:=True
    End With
    Set wdDoc = Nothing
    strFile = Dir()
  Wend
  Application.ScreenUpdating = True
lbl_Exit:
  Exit Sub
End Sub


Function GetFolder() As String
Dim oFolder As Object
  GetFolder = ""
  Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
  If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
  Set oFolder = Nothing
lbl_Exit:
  Exit Function
End Function

[John 4]

That's brilliant Greg - problem sorted

I've read your help pages on Userforms should i should have suspected something like that. But the various 'Instances', methods of loading and unloading, calling, initializing and activating etc, is all fairly confusing for a relative beginner.

Thank you

[gmaxey]

Glad to help. Not brilliance. Just experience.

[John 4]

For the benefit of other beginners like myself who can struggle greatly with these things, here’s the full code:

Option Explicit
Public x As Integer

Sub AllFolderDocs_Search()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, strDocNm As String, wdDoc As Document
Dim UF3 As Uform_PauseCode
strDocNm = ActiveDocument.FullName: strFolder = GetFolder

If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.docx", vbNormal)
While strFile <> ""
x = 0
'Change the following <> to >= to pick up from
'where the previous search left off:
    If strFolder & "\" & strFile <> strDocNm Then
    Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False)
    
    With wdDoc
        'Call your other macro or insert its code here, i.e.:
      With Selection.Find
        .Text = "My search"
	‘etc…
      End With
      Selection.Find.Execute

      If Selection.Find.Found Then
          Application.ScreenUpdating = True
          Set UF3 = New Uform_PauseCode
          UF3.Show vbModeless
            Do
              DoEvents
              If x = 1 Then Exit Do 'and continue the search
              If x = 2 Then Exit Sub
            Loop

          Application.ScreenUpdating = False
         End If
    End With
        DoEvents
    wdDoc.Close SaveChanges:=True
  End If
  strFile = Dir()
Wend
Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub

'-----------------------------

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

'-----------------------------

Function MyFunction1(x As Integer) As Integer
  x = 1
End Function

Function MyFunction2(x As Integer) As Integer
  x = 2
End Function

And here’s the code for the two Userform buttons:

Private Sub Button1_Continue_Click()
  Call MyFunction1 (x)
End Sub

Private Sub Button2_Exit_Click()
  Call MyFunction2 (x)
  Unload Me
End Sub

Call the Functions and Userform command buttons whatever you like, and you may prefer to use a less common variable name than “x” seeing as it’s a public variable (but x will still work fine). Greg will notice that I’ve retained the Selection object for the search rather than use the Range object. I think beginners like myself will usually find the Selection object much easier to work with because you can see what’s going on if you want to step through the code with the F8 key.

I’ve also retained Paul Edstein’s use of “StrDocNm” because it includes the line:

If strFolder & "\" & strFile <> strDocNm Then

If you want to continue the search from where you left off, then have that document (which contained the successful search when you exited the code) as the Active document when you run the code again, and change the <> in the above line to >=

Greg, Paul, Andrew, or Greg Mayor could probably provide a few lines of code to determine whether the active document is within the search folder and ask the user if he wants to continue from there or search the entire folder. But as it is, it’s not too big a deal to make the above small change to the code instead.

Happy Christmas

[gmaxey]

John,


Personally, I think the sooner you learn to use ranges vice selection, the better off you will be. Whichever you use, in my experience, just "If Selection.Find.Execute ..." works just as well as "Selection.Find.Execute followed by If Selection.Find.Found ..."


The latter for some reason is a practice of Paul's which I don't understand but also feel he will never change. To each his own. You don't need the public variable "x" at all or the two functions. Just use the form tag property:

Sub AllFolderDocs_Search()
Dim strFolder As String, strFile As String, strDocNm As String, wdDoc As Document
Dim oFrm As Uform_PauseCode
  Application.ScreenUpdating = False
  strDocNm = ActiveDocument.FullName
  strFolder = GetFolder
  If strFolder = "" Then Exit Sub
  strFile = Dir(strFolder & "\*.doc*", vbNormal)
  While strFile <> ""
    'Change the following <> to >= to pick up from where the previous search left off:
    If strFolder & "\" & strFile <> strDocNm Then
      Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False)
      With Selection.Find
        .Text = "Dogs"
        If .Execute Then
          Application.ScreenUpdating = True
          Set oFrm = New Uform_PauseCode
          With oFrm
            .Show vbModeless
            Do
              DoEvents
              Select Case .Tag
                Case Is = "Continue"
                  Unload oFrm
                  Exit Do
                Case Is = "Exit"
                  Unload oFrm
                  GoTo lbl_Exit
              End Select
            Loop
          End With
          Application.ScreenUpdating = False
        End If
      End With
      DoEvents
      wdDoc.Close SaveChanges:=True
    End If
    strFile = Dir()
  Wend

 lbl_Exit:
  Set oFrm = Nothing: Set wdDoc = Nothing
  Application.ScreenUpdating = True
   Exit Sub
End Sub

Function GetFolder() As String
Dim oFolder As Object
  GetFolder = ""
  Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
  If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
  Set oFolder = Nothing
lbl_Exit:
  Exit Function
End Function

and:

Option Explicit
Private Sub Button1_Continue_Click()
  Tag = "Continue"
lbl_Exit:
  Exit Sub
End Sub

Private Sub Button2_Exit_Click()
  Tag = "Exit"
lbl_Exit:
  Exit Sub
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = 0 Then
    Cancel = True
    Button2_Exit_Click
  End If
lbl_Exit:
  Exit Sub
End Sub

[John 4]

Thank you Greg,
What's the purpose of the three lbl_exit(s) in the Userform code?

[gmaxey]

Nothing really in those cases. Just my style. I never like to hit the End sub.

[John 4]

Originally Posted by gmaxey

With Selection.Find
    .Text = "Dogs"

That's a coincidence, we just got a new dog called Max

Originally Posted by gmaxey

Nothing really in those cases.

That's what I thought. Of course, scattering completely unnecessary bits of code every few lines is only going to lead to confusion for people. And I know you wouldn't want that.

If you were to ask any beginner (or probably intermediate either) whose code from the examples above they'd rather work with, yours or mine, I'd be confident of the answer, even though yours is more efficient. Due to your use of obscure coding.

I'm going to have to sign off for the year. Thanks once again; yourself, Charles, Andrew, Paul and Greg Mayor; you've helped me out on a number of occasions over the last months when frustration and confusion got the better of me

[gmaxey]

John,


No offense and as we are simply sharing opinions, I don't wish you to take offense either. We each have our own styles and I don't know which code another user might prefer. However, lbl_Exit: is simply a label and Exist Sub is a relatively common and often used line of code. In fact, you used it in your example. Neither, would I call "obscure."

As a standard practice (in fact I have a autocomplete function) to start all procedures that I write) like this:



Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey


lbl_Exit:
Exit Sub
End Sub



I then give is a name and go from there. Now if I wrote:
Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Msgbox "Hello John"

lbl_Exit:
Exit Sub
End Sub


Then understandably someone might ask, "What is the purpose of the lbl_Exit: and Exit sub lines. Well, with my style, I always Exit Sub. That is the purpose of that line and again in this case, other than style, lbl_Exit has no purpose.

Now, let's consider your use of Exit Sub. In your procedure, you have the following two lines:

Set wdDoc as Nothing
Application.ScreenUpdating = True


Now I assume that since you included those two lines, that you wanted to execute them. However, you also use Exit Sub above those lines so if Exit Sub runs then those two lines are bypassed. A novice, intermediate or even wizard might wander why you wrote those lines if you don't want them to execute? So using my style, I would include those two lines after the lbl_Exit line

lbl_Exit:
Set wdDoc = Nothing
Application.ScreenUpdating = True
Exit Sub

End Sub


and use GoTo lbl_Exit where you used Exit Sub.


Or when my procedures have an error handler I might do something like this:
Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oDoc as Document
Set oDoc = ActiveDocument

On Error GoTo Err_Demo
Err.Raise 6

lbl_Exit:
Set oDoc = Nothing

Exit Sub
Err_Demo:
Resume lbl_Exit

End Sub


Make sense?

[John 4]

Hi Greg, I hate to duck out but it's gotten too late in the year for debates; like i said, I'm retiring.

I accept your point of course that I should've had a label before "Set wdDoc as Nothing" instead of exiting the sub from the Do Loop. An oversight that I'll correct.

Thank you, and Happy Christmas to everyone