#1
|
|||
|
|||
Search-all-files-in-a-folder - Userform opens attached to wrong document
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? Code:
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 Last edited by John 4; 12-14-2020 at 08:07 PM. |
#2
|
|||
|
|||
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 Code:
wdoc.activate Code:
Application.ScreenUpdating=True Note, I have not tried this but it should not hurt. |
#3
|
|||
|
|||
I had already tried that. No difference unfortunately.
Thanks anyway Charles |
#4
|
|||
|
|||
John,
It is all tied to when you initilize the userform. Try: Code:
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
Glad to help. Not brilliance. Just experience.
|
#7
|
|||
|
|||
For the benefit of other beginners like myself who can struggle greatly with these things, here’s the full code:
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 Code:
Private Sub Button1_Continue_Click() Call MyFunction1 (x) End Sub Private Sub Button2_Exit_Click() Call MyFunction2 (x) Unload Me End Sub I’ve also retained Paul Edstein’s use of “StrDocNm” because it includes the line: Code:
If strFolder & "\" & strFile <> strDocNm Then 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 |
#8
|
|||
|
|||
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: Code:
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 Code:
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 |
#9
|
|||
|
|||
Thank you Greg,
What's the purpose of the three lbl_exit(s) in the Userform code? |
#10
|
|||
|
|||
Nothing really in those cases. Just my style. I never like to hit the End sub.
|
#11
|
|||
|
|||
That's a coincidence, we just got a new dog called Max
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 |
#12
|
|||
|
|||
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? |
#13
|
|||
|
|||
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Extract Document Property and insert it into the same document, for many files in a folder | MisrIdley | Word VBA | 1 | 12-07-2017 12:41 PM |
VBA Word - Search Within Files Containing A String - Copy Files to New Folder | jc491 | Word VBA | 0 | 01-09-2016 12:00 PM |
Outlook 2010: Saving attachments opens up wrong windows folder to save in | jeroen | Outlook | 0 | 09-29-2015 01:51 AM |
Music is attached to wrong animation | musbegin | PowerPoint | 2 | 11-17-2014 02:40 AM |
VBA Code in a UserForm module to delete a Command Button which opens the userform | Simoninparis | Word VBA | 2 | 09-21-2014 03:50 AM |