Extracting Data with Multiple Macros.

Question

Hello,

I am working on a project where there is a couple of macros that are missing and need to be reinvented with a current workflow that exists.  The workflow is using the directory tool then a formula tool to break it down to the sheet names, then a macro. Also, within the directory tool, the location needs to stay the same, but the excel sheets need to be able to get changed.  I have attempted to use a batch macro that has came close to fixing the first macro issue but it is not pulling the correct information.  If anyone can help, it would be greatly appreciated.

Weekly Charting.yxmd

OTrieger · Answer

@rachelforbes 
There is an important lesson to be learned here.

As best Practice all ways save the macros that you are using in your WF in the same folder where the WF itself is saved.

When uploading a WF that contains macros to the server always mark them as assets.

What do you mean by incorrect information? I cannot download your WF as it contains macros.

I prefer using the 2nd choice as you have more freedom what to do with the data and how to set it up.

DanM · Answer

@rachelforbes

There's a couple of ways you can pull data from a Directory tool. What you have mentioned so far is correct that the you use the Directory tools to get the sheet names. The first way is you can use the Dynamic Input tool where you configure the tool to the location of the files and then use the field from your Formula tool to update the sheet names. That will pull in all the data from the directory. The downside of this method is that your schema must be the same for all files.

The second way is a batch macro. To do this, open a separate workflow, add an Input tool and configure the tool to one of the files. Then add a Control Parameter tool and connect it to the Input tools at the top of the tool. An Action tool will be applied in between the tool. Configure the Action tool to only update the sheet name. To do this you will remove all of the text besides the sheet name at the bottom of the Action tool. Here's the important part, if your files do not have the same schema, you can open the Interface Designer found in view click on the gear and change the macro to "ignore the schema". Then add an output tool and save as a macro. Sample attached.

Input Macro Sample.yxmc