Consulting

Page 4 of 5 FirstFirst ... 2 3 4 5 LastLast
Results 61 to 80 of 98

Thread: Solved: Search engine

  1. #61
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Airborne... I tried adding the .showwindowsintaskbar (never used that before!) and it seems to work just the way you're looking for in my case...

    In the Findall routine, I changed:
    [vba] 'Turn off screen flashing
    Application.ScreenUpdating = False [/vba]
    To
    [vba] 'Turn off screen flashing & taskbar updates
    With Application
    .ScreenUpdating = False
    .ShowWindowsInTaskbar = False
    End With[/vba]

    And near the end:
    [vba] 'Restore screen updating and clear statusbar
    Application.ScreenUpdating = True
    Application.StatusBar = False [/vba]
    To
    [vba] 'Restore screen updating, show open files on taskbar and clear statusbar
    With Application
    .ScreenUpdating = True
    .StatusBar = False
    .ShowWindowsInTaskbar = True
    End With[/vba]

    No screen flashing, no taskbar updates until you click okay. Is that what you meant?
    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. #62
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Same here!!!!

  3. #63
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by Airborne
    Sorry. I think I've done something wrong. I thought it worked because when I click on the button It doesn't close and I get a message to close using ....button. But it now is impossible to close the file.
    :rofl That's what you wanted, isn't it? :rofl

    I think MD's idea was that you used a separate command button to close the file. If you comment the code, though, you'll be able to continue until that's confirmed...

    MD, I think that method will only work for a userform... but could be wrong...
    Last edited by Ken Puls; 11-04-2004 at 05:19 PM. Reason: Added last line...
    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!





  4. #64
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi, I think you need the following code in your button. I'm not totally clear as to any knock-on effects on other open Excel files though.
    MD
    Application.EnableEvents = False
    ActiveWorkbook.Close



    I hate it when my computer does what I tell it to, and not what I want it to.

  5. #65
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Ken I guess you did it again. Fantastic! Now it looks real professional:cool . A real search engine in my workbook!

    I hope you won't be offended but I never thought this project would become this professional so I would like to speed up the search (search on a map of 90 files takes 1 minute vs Windows search 25 seconds) and add a search bar as Windows uses in it's search engine.

    Do you want me to put the routines you made with comments in this thread now?

    Thanks and regards.

  6. #66
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hmmm...

    First, for the record, I stand corrected on the close routine. Thanks MD!

    Second... ho boy! My only concern here is that I don't know how much faster we can make it. Remember that Windows is specifically built for this kind of function, while VBA calls some of those and runs them. Inherently, they will always be slower. I'll pitch in what I can though, but be aware that it may be small improvements, not large. (I'm always game for a challenge though! )

    FYI, you never answered me on the time sheet naming... if there is a convention, we could save the time of opening those files by evaluating their name before we open them. That would save on the open/close time for each of those, at least.

    As for the progress bar, yep, we can do that. Out of curiosity, can you export a copy of your userform (right click it in the VBA explorer), zip it up and post it here? I want to see how it's set up to tinker with it. I read a bit about it last night before bed (my wife reads novels, if you can believe that! ), and came up with a good one, I think. I should be able to make it without slowing down your code much at all, as it could just replace the statusbar part.

    Maybe do post the full FindAll routine, though. If the userform hasn't changed, that should be okay.

    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!





  7. #67
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Hi mdmackillop, that works. I added the line [VBA]Application.EnableEvents = False[/VBA] (as you said) to my quit routine. That routine has knock-on effects on other sheets unfortunately. I'm working on that still. [VBA]Sub Quit()Application.EnableEvents = False
    If MsgBox("Close Data tool Without Saving Other Excel Files?", _
    vbQuestion + vbYesNo) = vbYes Then
    Application.ScreenUpdating = False
    Application.Quit
    Workbooks("Data1").Close saveChanges:=False
    ActiveWorkbook.Close saveChanges:=False
    Else
    End
    End If

    End Sub[/VBA] I would like the routine to check if other workbooks are open and then ask the question as above in the msgbox, otherwise it should close right away.

    But maybe I should transfer this to another thread. Ken works on this thread while his wife reads novels but my wife is already sleeping . I'll answer him and then it's off to bed.

    Thanks and regards. vbmenu_register("postmenu_10634", true);

  8. #68
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi both,
    I've not been following the detail closely, but if this search is to be used routinely, here's a few thoughts

    A variation could be to save the positive results for the commonly used search terms in a specific worksheet within each workbook (maybe a WorkbookClose routine). You could then search only these worksheets for the desired files.
    Ideally, you would want a solution which can be searched without opening the workbooks.
    http://www.experts-exchange.com/Appl..._20980429.html

    Another idea would be to add the search terms, either as Keywords or Custom Fields to the WorkBook properties.

    Finally, you could write the results to an "index" file to maintain a record of the file locations.

    MD

  9. #69
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Hi Ken, here are both routines. The form is still as you said.[VBA]Private Sub cmdOkay_Click()
    Const MainPath = "\\Disk1\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
    If FolderExists(MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm")) Then
    If CountFiles(MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm")) > 0 Then
    'If folder for the month exists & has files in it, call FindAll routine

    Unload Me

    Call FindAll(MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm"), _
    tbSearch.Value)

    Else
    'If no files are in the directory, inform the user
    MsgBox "The information you entered generated a file path of:" & _
    vbCrLf & MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm") & _
    vbCrLf & vbCrLf & "There are no files in that directory!" & vbCrLf & _
    "Please modify your selection and try again!", _
    vbOKOnly + vbCritical, "Directory is empty!"
    End If
    Else
    'If the folder for the month does not exist, notify the user
    MsgBox "The information you entered generated a file path of:" & vbCrLf & _
    MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm") & vbCrLf & vbCrLf & _
    "That file path does not exist! Please modify your selection and try again!", _
    vbOKOnly + vbCritical, "Folder does not exist!"
    End If
    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

    Private Sub UserForm_Initialize()
    'Put today's date in the userform
    tbDateToday.Value = Format(Now(), "d-mmm-yyyy")
    End Sub

    [/VBA]

    And......[VBA]Option Compare Text
    Option Explicit
    Function FolderExists(Folder As String) As Boolean
    'Function purpose: To count all files in a directory
    Dim fso As Object, _
    SubFolder As Object

    'Create objects to get a count of files in the directory
    Set fso = CreateObject("Scripting.FileSystemObject")
    On Error Resume Next
    Set SubFolder = fso.GetFolder(Folder).Files
    If Err.Number <> 0 Then
    FolderExists = False
    Else
    FolderExists = True
    End If
    On Error GoTo 0
    End Function
    Function CountFiles(Directory As String) As Double
    'Function purpose: To count all files in a directory
    Dim fso As Object, _
    SubFolder As Object

    'Create objects to get a count of files in the directory
    Set fso = CreateObject("Scripting.FileSystemObject")
    On Error Resume Next
    Set SubFolder = fso.GetFolder(Directory).Files
    If Err.Number <> 0 Then
    CountFiles = 0
    Else
    CountFiles = SubFolder.Count
    End If
    On Error GoTo 0
    End Function
    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 Processed As Integer, ToProcess As Integer
    Dim BoxText As String
    Dim Problem As Boolean
    'Turn off screen flashing
    With Application
    .ScreenUpdating = False
    .ShowWindowsInTaskbar = False
    End With
    'Count number of files to proces, and assign first filename in directory to
    'FileName variable
    ToProcess = CountFiles(SearchPath)
    FileName = Dir(SearchPath & "\*.xls", vbNormal)

    Do
    'Inform the user how many files have been processed
    Processed = Processed + 1
    Application.StatusBar = "Processing file " & Processed & " of " & _
    ToProcess & " (" & Int(Processed / ToProcess * 100) & "% complete)"

    '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"
    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
    TermFound = False
    For Each WS In WB.Worksheets
    'If no error, check all textboxes in the file for search term
    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 = True Then Exit For
    Next WS
    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!"

    'Restore screen updating and clear statusbar
    ' Application.ScreenUpdating = True
    ' Application.DisplayStatusBar = False
    ' Application.ShowWindowsInTaskbar = True
    With Application
    .ScreenUpdating = True
    .StatusBar = False
    .ShowWindowsInTaskbar = True
    End With
    End Sub


    [/VBA]

    Regards.

  10. #70
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Okay... here we go!

    I've rewritten a bunch to get the progress bar working, so you'll want to review this carefully. Some things to note:
    -You no longer need the "FolderExists" or "CountFiles" functions, as I have rolled them both into the cmdOkay routine of the userform
    -In addition, the count of the files now is passed to the FindAll routine so that it doesn't need to be recalculated
    -The progress indicator has replaced the statusbar, but it most likely will slow things down a bit. At least you get to watch something though, I guess It is 4-5 lines of code instead of 1, which gives rise to the speed concern, but hopefully it isn't too much
    -The userform now expands and shrinks to show/hide the progress indicator
    -I've attached a zipped copy of the userform that I've been working with as I think it's easier that way than trying to explain it. If your userform is called ufSearch, you will want to rename it before you attempt to import this one
    -I have not done much to the file searching at this point. That is going to take a little while to read up on around the net (thanks for the posts, MD, those will be helpful). I figured that speed could probably come later, as long as the main view is there first. (Again, at least giving your users something to watch while the wait )


    So here's all the code... In the regular module:
    [vba]Option Compare Text
    Option Explicit
    Sub FindAll(SearchPath As String, SearchText As String, ToProcess As Integer)
    '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 Processed As Integer
    Dim BoxText As String
    Dim Problem As Boolean

    'Turn off screen flashing & taskbar updates
    With Application
    .ScreenUpdating = False
    .ShowWindowsInTaskbar = False
    End With

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

    'Expand ufSearch to show progress bar
    With ufSearch
    .Height = 174
    .Repaint
    End With

    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"
    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
    TermFound = False
    For Each WS In WB.Worksheets
    'If no error, check all textboxes in the file for search term
    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 = True Then Exit For
    Next WS
    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

    'Inform the user how many files have been processed and update the progress bar
    Processed = Processed + 1
    Call UpdateProgress(Processed / ToProcess)

    '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 = ""

    'Restore screen updating, show open files on taskbar and clear statusbar
    With Application
    .ScreenUpdating = True
    .ShowWindowsInTaskbar = True
    End With

    'Hide the progress indicator
    With ufSearch
    .Hide
    End With

    '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!"

    End Sub
    Sub UpdateProgress(PctDone As Single)
    'Source: John Walkenbach's Excel 2002 Power Programming with VBA
    'Purpose: To update the ufSearch userform
    With ufSearch
    .frmProgress.Caption = Format(PctDone, "0%")
    .lblProgress.Width = PctDone * (.frmProgress.Width - 10)
    .Repaint
    End With
    End Sub[/vba]

    And in the userform:
    [vba]Option Explicit
    Private Sub cmdOkay_Click()
    Const MainPath = "\\Disk1\Stockdata\Year\"
    Dim Prompt As String, _
    FilesToProcess As Integer, _
    fso As Object, _
    FullFilePath 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 is empty, then no problems were detected
    If Prompt = "" Then
    'Create a file scripting object and set the FullFilePath variable
    Set fso = CreateObject("Scripting.FileSystemObject")
    FullFilePath = MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm")

    'Check if the file path exists, and count the number of files in it
    If fso.FolderExists(FullFilePath) Then
    FilesToProcess = fso.GetFolder(FullFilePath).Files.Count
    If FilesToProcess > 0 Then
    'If folder for the month exists & has files in it, call FindAll routine
    Call FindAll(FullFilePath, tbSearch.Value, FilesToProcess)
    Unload Me
    Else
    'If no files are in the directory, inform the user
    MsgBox "The information you entered generated a file path of:" & _
    vbCrLf & MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm") & _
    vbCrLf & vbCrLf & "There are no files in that directory!" & vbCrLf & _
    "Please modify your selection and try again!", _
    vbOKOnly + vbCritical, "Directory is empty!"
    End If
    Else
    'If the folder for the month does not exist, notify the user
    MsgBox "The information you entered generated a file path of:" & vbCrLf & _
    MainPath & Year(tbDate) & "\" & Format(tbDate, "mmm") & vbCrLf & vbCrLf & _
    "That file path does not exist! Please modify your selection and try again!", _
    vbOKOnly + vbCritical, "Folder does not exist!"
    End If
    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
    Private Sub UserForm_Initialize()
    'Shrink userform so progress bar doesn't show
    Me.Height = 108
    'Put today's date in the userform
    tbDate.Value = Format(Now(), "d-mmm-yyyy")
    End Sub[/vba]

    To import the attached, right click on your forms directory and choose "import" (but remember to change your userform first if it's name is ufSearch!)

    Now it's time for me to go too!

    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!





  11. #71
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Ken that's it, FANTASTIC! I had a good short night sleep but I couldn't wait how fast to check my e-mail.

    I don't know if you agree but I guess this thread is solved. I'll wait for your comments because of the recommendations of mdmackillop (thanks) before I mark it solved.

    I will post the other questions regarding my workbook in a new thread because they have nothing to do with the search engine.

    Thanks very much for your trouble (you will have some time to read a novel too now ).

    Regards.

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

    Glad it works! As far as marking the thread solved, that's your call, not mine. Is it doing everything you want it to? How is the speed factor (did the most recent changes make a difference one way or another?)

    I've mulled over MD's comments, and they hinge on two important points:
    -Will you be able to come up with a list of the most frequently used search tems?
    -What happens if a user wants to search for a term that isn't in your list?

    I've read a few articles on trying to pull from a closed workbook as well (thanks to some referenced articles in the link MD posted, and at Ozgrid.) The one issue I can see here, from everythign I've read, is that it all points to pulling from worksheet cells, not textboxes. I don't know how easy it would be to get around that, as the textbox objects may not be supported with those methods.

    And I'm glad to have been of help with it too. (And FYI, I read Excel books instead of novel's on a regular basis. I actually enjoy it! )

    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. #73
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Ken,
    I wasn't thinking to read textboxes from a closed workbooks, but rather an "index" page within the workbook.
    Thanks for "taking over" this one as I've been busy with a query elsewhere.
    MD

  14. #74
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi MD, as I said, quite enjoyed it. I love working on projects where you can follow it along from start to finish. Gives a good sense of accomplishment at the end of the day.

    With regards to the search, let me see if I've finally understood you.. ( ).

    You're thinking of something like writing all textbox values to a specific sheet (one of more cells) before closing. Then using one of the aformentioned procedures to search those cell(s), on that specific sheet only, without opening the workbook?

    I think that could be made to work. I'm thinking I'd put it in the same workbook, or you could run into issues with maintaining (syncronizing) the files. Of course, knowing the amount of data typically (and the max amount) entered in the TextBox would be important to determine the best way to structure it in a the sheet.

    Is that sort of what you meant?
    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!





  15. #75
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Ki Ken,

    That's the general idea. If there is a list of commonly used search terms, this could be set up as a Close event, to keep the data in the book (or separate index) up to date, and your routine, as you have it at present, would always be available for custom searches.

    MD

  16. #76
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    All right, I'm game for that!

    So Airborne... what do you think? Would you like to try on this one? One caveat... I'm away this weekend, so it will take me a little time to respond on it initially.

    Would need a couple of things from you to proceed though...

    -The workbook that fires your search procedure... can we store an index on a sheet in it, or does would you prefer it to be stored in a different workbook?

    -Can you provide a list of the most frequently used search terms?
    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. #77
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Hi both,
    Sorry for replying that late but work, work......

    First: It worked great from home, it worked great on a stand-alone pc at work (not supported by IT) but on the pc's supported by IT the sheet where the bar is on, disappears (the bar stays) and you see all the files that are beeing searched popping up and closing again. After 90 files opening and closing fast you get a little nervous. Now the IT guys had already left for the weekend so I couldn't ask one of them. The configuration of Excel is the same on all pc's, so I'm a bit confused .

    The idea of not opening sheets sounds nice all of a sudden. We work from the standard sheet, all the macro's are there, so storing an index there is no problem. We don't realy need a search history and when you get the message no files found that matched your criteria is fine by me. The sarch criteria are very mixed. Sometimes we search on a tagnumber and then on a person, then on equipment name, it's very difficult to give a list.

    I was very happy with the routines you provided and I'm a bit that I have the problem on the IT pc's.

    I'm sure willing to try the new idea though, everything I can do to learn VBA is fine by me

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

    I'm a little stumped as to why it wouldn't work on the IT pc's too. If all the configurations are the same, and you're using the most up to date file, that doesn't really make sense to me. Are all the same add-ins installed?

    And now for something completely different... If you don't have commonly used search terms, it puts a wrinkle in that plan, but what about this...

    You only have 8 textboxes in a file, right? So if you dumped the entire contents of your textbox into a cell (8 columns, 1 for each textbox), and also the file path and name (in another 2), using the workbook_beforesave event (in the tb laden files)...

    You should then have a master list of all contents of all textboxes in all files with their paths. Theoretically, you should be able to search using the find method on the worksheet list, and if the term shows up in any of the columns, add the file path to an array. Once you have the array, open up each file in it.

    I would think it would be way faster than opening, checking, closing, etc...

    Just a thought. What do you guys think?
    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. #79
    VBAX Contributor Airborne's Avatar
    Joined
    Oct 2004
    Location
    Rotterdam
    Posts
    147
    Location
    Hi Ken, it's not that it doesn't work. The search engine works great! It will find every word I search for. But, it's the flickering of the screen, files opening/closing for about one minute. I don't know why it happens on the IT pc's. All the add-ins are installed. It looks so professionel on my pc .

    As for your suggestion....that's too much English for me . Can you give me an example?

    Thanks and regards.

    p.s.
    It's that time already at my location.

  20. #80
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Sorry, Airborne. I knew what you meant, but it's like the screenupdating code isn't firing. Why that would be, I just don't know.

    As far as the rest goes, why don't I post some stuff up on Monday (maybe Sunday if I get a chance.) Stuff to do tonight, and off on a trip tomorrow for the weekend.

    Have a good weekend!
    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
  •