Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.
Announcement | We'll be doing maintenance between 2-3 hours, which may impact your experience. Thanks for your patience as we work on improving the community!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

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

D_Y
8 - Asteroid

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!

5 REPLIES 5
csmith11
11 - Bolide

"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
8 - Asteroid

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 

binuacs
17 - Castor

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

 

binuacs_0-1641486358026.png

 

csmith11
11 - Bolide

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)

 

 

csmith11_1-1641489176597.png

 

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.

 

 

 

 

 

 

 

 

D_Y
8 - Asteroid

Thanks for laying this out! I can see how using Alteryx Server may complicate things. I'll see how it goes to mimic your workflow

Labels