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.

[Qiu]

@aparna0208 

Maybe this is what you need?

Please adjust the details according your real situation.

[Meet_Nandu]

Yes, you can give a directory input and run a workflow for each file in the folder. To do this, you will first have to give the directory input to the macro as -

The macro workflow will look something like this -

You can update the macro workflow ahead as you want to process each file. As you can see, in the action node (which is highlighted) I am updating a certain value. This value is updated to read the path of each file. So after one file is processed, the action node will update the input path of the next file in the directory and will process the same workflow for the next file.

In the normal workflow, you have to mention what value must go in the control parameter. This will be the FullPath which you will get from the Directory Input node. 

You can try this and let me know in case you have any issue.

[aparna0208]

@Meet_Nandu thanks for your inputs. If you don't mind can you please share some sample workflows/macros as I'm not able to follow the whole thing from the image?

[aparna0208]

@Qiu thanks for your help. I'm having some trouble opening the attachment after importing it so still looking into that issue.

[Qiu]

@aparna0208 

Let me know if I can be any further help.

[Meet_Nandu]

@aparna0208 sure. I have shared a Sample workflow and the batch file used in the workflow as well.

In the Sample workflow, I am giving a directory input which reads all the excel files in that folder. 

In the macro (Sample batch), I am appending all those excel files and getting the output as a single file. You can change the workflow inside the macro as you need. 

Do let me know if you need any more help.  

[aparna0208]

@Meet_Nandu sorry for some reason your response was not showing up last week and now I'm able to see it. Will try your examples with my files and see if it works and get back to you with any questions or issues. Thanks a lot:)

[aparna0208]

@Meet_Nandu Sorry if I'm not getting this right. I'll tell you what I did and let me know if I'm doing it right. I tried your example(macro) by connecting it to my existing workflow which does a bunch of different things based on the incoming file as seen below. It worked fine but looks like every time I have to select the .xlsx file in the input tool. I'm wondering if there is a link between the sample workflow and macro you shared. I tried opening your sample workflow and it was disconnected and errored out as indicated in second snapshot. All I'm looking for is a way to first read all .xlsx files available in the folder and process it one by one. Like first .xlsx file runs through the whole workflow and once done the next one is picked and same thing continues till the last file in the folder is processed.

Kindly advise.

[Meet_Nandu]

@aparna2808

So from what I understand, you have a folder containing all the excel files. So you will have to give a directory input tool. After that, you will have to import the macro you have created in your workflow. The input to this macro will be FullPath from your directory input tool. 

Inside the macro, you will have to select a default .xlsx file in the input tool. This value will be updated with the help of action tool. (The one which looks like the action board for movies in black) In the configuration for action tool, you will have to select:

Select an action type: Update Value (Default)

And at the bottom there will be a checkbox to replace a specific string. You will have to check this and give the default value that you gave in the input tool. 

The action tool will then update the path for each file it gets from the directory input tool. This way you can process the macro each time for each file.