Highlight cells with a certain value and then output to a specific range of an Excel file?

Hi there

I am trying to highlight cells that contain a certain value and then want to output to a specific range in an existing Excel file.

I know how to do both of these separately:

1. I need a Table tool to build the column rule for highlighting the appropriate cell

2. I need a Formula tool that appends the specific range I need to the existing Filename field, e.g. [Filename] + |||Sheet1$A2:B7

3. I need an Output tool that has "Change Entire File Path" selected and outputs to the Filename from 2.

I don't know how to combine these together. It seems like I can't have a Table tool and Output tool used together, i.e. I can't see any highlighted cells.

Any help is appreciated!

Output

Accepted answers

csmith11

While it may not be a complete deal breaker the permissions and modification required to implement this method on Alteryx Server can be a headache (See Issues at bottom of post)

I couldn't find my old post. So I'll outline the solution here.

Basically, you'll use a BAT file to copy 1 file path (YOUR TEMPLATE FILE) to another file Path (YOUR OUTPUT PATH).

The Run Command Tool will Create the BAT File using the code in the formula tool, and execute the BAT file.

After the Bat File runs, you will then be able to write to you output path using a specific range. (Note the Parallel Block Until Done Macro)

For your use case, you'll want to use conditional formatting as part of you template file.

Issue/Modification for Alteryx Server:

If this is to be implemented on Alteryx Server, You'll need permission to run BAT file on the Server. Your Server will need access to a Shared DRIVE (WHERE the template and output will be located) You'll also need to use UNC paths.

RunCmd.zip

All comments

csmith11

"I don't know how to combine these together. It seems like I can't have a Table tool and Output tool used together, i.e. I can't see any highlighted cells."

You are correct.

The best solution that I'd recommend is use Excel Conditional Formatting on a Template File. Alteryx will write to the specific Range with an Output Tool. This will achieve your desired result.

Now implementing this is a lengthy process as you'll need to create a template file that can be written to. And you'll find that you don't actually want to write to the same template file again and again and again, so you'll need to know how to make a copy of the template file first.

Couple of Question: Will this be deployed on Alteryx Server or just Alteryx Desktop?

What Version of Alteryx are you using?

I'll grab a link where I've already discuss how to implement a Template Excel File Solution.

D_Y

Thanks for your help.

We will likely be deploying this to Alteryx Server, though if it is a dealbreaker we can stick with just Designer (not as ideal).

I'm using Alteryx Designer 2020.3.5

binu_acs

@D_Y you should use a render tool to output the table result , i dont think the range functionality work

Sample3.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703