The Challenge: Pulling in data from unpredictable Excel input files is difficult because the format and sheet names and columns can change.
The Solution: Load all the data in all the sheets from all the files as String type, and then use downstream tools to get to the data you want.
The Value: We can now create an input directory and flexibly extract the required data by intelligently finding it in the source files. If Nigel in Accounts adds a new column in the middle of a sheet, the macro does not break.
This macro set can load any data from variant Excel files and blend it together. The file and sheet name are appended to each record, so that once the data is loaded you can filter out just the files and sheets that are relevant to your problem. If the format off the Excel files changes, the macro does not break.
HOW TO USE:
The YXZP file contains "ExcelHoover_Harness.yxmd" and "ExcelHoover_byFullPath_Harness.yxmd". These are example workflows, which load some example Excel files that are also in the YXZP.
