Excel Input Data - Do I need a Macro?

[michael_heitz]

I am working on a project, users will update comment fields in an Excel file that lives on a shared folder.

My Alteryx Workflow will be scheduled to run at the end of the day to capture all the updated info from user comments in all 29 files, and push that into a table in our Snowflake database.

The issue is this:

1. There will be 29 separate files in the shared folder, one for each of our business areas. The names will be in a format like...

"Daily Report SOUTHEAST 20181120.xlsx"

2. The tabs in the Excel spreadsheet will be named similarly to the files - they'll have the date dynamically, and the area in it - so the tab I need to capture in the file in #1 above might be called OUTS SOUTHEAST 20181120... the tab will always be the 2nd tab, but they will be named uniquely - 29 different names :(

3. Because of the file and tab naming, I don't believe I can just use a simple wildcard with an Input Data tool. My experiments with that have failed so far.

I believe I need to create a Macro tied to a Dynamic Input tool for this to function properly, but I haven't done something like that yet, so I am lacking confidence in that area  :)

Has anyone seen a situation like this? Am I on the right track?

Thanks

[michael_heitz]

OK, quick update with unforeseen issues.

Tested the workflow with the solution provided - in testing, we had no issues capturing the data from the one sheet per workbook, pushing to a Snowflake table, etc... All was right in the world!

THEN... we went live... 

Turns out the testers were not using the test files as they would the NORMAL live report... they were validating some data, then exiting the file - not much more. Yesterday was their first day live with the new report, and last night I ran my workflow to capture their updated comments.

Of the 30 files, only 1 was imported to Snowflake because of a variety of things the users did in the other files - from adding a column to renaming a field to adding a pivot table in blank space to the right of our data - all of which then changes the schema of the file and the Alteryx process fails.

So - any thoughts on how to handle something like that? LOL Or do we just have to lock the files down so they can only update the 2 or 3 columns we need them to?

Any ideas would be welcome