Avatar of NiceMan331

asked on 

Code To Search an excell sheet

referring to this question
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
but this is searching only active excell sheets
how can adjust it to search all sheets ?
Microsoft AccessVisual Basic ClassicVBA

Avatar of undefined
Last Comment
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

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.
Avatar of NiceMan331


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 ?
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

Here is the workbook with the lines of code added which I suggested in my previous post.
Avatar of NiceMan331


Ok thanx , I will try it tomorrow , now I left my office , thanx
Avatar of NiceMan331


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
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of NiceMan331


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
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.
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo