Need a Macro or UI Bot for capturing company responses from a same output file.

[pranit1997]

I have attached a file which is Survey response file.

Similar to this I have 100 company response files and I want to extract it in a desired format and then analyse it.

I have prepared the workflow for extracting it in the desired format but I need help with preparing a macro so that I could run all 100 responses together and all 100 files together in a single file or multiple files but at one go. 

Thank you in advance.

[grazitti_sapna]

Hi @pranit1997, would it be possible for you to share a sample file with 2 or more companies?

Thanks. 

[pranit1997]

Hi

Thank you for helping me out.

Company response files are in the format as attached only the details would get changed.

[grazitti_sapna · Accepted solution]

Hi @pranit1997 , I have prepared a mock workflow I hope it helps.

1) Here you need to provide the path of the files that you want to extract.

Here i have created a macro that will read the path of your file one by one and will give you collaborated output.

Result-

2) If you want the result to be in separate files then you can attach output tool at the end instead of browse tool and follow this config.

Let me know if this works for you, also please mark this post as solution if this is what you are looking for.

Thanks.

[pranit1997]

Hi 

facing this error

[grazitti_sapna]

@pranit1997  where are you facing this issue? can you share the directory tool output with me? It will work for excel files in case you are using csv then you have to change the settings in the macro itself.

Thanks.

[pranit1997]

Hey

Sorry my bad.

So the company response file has about 8 sheets.

But the required data has the sheet name as "SG&A data and activity drivers" for all response files.

[grazitti_sapna]

@pranit1997 . can you try selecting the sheet and then try to run the workflow?

Thanks.

[pranit1997]

Not able to select the sheet as I am only able to give the reference for the folder.

[grazitti_sapna · Accepted solution]

@pranit1997 , will it be possible for you to share a sample file with 8 sheets or try this.

Use a formula tool and pass the sheet name hardcoded to the a new column.

Use this new column in the control parameter field.

This works for me.

Thanks.