My solution files are attached. This was definitely a challenge for me!
Today I learned a thing. That was nice :-)
Fixes attached. I guess I viewed this as a troubleshooting problem as opposed to an automation problem. In looking at a couple of the macro solutions, neither fixed the formatting issue in File3. The metadata in these solutions showed that Column23 was incorrectly set to string instead of numeric. They also appeared to assume each Excel file would have a single sheet and that the columns present in each file were positionally correct. I think fixing the data sources is the better approach. It was good to see that was the solution in the video. It was interesting to see the use of the Field Info tool and its value in the troubleshooting. I resorted to examining the files directly.
It was definitely a challenge for me. It was straightforward coming to think of it but kept getting into "schema error" for the fourth file but finally was able to crack it just by reading into the finer details.
Here is my solution -
Anyway my approach is simple. Have a directory tool spitting out all the required input files. Connect it to a batch macro.
At the macro level -
1. Drag an 'Input Tool' and connect to one of the excel file inputs with option for 'List Sheet Names'
2. Have an Dynamic Input Tool that takes the input form the 'Input Tool' above. Here have the tool configured with option 'Modify SQL Query' and set it for 'Replace a specific string' option.
3. Have a 'Control Parameter' connecting to steps (1) and (2).
4. Connect this back to the 'Macro Output'. You are done. It is at this point you need to go to 'View' -> Interface Designer -> Tools and set the option - 'Auto configure by Name' as shown in image 3
Special thanks to @mceleavey for his valuable input regarding 'Interface Designer' within the macro page, never knew such option existed.