Search Database Not Working See Attached Spreadsheet.

I have a spread sheet attached with very good notes and a diagram to show what I need to fix. I have some of the VBA code working but I can't quite get the whole thing working as I need it to. Any help would be much appreciated.

Thank you,

This is a revised version with more vba code. I believe it is the right code, it just is not searching beyond the sample customers within this sheet. I need it to search and open the folder it resides in. any help is greatly appreciated.

thank you,

Hey tigeravatar, first, thank you sooooo much for looking at this mess for me. answer to first quesion: Yes, subfolders in a main folder and they may be a few subfloders deep within the main folder. As follows, (Example), to navigate to one of these subfolders, it will look something like this. Z:\Insurance Jobs\Liberty Mutual\Stephanie Thompson\Document Templates, which finally brings us to an Excel Workbook which has a sheet in it called "Communication Log". I would like to have the user push the search button and enter any one of the criteria, (Name/File #/Address/Claim #) and the macro will not display the path but actually take you to that sheet, deep down in the filepath directory. Second question: multiple results. No, I think if once the macro takes the user to that sheet "Communication Log", they will be in the place they need to be. I don't believe there would be a conflict in the search as there should only be one file # or definatley only one claim #. I would like to use the macro with the button that allows the user to type in one of the search criteria and hit enter and they are sitting in the "Communication Log" sheet for that customer. That's the long and short of it. I have so much crap in that vba code box, I can't even begin to tell you. You can delete everything else in there and make that button do as I stated in the last sentence, you will be a god! I can attach the workbook that has the "communication log" so you can get a better idea of what destinations you are trying to get the search button and vba code to navigate to. Wow huh? Well maybe not for you but for me this stuff is Unreal. I am so frustrated with not knowing how to make this work and I am on a very tight timeline as our corporate office is all over me with completing this stuff.

thank you so much for any help you can give.

This is the workbook that will be used for every new customer who will get there own folder as they become a customer. I will need the master search vba to recognize a new folder. or I suppose it will just look for a match and navigate to the matched workbook in the destination folder. Right??

thanks,

Yes the search will always start in "Z:\Insurance Jobs\, but there will be several different trailing folders like State Farm, Allstate, Nationwide, etc. As we get new Insurance companies, we will add those folders and sometimes we will have private (non-insurance) job folders added as well. so actually the correct answer is No, not always "Z:\Insurance Jobs\ It maybe "Z:\NON-Insurance Jobs\"Sample Name". But 95% of the time it will be "Z:\Insurance Jobs\. All four search terms will be in the excel file, yes.

You know tigeravatar, If all I have is that button that says "Press To Search" and that box pops up so the girls in the office can simply type in the any one of the search criteria (Name/File #/Address/Claim #), that is all we need. You know what would be real cool, is if we could make that button so it could reside on the girls desktop screen and when they push the button, the search box pops up right on there desktop. Is that even possible?

Need to look in the Z: drive in a folder that wouldn't contain the word insurance as well.

Once the macro finds the match to one of the search criteria, then open the workbook and then always open the sheet named "Communication Log". Every new job will have a "Communication Log" and that's where 99% of the time a workbook is looked up, to read the log notes and update the customer, their agents, the adjusters etc. We will constantly be searching that Sheet. ("Communication Log").

Well, I suppose we can put non-Insurance jobs in the Insurance folder to speed the searches up. As a matter of fact, lets do that. I will just advise everyone how and where to enter non Insurance jobs. That will make things easier. Cool?

thanks,

Sounds good, thank you so much for your help. the .vbs is for the button to reside on the desktop?

I got the .vbs on the desktop and the search box came up. When I typed Mildred Smith, the mouse pointer turned to its usual blue cirlcle (spinning) as it appeared to be searching and then it just went back to the standard mouse pointer and nothing happened. I checked the Z drive is mapped. I manually went to the folder where mildred smith folder is and in there was the excel workbook with the sheet named "Communication Log". So...... not sure whats going on with it. I can copy and paste the file path if that would help anything. ??

thank you,

Aslo, if it matters, I noticed in you vba code, it references .xls My workbooks are .xlsm ?? does that matter?

Yes, Mildred is listed in the "Communication Log" sheet where it says "Customer:" That would be cell B1.

Not sure if this matters but, the way I have programmed that workbook is: You open up the workbook and go to the first sheet named "Claim Loss Report". User fills that form out and it will automatically populate all the rest of the forms/sheets in the entire workbook, including the "Communication Log" sheet. So Mildred Smith is referencing Sheet1 "Claim Loss Report", Cell C6. That possibly have anything to do with it's search?

So in other words, Mildred Smith's name is plastered all over that workbook. Not just in the "Communication Log".

Ok, where do I paste this code?

Ok, "Windows Script Host": Error box says: Script: c:\Users\RBI\Desktop\Master Job Search.vbs
Line: 1
Char: 7
Error: Syntax error
Code: 800A03EA
Source: Microsft VBScript compilation error

The entire code on your last post. Was that not good?

I Copied this code, Opened a new blank notepad, pasted this code in it, file, save as, "the name I chose" .vbs, to the desktop. NO?

Please Login or Register  to view this content.

After it was on the desktop i clicked on it and the immediate error message that popped up was: "Windows Script Host": Error box says: Script: c:\Users\RBI\Desktop\Master Job Search.vbs
Line: 1
Char: 7
Error: Syntax error
Code: 800A03EA
Source: Microsft VBScript compilation error . Not sure what happened.

Ok, what if we just set it up in that attachment I posted earlier that you were working with. The one that had the button for search in it. Would that be ok?


thank you so much, and I hate bothering you with this. I know it has kicked my butt for the last week.

Tiger avatar was trying to set up a search box on the desktop therefore bypassing excel. That requires .vba code.

Thanks

This is the script, in .txt format for testing. I finally got it to start to search for "Insuranc Jobs" folder and then ultimatley finding "customers name" and then finally automatically opening the "Communication Log" in that customers workbook. When I type the search name, "Mildred Smith", another box pops up which is blank and has an ok button on the bottom. I push the OK button and the box just keeps blinking. After pressing it multiple times, another long box pops up with the file path to the folder on the Z: drive. I hit OK on that box and then the first box pops back up (still blank), so I start hitting that box again, and the same process starts over again until the second box pops back up but this time it has the file path but now includes "Mildred Smith". I click OK and it once again goes back to that first box that is blank and has the OK button on it.

I think its close to working, just not sure how to get it to not ask for the customer, just open the file.


thank you,

Please Login or Register  to view this content.

Its just like it is slowly confirming that each folder is there but never opens any of them. I just went through the button pushing process again just to see where it took me. What it's doing is popping up file paths to every folder in the main "Insurance Job" folder. It shows the path directory and when I click OK, it closes and finally it closed and disappeared from the desktop screen. It never actually opened any of the folders that it apparently found in the Z drive. Hopefully this helps.

thank you,

Ok, I have attached a worksheet with the button that says" Press to Search". I have the Vba code in there. It does search through my Z drive and find the folder "Mildred Smith" (that is the name to search for testing purposes). and then opens the sheet in the workbook named "Communication Log". Thats what I need to do. The problem is when I type Mildred Smith in the search box. I have to hit OK on the little box that pops up, sometimes upwards of 20 times before it opens another box which I only have to click once, then the "Communication Log" opens in Mildred Smiths workbook. Then when I go to exit, I have to click OK on the box that pops back up, about 20 more times and along the way it is opening up every other "Communication Log" in all the other workbooks. ?? Help!Please!

Thank you,