Clear macro output after each iteration

[RefRel]

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.

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

[patrick_digan · Accepted solution]

@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 :)

[RefRel]

Hi Patrick

Thanks a lot for your reply.
Unfortunately it did not work.

At first there was an error regarding your attached files: This document was created by a more recent version of this application and cannot be read.
I did not know that Alteryx files are not downward compatible.

So I tried to follow your description and either I did something wrong or your solution just does not work ;).

The errors occur already in Macro__TEST2 when the macro "Macro_Monopoly" is called.

There are a lot of errors looking like this: 

Error: Macro_Monopoly (1): Record #56: Tool #1: File not found "C:\Users\byf\Desktop\Alteryx Training\1069"

It seems like that Alteryx tries to handover the sheetnames as filesnames (1069 is a sheetname in one specific file).

Best regards

[patrick_digan]

@RefRel Hmm...I must have been just a little off. I'm attaching an alteryx zip package that has a working example (it should work in 10.0+). Assuming that works for you, you could then change the directory tool to whatever directory you had originally and verify that it's working as expected there as well.

Let me know if that resolves it!

[RefRel]

Hi Patrick, 

I think the problem is the interface between Macro__TEST2 and Macro_Monopoly.

So what Macro__Test2 passes to Macro_Monopoly is the following:

So the output passed to Macro_Monopoly is the filename including the sheetname.

But then in Macro_Monopoly (I guess) that only the part below "Connect a File or Database" is replaced which only consists of the filename without (!) the sheetname:

[patrick_digan]

@RefRel I think you're right there! In your Monopoly Macro, what does your action tool look like? Here is what mine is setup as:

So I've selected the File - Value line, but at the bottom I've unchecked the replace a specif string. That way it will take the whole filepath plus sheet from your control parameter and pass that to replace the filepath plus sheet already in the input tool.

[RefRel]

I think I figured it out - thanks to your help.

The problem was the setting "Choose Control Parameter" for the Macro "Macro_Monopoly" within the Macro "Macro__TEST2.yxmc".

I changed it to "FileName" and now everything seems to work.

Thanks a lot for your help.