Input more than one Excel file in one macro

[asteryx]

I have a simple workflow that reads an area from an Excel sheet into a database.

But when I try to turn it into a macro that runs through a directory full of Excel files with the same structure and imports all the areas from each file, it never works.

I've looked at all the examples of importing many files/many sheets, and I cannot get mine to work.

I use a directory tool to feed the full path to my macro.

In the macro, the Control parameter takes the full path and feeds it to the Action tool with a "replace string" option that equals the full path of the Data Input tool.

The Data Input tool has a SQL query to target the cells (Select * From '<sheetname>$c6:c72').

The whole process works, but only for the first file in the directory. I do not need to import more than one sheet per file and only need the specific range above for all of them.

(Note - the attached uses an example directory and filename, so ignore the error there.)

Thanks for any suggestions and help!

[asteryx]

Debugging strategies probably deserve their own topic.

Thanks for the advice.

[asteryx]

A couple of additional tricks that my workflow needed:

- Since each sheet represented a different record, we had to add a record ID to each and then pivot the results on that ID (Cross Tab tool). Otherwise, the output would have been a single column with all the data unioned one after the other.