This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I'm currently trying to build out a batch macro and I've run into a bit of a wall. I'm currently using two control parameters to update the inputs with the FullPath of the excel files that will be running through the macro. What I want to add is a third, fourth, and fifth control parameter that influence the selected columns in a Unique filter. Is that possible?
For clarification, I have an information Schema that has all of the columns listed out for each excel sheet that I am pulling in. The purpose of the macro is to pull in two different sheets and help identify the differences between the two. The first Unique would separate all the values that fully match, the second would separate all those with the same values but a different index, and the final would identify those rows whose values have changed.
I've attached the an image of a version that only has one Unique tool.
EDIT: I'm now realizing a control parameter is probably not the way to handle this. I don't want to run a batch on every single column name. Is there any other way to dynamically change the columns selected in the Unique Tool?
While I think it might be possible to use one or more action tools connected to your unique tool to update the field list and field to perform the unique test on from an earlier part of your macro, I think it could be very tricky an problematic.
Would it not be possible to use field info tools and a join tool to identify matching and non-matching columns in your 2 datasets?
I thought about using Field Info but the issue with that is that the column names are the same in both data sets. To further clarify my issue, these datasets are being pulled from a SQL Database. The same table in two different instances, as a matter of fact. I want to be able to compare them to see what has changed. However, I want to build a macro for this that is dynamic so I can use it for multiple different tables.
IDEA: What about using some sort of interface tool to update the XML of the Unique function?
The thing about interface tools like List Box, Text Box or Drop Down is that they're designed for user input at run time.
It sounds to me that what you're looking for is for the Unique Tool to be dynamically updated with both a list of fields and the Fields to be used by the Unique test to from the data that flows into your macro.
Those interface tools won't help you with this. I think what you're looking to do is to have a macro within a macro.
I think you need a macro that only contains an input, output and a unique tool with a control parameter and one or 2 action tools to define the list of fields and unique fields of the Unique tool. It sounds like the kind of thing there surely must be a Crew macro for.
You can then use this Unique macro in your other macro that compares the 2 sets of data.