Clear macro output after each iteration

Dear all

currently I'm struggling with the following task.
It would be great if you could help me.

What I am trying to achieve:
With a Directory Tool I read out the files in a folder.
Then I call macro in which the List of Sheets of each and every file are read out.
Afterwards another macro is called which does some data preparation with the data in every sheet.

The problem right now is:
The macro where the list of sheets is read out appends the list of sheets of every file as the output of the macro.

How can I achive that the list of sheets is cleared after every iteration?
Otherwise I get an error message that Alteryx does not find some sheets in some files.

Thanks.

Macros

Accepted answers

patrick_digan

@RefRel I think you want to go with my first option from my first post. The key is that your monopoly macro had a static filename and was just replacing the sheet name. So you would just want to make that filename part dynamic. Here are the changes I made:

1) In Macro_Test2.yxmc:

a) Changed your input data tool to output the full path:

b) Added a formula tool right after the input tool to change the FileName field to:

Replace([FileName],"<List of Sheet Names>","`"+[Sheet Names]+"$`")

2) In the Macro_Monopoly.yxmc:

a) I just changed the action tool to replace the whole string:

I didn't test it, but it should work

Macro__TEST2.yxmc

Macro_Monopoly.yxmc

read_list_of_files.yxmd

All comments

patrick_digan

@RefRel what kind of macros are you using? Batch, iterative, standard? If you can post your workflows/macros, that may help. Even just the macros with no data may help us better understand the process. I have a couple thoughts:

1) In your first macro where the list of sheets are read out, can you change the "output file name as field" to full path?

Then add a formula like this on your filename field:

Replace([FileName],"<List of Sheet Names>",[Sheet Names])

the result will be something like "C:\Data\file.xlsx|||sheet1". Then in your last macro that reads the data in, you can have your input (or dynamic input) read that whole file path (instead of just the sheets like earlier).

2) Depending on your setup, you could "combine" your macros that list the sheets and read the data out. It's entirely possible that your unique requirements may make this unfeasible/impossible to accomplish.

Let me know if don't quite have your situation correct.

RefRel

Thanks for your answer, though I don't really get what you mean. See attached the files that I'm using, hopefully that helps.

1: read_list_of_files.yxmd: That's the starting point where the name of files of a directory are read in. Within this workflow the Macro "Macro__TEST2" is called.
2: Macro__TEST2.yxmc: Within this macro the list of sheets is read in. Within this macro the macro "Macro_Monopoly" is called.
3: Macro_Monopoly.yxmc: This macro "does the work" (data preparation etc). This macro should be run for every sheet in every file of the specified directory.

Step 2 and Step 3 already work fine together. So I am able to prepare the data within every sheet within one file. But I wan't to do this for several files.

Hope that clarifies the situation.

Macro__TEST2.yxmc

Macro_Monopoly.yxmc

read_list_of_files.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703