Macros

[aparna0208]

Hi,

I have no clue about macros and I did see some articles and videos but not able to understand if I can use macros for my logic. I already have a workflow in place to process some excel files where it does bunch of different things. I have to process files one by one with this workflow. I was thinking if there is a way to process all at once. That's when I started playing around with tools and I found this directory tool. This reads all the files in a particular folder which is fine. Now I'm trying to see if there is a way to read all files at a time but process it one by one from start to end through the existing workflow.  Each file from the location needs to go through the whole workflow before moving onto the next file rather than bringing in all of the files together and then run them as one output before splitting out each file right at the end. So I was wondering if this is something that could be achieved with the help of macros. Looking for some help! Thanks in advance.

[aparna0208]

@Meet_Nandu 

Yes, you got it right. That's what even I'm looking to do. 

[aparna0208]

@Meet_Nandu Hi! Could you please provide some thoughts or suggestions on this? I am having a hard time implementing this logic. As I had mentioned earlier, I tried to open your sample workflow but there is some error. This is one of the biggest task I'm trying to do and if I get it to work then it would be great.

I have an existing workflow as seen below where I connect the file that needs to be processed to the input tool and run it every time. Now how do I add macro and directory tool to this existing workflow

The logic that you have explained in your previous reply is what I'm trying to replicate. I first used a directory input tool to read all .xlsx files. After reading all the files, should I connect the macro or my existing workflow to the directory input tool? This is part that I'm quite confused and not doing it right. Kindly advise. Thank you in advance!

[Meet_Nandu · Accepted solution]

Hi @aparna0208! 

First, you would have to create a macro of the workflow you want to run multiple times. So, you can create the macro by selecting the macro option in configuration pane, like below - 

After, you have selected this option, you will have to add two new tools - Control Parameter Tool and action tool (the ones in black)

The control parameter tool will read the path of each file one at a time and the action tool will update the path of the input tool with the value that comes from the control parameter tool. You will have to give the configuration of the action tool somewhat like this - (Please don't forget to tick the replace a specific string at the bottom)

 

You can then add your existing workflow after the input tool. Once you save this, it should be saved with a .yxmc extension. 

After this, create a new workflow as below - 

 In the input directory tool, give the input of the folder where all your files are located. The formula node is not necessary. (I needed it because I wanted to write the files in a different folder) Then you will have to import the macro you created by right clicking on the canvas and then selecting insert and then the last option should be macro. (You should import the .yxmc file you created) The macro will ask you to select a field for the control parameter tool. You must select the option FullPath. This way the macro will be fed the full path of each .xlsx file you want to run. 

Hope this helps you! If not, feel free to contact me via LinkedIn or private message me here. 

[aparna0208]

@Meet_Nandu First of all a big thank you for patiently answering all my questions and providing inputs and examples line by line:)

I want to explain what I did and how I modified few things and then wanted to know if I can tweak it a bit. I got the output just the way I wanted where it reads all excel files in that folder and processes it one after the other. I'm so happy that I was able to make it work to this extent with your inputs.

As you can see in below snapshot, that is the final workflow that I built by following the steps you had explained in your response. I connected the directory tool to the macro(blue circle) which contains the existing workflow. I hit run and it automatically processes all the .xlsx files and shows the progress as record#1 or 2 or 3 based on the file it's processing and it runs through the existing workflow from start to finish and outputs the file

Now I am going to just quickly explain how I modified it and ask few questions that I have and want to see if that's doable

The below snapshot indicates my existing workflow. As per your inputs, I wasn't able to implement step 1(creating macro of workflow I want to run multiple times).

The reason being my existing workflow starts with the input tool where I connect it to the excel file and run it. The first step you had mentioned also includes an input tool so I wasn't able to connect two input tools(connect output from one to another). So, I just tried by connecting my existing workflow directly to the batch macro and changed the settings to macro as you had mentioned.

Question 1:

My question here is will this be an issue as I skipped the first input tool and directly connected the macro to my workflow?

Question 2:

As you can see in below snapshot, there is a text input tool where I used to input some parameters like unique identifier values, list name etc based on the file I run. Now that this macro picks up one file after the other and processes it in a sequence, I am not able to input any parameters in that tool. Just trying to understand if there is some way to do that?

Question 3:

Also, when I connect the file to the input tool in the existing workflow, I used to manually select the sheet that has the actual data and then run the workflow. Again in this macro process that is another thing that is lacking. When we get files to process one thing to note is not all file will have the actual data in sheet 1. I open the file and check the sheet and then select it when I connect to that file.

All questions I have asked above is to see if I can replicate the steps that I do during manual run for each file into this batch macro process. 

Once again thanks a lot for all your help on this:)