Reading Multiple Files and Tabs

[slckkrky]

Hello all

I am trying to read multiple files and tabs in the same folder.

- There are two files for each with 2 tabs.

- File names are slightly different but body of the name is same.

- Sheet names are same.

- Data does not start from same row in every sheet.

- Data and structure between sheet A and sheet B are different.

   File1 - sheet A and File1 - sheet B (This is the file with actual data)

   File1Template - sheet A and File1Template - sheet B (This is the file with dummy empty data which i exclude later on)

I tried Directory and Dynamic input tools. However Dynamic input tool is giving an error as " No sheet is specified....". However i specified the template sheet and as a result it is reading from there.

Why is this error happening? Is there a way to solve this without macro?

If macro is needed because of different sheet names, Could anyone share step by step screenshot please?

I read all the articles about macros however they are not helping how to set up and configure each macro steps.

 

Thank you in advance

[slckkrky]

I simply pasted your workflow and started with a Directory tool on myside.

This is also helping me to understand how to do in future.

1- I donot understand how to create new macro as in save as xyz ( your macro name is "sheet names") and use it in different part of the workflow.

in the workflow your macro is used two times and they appear to be identical. Right?

2- how to spit the source file and sheet names as an additional data.

as a summary i need amendment that will be able to read multiple files and sheets which starts from different rows and identifiers of the sheets so I can separate the data on later stages.

[slckkrky]

Thank you for your help buut this is also helping me to understand how to do in future. Because even most basic details are extremely unknown.

I simply replaced Directory tool with my Directory tool and thats it.

As a summary I need macro to be able to read multiple files with multiple sheets where data donot start from same row. As an output I need an extra field showing file and sheet name so i can separate data on the later stage usage.

1- How can i save as a macro and use it in different part of the workflow?

If i make a change in your macro, i.e. start data from row 3, then those changes are used everywhere same macro is used. In your sample macro is used twice and are they identical?

[binuacs]

@slckkrky open the workflow and save as with a different name in different location, if you want to use the new macro right click on the workflow canvas and you will see an option to insert macro in the pop-up window, from there you can select the macro you saved 

[slckkrky]

Thank you mate but i dont see any option you mention this is why if you can share some screenshot it will be very helpfull.

Macro has an image as a node. After I make changes to your makro Where can i save it?  all changes are saved in same macro.

Do i need to save every macro as a separate workflow?

Your macro is saved somewhere in my temp folders. How do i save a brand new macro without any dependeny?

also do you have an answer how to get the sheet name as an output? i still dont find it.

[slckkrky]

I found most of the answers however I still cannot get the sheet names at the end of the workflow. Can you help me with that?

[binuacs]

for sheet name you need to add another formula tool and extract it from the FileName field which will be in the last column of the second macro output

you can find the workflow macros in the folder where  you downloaded the zip file where i sent to you initially, check for the folder which will have the same name of the workflow inside that you will find the macro, you can copy those macro and paste some where

[binuacs]

REGEX_Replace([FileName], '.*\|\|\|(.*)', '$1')