Searching through multiple excel files

Hello everyone.

I need a program which searches a particular string through multiple (all) excel files in a folder.
I want to have the results (maybe on a page in excel) with:
-name of the file
-sheet in the file
-cell in the sheet (address)
-cell in the sheet (content)

I am particularly looking for a VBA solution, but I do not want to open the files before searching, because some of the files take a lot of time to load and the files are big so... there could occur a memory shortage.

I would appreciate any idea I will receive.

A VBA solution will require the files to be opened before searching, although can do one at a time to minimize memory utilization.

Actually you CAN extract the sheet names from a closed workbook. If you Google "Excel VBA Sheet names from closed workbook" you'll find several approaches that will work.

Once you have mastered getting the sheetnames, you would need to imbed that process in a macro that finds all the files in a given folder, extracts the sheetnames one at a time, does some sort of worksheetfunction calculation on the closed workbook to extract a value using a known working formula, then recording that answer, lather rinse repeat.


I understand you're looking for free, but all the stuff I outlined above would probably be a few hours of work to getting functioning exactly the way you want. That's a tall order. But someone might jump in to donate that much time.

Each of your questions just reveals that you don't know how to do what you're looking to do, and don't even know what you don't know regarding getting it at all. A tall order, as I said.

"I know my engine is smoking and stuttering and spitting black smoke, but I don't want to pay a mechanic to fix it. How do I fix it myself?"

See the problem? You're basically saying, "Hey mechanics, show me how to do this for free and then I can claim I fixed it myself... woo hoo!"

Anyway... Work out the steps/macro for extracting the information from ONE workbook that is open using your given knowledge. Once you have a macro that can do that, you're 60% of the way there. The forum, or Googling, can help you then convert your working macro into one that would:

1) First work by OPENING the files in a given workbook and extracting the values from each one using your already developed method

2) The last and biggest hurdle of all, extracting that info without opening the workbooks...

It's a lot of work, you should really do the groundwork, the ask for help with "steps" along the way.



Perhaps using a paid product really is the correct approach... or paying someone to do all this work for you.

REGEX is fast, certainly. But not knowing what you're actually doing makes it hard to comment specifically. If you're NOT going to be opening the workbooks in the end, there's the possibility that your method won't work. However there are many WorksheetFunctions that DO work on closed workbooks, so again, I'd have to know what you're doing to comment specifically.


One of my clients need me to create a secondary tool for him to use against his master Job Assignments workbook. The problem with his JA was that it was SO huge and so memory intense that opening the wb took five minutes and then any key you pressed, even in other workbooks, caused visual calculation lag.

The trick I used to access the main sheet's current data without opening the workbook was to create an "image" of that sheet's values in the new workbook using simple "='C:\MyFiles\[99-3z-JobAssignments-v69.xlsx]RoleAssign'!A1" formulas on the image sheet, and this formula was inserted dynamically, meaning he could select any "version" of that workbook and the correct direct links above would be inserted into the IMAGE sheet to recreate the data. The end result is very fast and we were then able to do any calculations we wanted on that data, then repeat again with another workbook.

So it's possible to run some FORMULAS against a known workbook/worksheet, and it's possible to grab ALL the data from a sheet in a closed workbook without opening from which you can then do just about anything, your REGEX tool would definitely work.

ADODB speed will be a subjective thing, only you can say how efficient the results "feel".

Google excel vba use adodb to read excel files and lots of opinions exist, I think you're just going to have to try it.
http://www.exceltip.com/import-and-e...oft-excel.html

I do not think you will obtain cell address using ADO. Also it will not be efficient if you must query all columns for your data. I think opening workbook would be faster. Windows search is perhaps cheapest solution.

I believe code you have may work if long text is in first rows of workbook.

Also you do not require LongPtr for your variables - this is only for memory addresses (window handles, pointers and so on).

Please Login or Register  to view this content.

if you do not use headers.