Code To Search an excell sheet

hi
referring to this question
http://www.experts-exchange.com/questions/28709664/how-to-search-from-windows-7-an-excell-sheet.html#a40953052
i have too many excell sheets , and really fail to find my sheet
is it possible to use vba code in access to search excell sheet ?
i found code here
http://superuser.com/questions/742482/how-to-find-specific-sheet-name-in-excel
but this is searching only active excell sheets
how can adjust it to search all sheets ?
NiceMan331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached workbook and click the button on Sheet1 to search the Excel File based on a Sheet Name which you remember.

The code will prompt you first to input the Sheet Name which you remember and then will prompt you to select a Target Folder and search for your file in the target folder and its all sub-folders if any. If a workbook is found which has the same name as you have mentioned in the inputbox, it will list the path of that workbook in col. A.

You may need to pay attention to the msgboxes which may appear during the code execution.

See if this helps.
SearchExcelFileBySheetName.xlsm
0
NiceMan331Author Commented:
yes , thanx
the operation is good , but i didn't get result
because i have too many files
and  the macro when it reading (or checking) a file .
it sent an errors that files may link to another sheet , and message to update or don't update
the process takes alot of times , then i skip it
any way to handle this error ?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may add a line of code in the beginning like....
Application.AskToUpdateLinks = False

Open in new window

and before End Sub and MsgBox line of code add another line of code like this.....
Application.AskToUpdateLinks = True

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Here is the workbook with the lines of code added which I suggested in my previous post.
SearchExcelFileBySheetName.xlsm
0
NiceMan331Author Commented:
Ok thanx , I will try it tomorrow , now I left my office , thanx
0
NiceMan331Author Commented:
ok , great file
i triyed one known file which i know it is already exist and i know the file name
the search once found a sheet like the name i select , the process stopped and displayed the file path
i need the code to continue searching if there is another file having same sheet name
but it is really good job , i appreciate
another question , could you please add another button to search all files in a specific folder , displaying all files name in one column , and all sheets name in the following columns
here i can easily find any file i need and may be without this search
thanx alot
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
My bad. I forgot one line of code and the results were being over written in cell A2, the reason being that I wrote the code but didn't test it.

With the tweaked code now, the code searches the files based on their Sheet Name and it will not stop unless it completes scanning of all your excel files in the chosen folder ( and its sub folders).
Therefore if there are multiple files with the same sheet name in the chosen folder and its sub folders, it will list all of them in col. A.

Also I don't think that listing the Sheet Names in col. B is useful when you know the sheet name you are trying to search. It will be only helpful if you want to search the files based on the part name of the sheet if you don't remember the full sheet name. I have included the code to list the Sheet name found in the excel workbook.

So if you want to search the files even with the part name of the sheet, say "sh", the code will search and list all the files which have at least one sheet which contains the word "sh" in it.
SearchExcelFileBySheetName.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
great great great
thanx very much
i found the file which i lost
really i'm thanking you
i got my answer
i have to accept the solution
now it will be a favor from you for me , if you like , to do what i asked you
to bring all excell files and all sheet names into the result page
thanx alot again
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad you found your lost file. :)

I think you should open another question with your new requirement. I will try to assist you with your new question also.
0
NiceMan331Author Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.