Using ADO with non-standard data sources - Ch. 20
Hi all, I was wondering if someone can help me with this question. I can successfully use the ADO method in the book to search through a massive amount of spreadsheets in distributed folders to look for information on the sheets. However, I noticed that the code example heavily depends on knowing at least the sheet names ahead of time.
Is there a method for getting the sheet names similar to getting a list of table names when the sheet names are unknown?
I have to search through the list to find every instance of a workbook with a particular string subset in the sheet names.
For example, if I were looking for 'xyz' in the sheet names, I would get the sheet names and do a string test 'intPos = InSt(sheetname, "xyz")' for each sheet, and if intPos is greater than zero, it found it. But, I need to get the collection of sheet names without the hassle of creating a new instance of excel opening each and every book to get the names.
Thanks for any help! :)