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

[LordNeilLord]

The same process will work with XLS...you just need to make sure you have the Access 2010 driver installed to read XLS files 

https://www.microsoft.com/en-us/download/details.aspx?id=13255&751be11f-ede8-5a0c-058c-2ee190a24fa6=...

[LordNeilLord]

Yeah it's a bit rubbish, I don't know why it's still not fixed (unless its intentional)

[kellyreburn]

After the macro, it is only showing the sheet names. How do I connect it to the other data that is pulled using the directory tool?

[NidhinGD]

The final result have file name (File path) and sheet name.Replace the <List of Sheet Names> in the filename field and send it to a macro for reading the excel.

Replace([FileName], "<List of Sheet Names>", [Sheet Names])

Thanks 

Nidhin

[jenv20032000x]

Hello and thank you in advance for helping me resolve the issue I'm having with the Directory flow you provided.  When I use the second "SheetNames.yxmc" macro I get the error "Sheet Names" is not contained in the record.  I mirrored your flow and macro.  I've attached the look of both the flow and the SheetNames macro.

[petause14]

@LordNeilLord

I was searching for a macro like this and it's very helpful!

Is there also a way to only import the first n columns of each sheet?

[Cidney]

Hello, 

In your example, the directory is pointing to yours, what do i need to change that too? In the beginning on my WF I am going into a directory and grabbing 10 spreadsheets. I need to now read all the tabs and data within those sheets. This WF works for me as is with your macro (not sure why as it is pointing to a nonexistent directory of yours).

[usmanbashir]

Exactly what I needed - worked like a charm! Thank you!

[AmitojSingh]

i tried using this macro but for some reason i just get the sheen names - i am referring to the first part of macro, it wont bring the file names just the sheetnames for odd reason - any suggestions, why would it act differently for me

[are__1f]

A little bit more optimized or you can say a different approach to achive this.