Macros

[YelenaK1982]

Hello. I am looking to setup a workflow where it reads all the files in the folder, copies the first tab from each of the files and pastes then to a master workbook to separate tabs. Should I use a macro for this? Please help me get started. 

[alexnajm]

You should use a macro to read in the data - you can grab it from here: The Ultimate Input Data Flowchart

Beforehand you can read in the list of files via a directory, dynamic input to grab the list of sheet names, use logic to filter to the tabs you need, attach the sheet names to the full path, and then use the macro last!

[alexnajm]

Here is some inspiration: Solved: Dynamic Input - List of Excel Sheet Names - Alteryx Community

[Pilsner]

Hello @YelenaK1982 

Yes, I would recommend using a macro for this task.

I have attempted to solve the challenge myself and have highlighted the steps to help you follow along.

Just for context, I have two excel files, each file with two sheets in each. I have selected the "first tab" to be the first one alphabetically, from each file. On the new master doc, I have named the sheets with their original file and sheet names.

1) Load in file names

2) Load in list of sheet names and extract "first tab"

3)  Load in data from the first tab. Format the name for the new tab in the master doc.

4)  Output the data to the new sheet in the master doc.

 

Happy to explain this further if you would like. I appreciate the nested macros can sometimes seem daunting but each time I have effectively repeated the same trick. I have taken some information (i.e sheet name) and used the control parameter in the batch macro to update either an input/ output tool with this information.

I have attached the workflow + macros below, in case you wanted to follow my logic and look at the comments. (I have tried to make everything relative but you might need to adjust the file path on some of the inputs / action tools once you have unpackaged the workflow.)

These was my results:

 

Please let me know how you get on.

Regards - Pilsner

[YelenaK1982]

Thanks for your help. This is very helpful. Still working through it. How do I get access to the macro? I don't have that tool in the designer ribbon.

[Pilsner]

Hello @YelenaK1982 

Once you have unzipped the workflow you can access the macro by doing the following.
Right click the canvas> Click Insert> Click Macros > navigate to location where the macro is saved. 
(When you open the workflow is will create a folder in the same location as the zip file, which contains all the macros/files from this workflow).

I hope this helps.

Regards - Pilsner