Get worksheet names from excel

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

ReadFile_Directory.yxzp

Dynamic Processing

Accepted answers

LordNeilLord

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

Directory.yxzp

All comments

WillBowman

Totally misinterpreted. There are some good solutions here!

LordNeilLord

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

Directory.yxzp

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.

List of sheet names.png

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).