Add sheet name when importing multiple sheets using macro

[Carlyn]

Hi

I am working on a macro to import multiple sheets from an excel file.

I tried Dynamic input initially, but as the tabs have different schema this wouldn't work.

So next option is a Macro.

It appears to have worked, however, I am looking to add a column with the sheet name on the output.

Can someone help me with how I can do this please?

[OTrieger]

@Carlyn 
You need to select to open in the input data tool, 5 Output File Name as Field, select file name

[ChrisTX]

Inside the macro, in the Macro Input tool, under Template Input > File Input > Configure, use the drop-down for 5 Output File Name as Field.

 

Chris

[Carlyn]

Hi Chris

Thanks for responding. I am not particularly comfortable with macros yet in Alteryx so this may seem like a daft question, but when I change to using the macro input tool and then try to link  it within my workflow by replacing the general input tool, there is no way to connect it that I can see. 

 

When I try to update option 5 in the general input tool I get the following error.

 

 

Do you know what I am missing/doing wrong?

[OTrieger]

@Carlyn 

The path is wrong and that is the error that you get.

[OTrieger]

check the formula tool as there path is been set there. You need to get a full path, .......file,xlsx|||Sheet1  or something like that, depends on the sheet name

[OTrieger]

Does your Data input tool is set for List of Sheet Names?

[OTrieger]

As you can see from the formula tool that to get the full path <List of Sheet Names> is been replaces by Sheet Name.

[ChrisTX]

@Carlyn see if this helps you:

How To: Import Multiple Excel Sheets or a Specific Excel Range into a Workflow

https://knowledge.alteryx.com/index/s/article/How-To-Import-Multiple-Excel-Sheets-or-a-Specific-Exce...

And an example here in the Solution to weekly challenge #412: Superbowl Time —Lowest-Ranked Football Positions:
https://community.alteryx.com/t5/Weekly-Challenges/Challenge-412-Superbowl-Time-Lowest-Ranked-Footba...

Chris

[JosephSerpis]

Hi @Carlyn have a look at the marked solution in this post.