Finding Formulas in Workbooks

[ExcelBoston]

Hi,

I need to search over 400 Excel workbooks in over 90 sub-folders for the existence of formulas entered in cells. These specific workbooks are not supposed to have any formulas in them so I’m trying to easily find any exceptions.
<o> </o>
How can I do this and either pipe the output into a text file or display it in a spreadsheet?
<o> </o>
I’d appreciate any help!

 

[Tom Urtis]

A couple questions, first, what good would it do you to list all the formulas; potentially there could be tens of thousands. What would you do with that list.

It seems that if those workbooks are not supposed to have any formulas in them, then as long as all the workbooks are being evaluated for the existence of formulas, why not just programmatically convert all the worksheets' used ranges to values and that would fix the problem...

...unless, the reason for keeping the formulas and listing them is pure curiosity to see which workbooks and worksheets and cell addresses contain formulas. Nothing wrong with that but cognitively that could be a lot of info where someone says "so what" and you'd presumably need to deal with the existence of those formulas anyway by copying for paste special values.

The second question is, what version of Excel are you using, and what version(s) of Excel are those workbooks saved as, which can potentially influence the syntax of code to do what you ultimately need to do.

 

[Special-K99]

Wouldn't selecting the whole sheet and using Copy/Paste Values achieve this in one strike?

 

[ExcelBoston]

These Excel Workbooks (remember there are over 400 of them in 90 folders) are used for Software Testing. The Spreadsheets are set up using what is called the KeyWord (or Action Word) paradigm. Essentially, the spreadsheets are test protocols used in the testing of a very large application. We have over 5000 tests that are run automatically using these spreadsheets. Some of the spreadsheets have only 500 rows of test statements where a large spreadsheet will have perhaps 7,000 rows.

Because of regulatory requirements the protocols must not have any formulas (they must be deterministic) at the time of execution.

So, I'm tasked with finding any exceptions to this mandate. It is not reasonable to find them manually so I need a script or macro to do the work.

How to do so?