Crew Macro Wildcard XLSX Input

[jeffv]

I likely missing something and maybe someone can assist.

Crew macros seem to be really useful but I can't find documentation Wildcard XLSX Input tool.  Seems odd a macro is created but not documented.  I saw one article where it said look in help which I see but still don't see anything on this particular macro.  Odd.

In any case,  trying to figure it out by playing with it, I give it a directory I need xlsx files read.  It seems to me (someone please guide me)  that it looks at all sheets in the workbook and tries to match all workbooks to the first workbook and if it doesn't match it sends the data out the N connector.  Trying to figure out how I can use the tool with the following scenario  I have a workbook created each day of month.  In the workbook it has say 10 sheets each with slightly different columns.  What I need to pull together all sheet1s, then pull together sheet2s from each work book and so forth.  I see something of a week ago that might help but I'm running 2018.3 and the example is created in a more recent version.

Do I have to pull in all workbooks but exclude all the other workbooks but the one I want?  I must be missing the point of the tool.

Any insight appreciated.

Thanks,

Jeff

[afv2688 · Accepted solution]

Hello @jeffv,

Try using this macro. It does basically what you are looking for.

Regards

[danilang]

Hi @jeffv 

For your version issue, try the procedures outlined in the attached document.  It shows how to convert Alteryx workflows and macros to be used in older versions of designer.  With this you'll be able to open the solution that you found last week.

Dan

[jeffv]

Hi,

Thanks for feedback.  I tried to open workflow and it failed so I edited the file and changed second line to 2018.3.  Then I tried to open file however, it gave the error that the sheet reader macro was not found.  Any further clarification to get it to work on my version?

Thanks,

Jeff

[danilang]

Hi @jeffv 

You'll need to modify the version on the referenced macro files as well.  These will be in the workflow directory, or the .\externals folder.

Dan 

[jeffv]

@danilang,

Thanks for response...  After changing the other macros I was able to run it.  I get a few warnings (maybe ok to ignore?) that say: There is no Question corresponding to the specified App/Macro value "Kill Excel Tasks".  The App/Macro may be out of date.

I'm looking at output and trying to understand what the provides me.  The output shows fields f1 through f28 with last field named FileName and giving the full path (including sheet name).  It appears scattered down the rows for each of the files there is a row containing the field names. I'm hoping I can take the fieldname row and apply to data after I separate rows by workbook name.

Am I understanding correctly?

Thanks!

Jeff