Get worksheet names from excel

[NidhinGD]

Hi

I couldn't find the solution i was looking for.If this is a repeat question,I am sorry.

I have multiple excel sheets which i will be reading by using Directory tool and a Batch macro.

I am facing problems to do this when there are multiple sheet in the excel.

I tried with Dynamic input to fetch the sheet names of the files being passed through the Directory tool,but the limitation is that the Schema should be same.

I am working with Files which will have different schema for files and worksheets.

I only want to get the sheet names of individual files (Full path will be passed via the Directory tool).

I tries creating a macro by passing the Fullpath|<List of file names> to a macro where i use the Text Parse to fetch the sheet names.But this approach also failed due to the schema

Attaching the file i tried out with.

Thanks

Nidhin

[WillBowman]

Totally misinterpreted. There are some good solutions here!

[LordNeilLord · Accepted solution]

Hey @NidhinGD

I built you a little workflow to do this.....the first Macro reads all of the sheet names and the second macro imports those sheets into a single file

[BenMoss]

You can avert the schema issue you are having with the list of sheet names by going to the interface designer, and then the settings pane and set this too 'auto config by name rather than error if different.

This is an issue I have faced in the past when trying to create a similar macro.

Ben

[ChrisTX]

Can you use the Input Data tool to get the list of sheet names?  See attached screenshot.

[NidhinGD]

Hi @LordNeilLord,

Thanks a lot for the solution.This is exactly what i wanted.

A quick ques though.

I will only be needing till the First macro output as the intention is to get the filename sheet name combination.

If I remove the formula tool after the first macro Output,I am not getting the list but if i keep it (the formula tool after the first macro Tool:Formula 7)the Macro out and Formula out will both have the same output (List of filenames and sheet names).

Am i missing anything ?

Nidhin

[NidhinGD]

Hi Chris,

As i am reading a set of files,input tool will not help me.I gave it a try by setting a macro out of it (attached in OP) ,but it didn't workout.

Thanks

Nidhin

[LordNeilLord]

@NidhinGD

Keep the formula after the first macro as it strip "|||<List of Sheet Names>" from the filename.

Also with a batch macro, you need a downstream tool to make sure it runs through all of the iterations (a known bug in Alteryx)

[NidhinGD]

Hi @LordNeilLord,

Sorry to add on.

Is there a similar approach to read .xls files as well ?

Thanks

Nidhin

[NidhinGD]

@LordNeilLord

Didn't know that (Needing a downstream tool for Macro).It's a useful info.

Thanks