Consulting

Page 2 of 5 FirstFirst 1 2 3 4 ... LastLast
Results 21 to 40 of 98

Thread: Solved: Search engine

  1. #21
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi Airborne,

    I've just tested the code using the workbook you provided, and the code pulled from posts by DRJ and mdmackillop above (inlcluding md's change re the text vs textbox).

    I'm running XL2003 on Win XP also, and the code fires for me just fine.

    Just to check... (I tried to follow the thread, but there's a lot to read) You are firing this code from a workbook different than the one you attached here, with the attached version being the target that you're searching?

    I have a few of questions for you on your target wb's:
    -Are they created from a template, or do users create them on their own?
    -How are you creating the textboxes in the sheets? (From Control toolbox or somewhere else?)
    -What is your code supposed to do when it finds a match? Currently it seems to do a debug.print on the NEXT file in the list, but does nothing with the match
    -FYI You also have a potential issue in that your code calls:
    [vba] Do Until FileName = ""
    On Error Resume Next
    Set WB = Workbooks.Open(FileName:=Path & "\" & FileName, ReadOnly:=True, Password:="DRJWasHere")
    On Error Goto 0
    'Cannot Open Workbook
    If WB Is Nothing Then
    Goto NextLoop:

    'More code here

    NextLoop:
    If Test = False Then WB.Close False
    Set WB = Nothing
    Set WS = Nothing
    FileName = Dir()
    Debug.Print FileName
    Loop [/vba]

    The issue is that if WB is nothing, you can't close it, so it will error out. This is also the section that led into the question about what's supposed to happen too. I would have expected to see the debug.print line above the set WB=nothing, but then again, I'm not really sure of what you're after.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  2. #22
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Re post above...

    Please check here http://www.vbaexpress.com/forum/showthread.php?t=1213. You may want to try coding around an OLEObject instead of a shapes object. I don't know if this is the issue or not, but I ran into error at the same place you did when trying to use a textbox created from the Control Toolbox. (The posted code worked fine with the file you suppliedthough, which is the reason for my question above about who is creating and how.) My gut leads me there...

    I don't think (although could be proven wrong) that it is a version issue, as usually code created in lower versions should run without issue in later versions. Since your version is higher than md's, it should be okay.

    FYI, I'll be in a seminar all day tomorrow, but will try and check tomorrow night to see what progress has been made.

    Cheers,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  3. #23
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Hello, sorry for the late reply.
    To answer your questions....

    -Are they created from a template, or do users create them on their own?
    The sheets are created from a template.
    The two sheets are copied and saved in a workbook with the name format ddmmjjj. The sheets in the template already have the textboxes and we just type our comments in the textboxes.

    -How are you creating the textboxes in the sheets? (From Control toolbox or somewhere else?)
    The textboxes were created from the Drawing bar, you can insert textboxes.

    -What is your code supposed to do when it finds a match? Currently it seems to do a debug.print on the NEXT file in the list, but does nothing with the match
    You are right but at the end of the day I just want to find a word in the textboxes of the saved files. In one of the files someone typed "K100 loaded". Now if for some reason we want to know when "K100" was loaded it would be a lot of work to open 30 files. My wish is to use the search engine which will search through the files and then only open the file where the word "K100" is typed in one of the textboxes.

    Thanks for the help.

  4. #24
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi Airborne.

    Sorry for the late reply as well. I've gone through and tested your code, and it all seems to run just fine for me, with the exceptions I already noted in previous posts. Here's the way I tested it, so tell me if anything is different:

    -Made a new blank workbook and copied in the code from the previous posts. Saved this file as "J:\VBA Tests\Airborne.xls"
    -Downloaded an saved your file to "J:\Data15-10-2004.xls"
    (I originally put "Airborne" in the same directory as the data file, but that caused me some issues as it ended up trying to open itself.)

    -Stepped through the code (in "J:\VBA Tests\Airborne.xls") searching files in "J:\" for the following text strings:
    -test
    -We type
    -Search for
    -Every one returned results as expected by the code.

    I don't know what else to tell you except that I have posted the full code I used to test (that I assembled from various posts), and have also attached a screenshot of the references that I have set to run this. You may want to check those out as well. (Tools | References in the VBE)

    I did check all of the textboxes, and they are certainly all shapes, so that seems okay.

    Otherwise, maybe it has something to do with the code placement? Where is the code located in your workbook (what module), and what workbook is it that actually holds the code to run it? Are there any other VBA routines in there that could be causing the conflict? A routine called BoxText, for example? Are any other files open at the time? (I'm shooting in the dark here... )

    [vba]Option Compare Text
    Option Explicit

    Private Const BIF_RETURNONLYFSDIRS As Long = &H1
    Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
    Private Const BIF_RETURNFSANCESTORS As Long = &H8
    Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
    Private Const BIF_BROWSEFORPRINTER As Long = &H2000
    Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
    Private Const MAX_PATH As Long = 260

    Type BrowseInfo
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszINSTRUCTIONS As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
    End Type

    Type SHFILEOPSTRUCT
    hwnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAnyOperationsAborted As Boolean
    hNameMappings As Long
    lpszProgressTitle As String
    End Type

    Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
    ByVal pidl As Long, _
    ByVal pszBuffer As String) As Long
    Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
    lpBrowseInfo As BrowseInfo) As Long

    Function BrowseFolder(Optional Caption As String = "") As String

    Dim BrowseInfo As BrowseInfo
    Dim FolderName As String
    Dim ID As Long
    Dim Res As Long

    With BrowseInfo
    .hOwner = 0
    .pidlRoot = 0
    .pszDisplayName = String$(MAX_PATH, vbNullChar)
    .lpszINSTRUCTIONS = Caption
    .ulFlags = BIF_RETURNONLYFSDIRS
    .lpfn = 0
    End With
    FolderName = String$(MAX_PATH, vbNullChar)
    ID = SHBrowseForFolderA(BrowseInfo)
    If ID Then
    Res = SHGetPathFromIDListA(ID, FolderName)
    If Res Then
    BrowseFolder = Left$(FolderName, InStr(FolderName, _
    vbNullChar) - 1)
    End If
    End If

    End Function
    Sub FindAll()

    Dim WB As Workbook
    Dim WS As Worksheet
    Dim Cell As Range
    Dim Prompt As String
    Dim Title As String
    Dim search As String
    Dim FindCell() As String
    Dim FindSheet() As String
    Dim FindWorkBook() As String
    Dim FindPath() As String
    Dim FindText() As String
    Dim Counter As Long
    Dim FirstAddress As String
    Dim Path As String
    Dim MyResponse As VbMsgBoxResult
    Dim FileName As String
    Dim test As Boolean
    Dim MyWS
    Dim shp
    Dim i As Integer
    Dim boxtext As String

    '*** Get folder from user ***
    ' Prompt = "Select the folder with the files that you want to search through." & _
    ' vbNewLine & vbNewLine & "Note: Subfolders will not be searched through."
    Title = "Folder Selection"
    ' MsgBox Prompt, vbInformation, Title

    '*** This code works with XP only and is also used to pick a folder ***
    'Application.FileDialog(msoFileDialogFolderPicker).Show
    'Path = CurDir

    Path = BrowseFolder("Select A Folder")
    If Path = "" Then
    Prompt = "You didn't select a folder. The procedure has been canceled."
    Title = "Procedure Canceled"
    MsgBox Prompt, vbCritical, Title
    GoTo Canceled:
    End If

    Prompt = "What do you want to search for in the folder: " & vbNewLine & vbNewLine & Path
    Title = "Search Criteria Input"
    search = InputBox(Prompt, Title)
    If search = "" Then
    GoTo Canceled
    End If

    '*** Confirm the procedure before continuing ***
    ' Prompt = "Are you sure that you want to search all the files in the folder:" & _
    ' vbCrLf & Path & " for " & """" & Search & """" & "?"
    Title = "Confirm Procedure"
    ' MyResponse = MsgBox(Prompt, vbQuestion + vbYesNo, Title)
    ' If MyResponse = vbNo Then
    ' GoTo Canceled:
    ' End If

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    '*** Loop through all Word documents and search each of them for the specified criteria***
    FileName = Dir(Path & "\*.xls", vbNormal)

    Do Until FileName = ""
    On Error Resume Next
    Set WB = Workbooks.Open(FileName:=Path & "\" & FileName, ReadOnly:=True) ', Password:="DRJWasHere"
    On Error GoTo 0
    'Cannot Open Workbook
    If WB Is Nothing Then
    GoTo NextLoop: 'PROBLEM HERE
    End If
    On Error Resume Next
    Set WS = WB.Sheets("Data")
    On Error GoTo 0
    If WS Is Nothing Then
    WB.Close False
    GoTo NextLoop:
    End If
    Set MyWS = WB.Sheets("Data")
    test = False
    'Check the values in the Text Boxes here
    For i = 1 To MyWS.Shapes.Count
    Debug.Print MyWS.Shapes(i).Name
    boxtext = MyWS.Shapes(i).TextFrame.Characters.Text
    If Left(MyWS.Shapes(i).Name, 5) = "Text " Then
    If InStr(1, boxtext, search) > 0 Then
    test = True
    GoTo NextLoop
    End If
    End If
    Next

    NextLoop:
    If test = False Then WB.Close False
    Set WB = Nothing
    Set WS = Nothing
    FileName = Dir()
    Debug.Print FileName
    Loop
    Canceled:

    Set WB = Nothing
    Set WS = Nothing
    Set Cell = Nothing
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    End Sub[/vba]
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  5. #25
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Happiness is here again . Thanks Ken (and Jacob and mdmackillop) for all the trouble.


    Summary: Ken I tried your last routine with the sheets and it worked. But when I copied a few original files to the directory I got errors like the ones mentioned before (inl line vbmenu_register("postmenu_10237", true); "boxtext = MyWS.Shapes(i).TextFrame.Characters.Text" and "If test = False Then WB.Close False.
    But there are no macro's in the workbooks and are absolutely the same as the files already there

    To find the errors I added [VBA]Goto NextLoop:
    EndIf
    On Error Resume Next 'added

    Set MyWS = WB.Sheets("Data")
    test =
    False
    'Check the values in the Text Boxes here
    For i = 1 To MyWS.Shapes.Count
    Debug.Print MyWS.Shapes(i).Name
    boxtext = MyWS.Shapes(i).TextFrame.Characters.Text
    If Left(MyWS.Shapes(i).Name, 5) = "Text " Then
    If InStr(1, boxtext, search) > 0 Then
    test =
    True
    Goto NextLoop
    EndIf
    EndIf
    Next

    NextLoop: On Error Resume Next 'added
    If test = FalseThen WB.Close False
    Set WB = Nothing
    Set WS = Nothing
    FileName = Dir()
    Debug.Print FileName
    Loop
    [/VBA]


    After adding the code it worked! So there must be errors in the routine but since I'm only a beginner I don't know where the errors are. It is working perfectly.


    My next request is:
    1) I want a counter added that counts the files that have hits and after the search (the files it found are minimized in the taskbar), a msgbox must pop up telling how many files were found.

    2) I don't want the user to select a map to look in but to make the routine follow a default route to the network. When users start the macrol a form will pop up where the type in the month and the word to search for and then the searching starts. So our network name is \\Disc1\StockData. So the routine has to go here. Our data sheets are saved by date every day in the map called by month (e.g. Jan, Feb, etc.).

    I hope I'm clear enough and not to greedy

  6. #26
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Found the problem. Test is declared as Boolean. But where I got errors I read "test". Changed to "Test", removed On error Resume Next and no errors.


    P.S. My requests still stand .

  7. #27
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi Airborne,

    I'll hit your first question in this post, then go back and look at your next one.

    You'll need to add the line "dim x as integer" to the declarations section of your code. From your last post, take your code, and try changing it to this:

    [vba]Goto NextLoop:
    End If
    On Error Resume Next 'added

    Set MyWS = WB.Sheets("Data")
    test =
    False 'Check the values in the Text Boxes here
    For i = 1 To MyWS.Shapes.Count
    Debug.Print MyWS.Shapes(i).Name 'you don't actually need this line
    boxtext = MyWS.Shapes(i).TextFrame.Characters.Text
    If Left(MyWS.Shapes(i).Name, 5) = "Text " Then

    If InStr(1, boxtext, search) > 0 Then
    test =
    True

    Goto NextLoop
    End If

    End If
    Next

    NextLoop: On Error Resume Next

    If test = False Then
    WB.Close False
    Else
    x = x +1 'count opened files using x as variable
    End If
    Set WB = Nothing
    Set WS = Nothing
    FileName = Dir()
    Debug.Print FileName
    Loop

    msgbox x & " files were found which matched your search term!",vbokonly + vbinformation, x & " Files Found!"[/vba]
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  8. #28
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by airborne
    I don't want the user to select a map to look in but to make the routine follow a default route to the network. When users start the macrol a form will pop up where the type in the month and the word to search for and then the searching starts. So our network name is \\Disc1\StockData. So the routine has to go here. Our data sheets are saved by date every day in the map called by month (e.g. Jan, Feb, etc.).


    Meaning that your files are saved in \\Disc1\StockData\Jan, \\Disc1\StockData\Feb, etc...?

    Are you using a custom userform to capture the data, or did you just want to capture it by using VBA's InputBox function like you use for your search term? (ie, Enter Month:____)

    Also, once you have everything working the way you want, make sure you post the entire routine. There's some work that can be done to streamline your code a bit. It may make it run a bit faster, and can definitely be made to be a little easier to follow. In addition, we can remove most of the requirements for the OnError statements (or at least make them a little tighter for you.)

    Cheers,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  9. #29
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Hello Ken, great! The routine you adjusted for counting works. I had to change it a little. I got an error message where you added "Else" telling me "Else without If".
    Also (and sorry, I think I wasn't clear enough), I didn't want to receive a message telling me how many files were checked but I wanted a message telling me how many files had hits. So it now looks like this:

    [VBA]
    Goto NextLoop:
    End If
    On Error Resume Next 'added
    Set MyWS = WB.Sheets("Data")
    Test = False 'Check the values in the Text Boxes here
    For i = 1 To MyWS.Shapes.Count
    boxtext = MyWS.Shapes(i).TextFrame.Characters.Text
    If Left(MyWS.Shapes(i).Name, 5) = "Text " Then
    If InStr(1, boxtext, search) > 0 Then
    Test = True
    x = x +1 'count opened files using x as variable




    Goto NextLoop
    End If
    End If
    Next

    NextLoop:
    If Test = False Then
    WB.Close False
    Set WB = Nothing
    Set WS = Nothing
    FileName = Dir()
    Debug.Print FileName
    Loop
    msgbox x & " files were found which matched your search term!",vbokonly + vbinformation, x & " Files Found!"

    [/VBA]


    To answer the other questions:

    Meaning that your files are saved in \\Disc1\StockData\Jan, \\Disc1\StockData\Feb, etc...?

    As \\Disc1\StockData\Year (2000, 2001, 2002, etc)\Jan, \\Disc1\StockData\Year (2000, 2001, 2002, etc)\Feb, etc.


    Are you using a custom userform to capture the data, or did you just want to capture it by using VBA's InputBox function like you use for your search term? (ie, Enter Month:____)

    From the standard workbook (Test) where I have a button called search now, I want to use that button to open the UserForm.
    Just create a UserForm with two textboxes and an enter/cancel button. You type the year, the month and the word(s) you want to look for then hit the enter button and go.

    Also, once you have everything working the way you want, make sure you post the entire routine. There's some work that can be done to streamline your code a bit. It may make it run a bit faster, and can definitely be made to be a little easier to follow. In addition, we can remove most of the requirements for the OnError statements (or at least make them a little tighter for you.)

    It would be great if you could make it faster. And sure, I will post the entire routine once it's finished. I think I will have some more wishes along the way but it's getting there.

    Regards.

  10. #30
    VBAX Contributor Glaswegian's Avatar
    Joined
    Sep 2004
    Location
    Glasgow, Scotland
    Posts
    196
    Location
    Hope no-one minds me butting in here. Had a look at your file. To access your textboxes I tested with this
    [VBA]
    Sub TextboxCheck()
    Dim i As Integer
    Dim theText As String
    For i = 1 To ActiveSheet.TextBoxes.Count
    theText = ActiveSheet.TextBoxes(i).Text
    MsgBox theText
    Next
    End Sub
    [/VBA]
    which worked fine (I'm using XP at home). I'm not sure of the search code but you could try the changes I made based on the above test
    [VBA]
    GoTo NextLoop:
    End If
    Set MyWS = WB.Sheets("Data")
    Test = False
    'Check the values in the Text Boxes here
    For i = 1 To MyWS.TextBoxes.Count
    ' Debug.Print MyWS.Shapes(i).Name
    BoxText = MyWS.TextBoxes(i).Text
    If Left(MyWS.TextBoxes(i).Name, 4) = "Text" Then
    If InStr(1, BoxText, Search) > 0 Then
    Test = True
    GoTo NextLoop
    End If
    End If
    Next
    [/VBA]

    Regards
    Iain - XL2010 on Windows 7

  11. #31
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Hello Glaswegian. That also works. I'm not sure which option is better? Or is it just two ways of getting there?

    Thanks.

  12. #32
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Nice work Iain!

    Airborne, I think I'd use the textboxes method. It's more direct. If there are more than just textboxes in the shapes collection of the workbook, you'll save the time of looping through them all.

    For the rest, I'm diving into a month end right now, so don't have too much time free. I'll try to take a look at your next question tonight, if you can hold on. (Unless someone else gets there first!)

    Cheers,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  13. #33
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hey Airbone,

    Okay, I've been through your code from start to finish, and made some rather large changes to it. Please don't be offended by this, but I found your code pretty choppy with all the Goto statements, and hard to follow. I had to keep looking back and forward to figure out where one loop ended, another started, if I was in an IF evaluation, etc... Because of that, I've restructured quite a bit. I prefer to build my loops, assign a value to a variable, and then test for it later.

    Basically, the differences you'll notice are:

    -I've changed your FindAll routine to accept two parameter values: The filepath and the text to search for. This allows you to call it from a textbox, but means you can't just click in the code to run it. In order to step through it, you'll have to set up a routine to pass the variables to it, and step through that one.
    -The code is a ton shorter. Passing the variables for the directory from your userform to the FindAll routine means that you don't need the SHGetPathFromIDListA function (API), the BrowseForFolder function, or any of the constants or types associated with them
    -I have eliminated all the Goto statements in your code except the "On Error Goto 0" statements. This puts you code into loops that are easier to follow (IMHO) as your code doesn't jump around so much. (Again, IMHO,) It is easier to read as you can test for a condition, then follow it to see where it goes
    -I have tightened up your On Error sections to the following format (always best to keep your error handling as tight as possible to avoid catching something you don't want):
    [vba]On Error Resume Next
    'statement which may error out
    If Err.number <>0 then
    'whatever you want to happen if there WAS an error
    End if
    On Error Goto 0 'to reset the error value to 0 and to break on unhandled errors[/vba]
    -Used the textboxes object as suggested by Iain (Glaswegian) as it is more direct than looping through the shapes
    -I've significantly culled your Dim list to prune out anything no longer required
    -I've changed a few of your variable names, mainly for my benefit when testing
    -I've commented the routines so that you can follow what I've done (and also because it helped me keep a handle on it!)
    -I've written a routine to pass the variables from your userform

    Okay, ready? Here goes...

    First the FindAll routine, which is to remain in a standard module. I would suggest making a backup copy of your current workbook first, just in case you don't want to go this route. Once that's done, copy this code over all of your old code that you've posted here:

    [vba]Option Compare Text
    Option Explicit
    Sub FindAll(SearchPath As String, SearchText As String)
    'Macro purpose: Loop through all Excel Workbooks and search each of them for the
    'specified criteria
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim FileName As String
    Dim TermFound As Boolean
    Dim i As Integer, x As Integer
    Dim BoxText As String
    Dim Problem As Boolean
    'Turn off screen flashing
    Application.ScreenUpdating = False

    'Assign first filename in directory to FileName variable
    FileName = Dir(SearchPath & "\*.xls", vbNormal)

    Do
    'Set problem to false then attempt to open workbook and set WB & WS variables
    '(If an error results, set the Problem variable to true)
    Problem = False
    On Error Resume Next
    Set WB = Workbooks.Open(FileName:=SearchPath & "\" & FileName, ReadOnly:=True) _
    ', Password:="Uncomment and put password here if required"
    Set WS = WB.Sheets("Data")
    If Err.Number <> 0 Then Problem = True
    On Error Resume Next

    If Problem = True Then
    'If an error resulted, (Problem is True,) close the workbook
    '(On error resume next in case WB never opened at all)
    On Error Resume Next
    WB.Close False
    On Error GoTo 0

    Else
    'If no error, check all textboxes in the file for search term
    TermFound = False
    For i = 1 To WS.TextBoxes.Count
    BoxText = WS.TextBoxes(i).Text
    If InStr(1, BoxText, SearchText) > 0 Then
    'If the search term is found, set TermFound to true,
    'and stop checking further (exit the loop)
    TermFound = True
    Exit For
    End If
    Next i
    If TermFound = False Then
    'If the search term was not found, close the file
    WB.Close False
    Else
    'If the search term was found, add 1 to the count of
    'opened files using x as variable to hold the info
    x = x + 1
    End If
    End If
    'Release WB & WS variables and set FileName to next file
    Set WB = Nothing
    Set WS = Nothing
    FileName = Dir() 'sets FileName to next file in directory
    Loop Until FileName = ""
    'Inform the user how many files were opened (number of opend files held in x)
    MsgBox x & " files were found which matched your search term!", _
    vbOKOnly + vbInformation, x & " Files Found!"

    'Allow screen updates
    Application.ScreenUpdating = True

    End Sub[/vba]

    Now, I've made some assumptions regarding your userform, so you may have to adjust some things here. I've assumed the following (using the naming conventions that I use):
    -The text box that holds the date is named tbDate, and will ask for a valid date
    -The text box that holds the search term is named tbSearch
    -Your Okay button's name is cmdOkay
    -Your Cancel button's name is cmdCancel
    -Your UserForm's name is ufSearch
    -August 2004's data would be stored in \\Disc1\StockData\Year2004\Aug\

    Given the above, the code that needs to be pasted into the userform object (right click ufSearch and choose "view code") is:
    [vba]Private Sub cmdOkay_Click()
    Const MainPath = "\\Disc1\StockData\Year"
    Dim Prompt As String
    'Make sure both textboxes have values assigned
    If Not IsDate(tbDate.Value) Then Prompt = "Please enter a prompt date" & vbCrLf
    If tbSearch.Value = "" Then Prompt = Prompt & "Please enter something to search for"
    If Prompt = "" Then
    'If Prompt is empty, then no problems were detected, so call the FindAll routine
    Call FindAll(MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm"), tbSearch.Value)
    Unload Me
    Else
    'If Prompt is not empty, tell the user what info need correcting and return to the
    'userform
    MsgBox "Sorry, but I need more information!" & vbCrLf & Prompt, _
    vbCritical + vbOKOnly, "Hang on!"
    End If
    End Sub
    Private Sub cmdCancel_Click()
    'Unload the userform
    Unload Me
    End Sub[/vba]

    Oh, and if you want the textbox to open with today's date, you could also add (in ufSearch's code pane) the following:
    [vba]Private Sub UserForm_Initialize()
    tbDate.Value = Format(Now(), "mm/dd/yyyy")
    End Sub[/vba]

    One thing to be aware of, (although I'm sure you already know,) is that it takes a while to search through each file for your data. During this search, the userform will stay open, but the screen updating is off, so it may end up looking like Excel has hung up. You may want to add a progress bar to the userform to make sure you user's are aware that the program is still working.

    I think that's it. I hope this helps, but be sure to post back if something doesn't work.

    Now it's off to bed for me! Cheers,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  14. #34
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Ken etc. You sure did a lot of work, thanks! If you want to learn VBA, this is the forum to go to. The searching sure is faster and it's great that you only have to type the month/year and the word you want to look for. Also cool is the curent date in my form.

    Your idea about a progress bar is great but how do I do that ?

    Regards.

  15. #35
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hi Airborne,

    Jake has a good one found here. Here is another very good one by Chip Pearson.

  16. #36
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi Airborne,

    You're welcome!

    In addition to the links that Zack posted above, John Walkenback also has one here. It's similar to Chip Pearson's at first glance, but I'm sure that there's some minor differences. I can't say that I have a preference on any of them, as they are all fantastic coders. Read them all, and see which one works best for you. Personally, I think I'd set it up as a multipage control, with the progress indicator on page 2 of the multipage. (Only because I've had good success doing it that way in the past.)

    The toughest part that you'll have to cope with is counting all the files in the directory before you start, then updating the progress bar as you go through. I guess we could be set up as a separate function though, returning only the count... Hmm... I'm seeing another KB entry here, as I couldn't find anything in the search. I'll try and work on that tonight, again, if I get some time. (My daughter was really good and went to bed at 8:00 last night! )

    Also, it might be a good idea to add a routine to check if the folder (as determined by your date) exists before the routine fires... just in case it doesn't. That would save the code from bombing in the middle. Nothing worse than a run time error once delivered to the users... Again, a separate utility function could be created for this.

    That's it for now. I'll try and check back tonight.

    Cheers,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  17. #37
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    What you could do (assuming adaption of Chip Pearsons, although any would do) what you need to do, after registering the .dll file and setting the reference, is put the .Increment property within your loop. As long as you have a start number (1) and an end number (your count) it will/should increment accordingly. Works well for me.

  18. #38
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Thanks Zack.

    That looks good. I'll have to play with it tonight. Most likely faster than coding a loop in VBA, and probably a heck of a lot less work than fiddeling with the userform.

    Good stuff!
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  19. #39
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Hi Ken and Firefytr. I'm checking out the links and the progress bars all look cool. How to implement them in the routine is not clear to me at the moment .


    I hope I don't sound ungrateful but the routine by Chip Pearson is not my first choice. Installing dll files on the network (not on my own pc) will give me extra paperwork I'm afraid.

    Thanks and regards.

  20. #40
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Ungrateful? :rofl

    I think that's a pretty valid concern (the installation part, not the ungrateful part!) If it's going to get you in trouble with the IT guys, it's probably a problem!

    Let's go back to the userform method. Check out DRJ's and J-Walk's methods, as they both use a userform, and no intallations on networks are required.

    Cheers,
    Last edited by Ken Puls; 11-02-2004 at 10:59 PM. Reason: Clarify point re concern
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





Posting Permissions

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