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.

Sample Survey sheet.xlsx

Workflow Test Business & Operations.yxmd

Macros

Accepted answers

grazitti_sapna

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.

test.zip

grazitti_sapna

@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.

grazitti_sapna

@pranit1997 , try this.

add a formula tool and use this

[FullPath]+"|||SG&A data and activity drivers" in formula tool and it will work.

Thanks.

grazitti_sapna

Hi @pranit1997 , change the text with the path where your files are located just need to provide one path.

Also attaching the latest macro that you can replace with the older one and then edit the path.

Thanks.

Workflow Test Business & Operations.yxmc

All comments

grazitti_sapna

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

Thanks.

pranit1997

Thank you for helping me out.

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

grazitti_sapna

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.

test.zip

pranit1997

facing this error

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

@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.

Qiu

@pranit1997
I have created a standard Macro to read all the files and the processing part is from your workflow.
so only input and out are mine.

1019-pranit1997.yxzp

pranit1997

@Qiu

Was not able check your solution as alteryx versions are not matching

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703