Using action tool to update the TAB/TABLE selection in an excel sheet for input

Hi there,

I am currently working on a project that requires me to read in an Excel file from a directory. In order to do this I am using a drop down and manually setting values to update the file name based on what was selected. For example if I selected "House" it would update the path to House.xlsx. So, there are two tabs that I need from each datasheet. I have two file inputs, each with one of the tabs selected, so when I update the name of the file it still selects that tab.

The problem that I'm running into is that one of the Excel file's tabs are named a bit differently (I am not allowed to change it to match the rest). So when I choose that Excel file the tab names don't register because they're named differently. Is there a way I am able to update the tab names selected using a formula in the action tool?

Tips and Tricks

Apps

Interface Tools

Accepted answers

Claje

You're on version 11.0 or later. That is my fault! I'm using an older version of the tool, which explains why my solution wasn't working.

Here's a corrected version of my prior solution:

IF [#1] = 'Apartment Datasheet.xlsx' THEN REPLACE([Destination],'Condo.xlsx|||Rent',[#1]+'|||Rental') ELSE REPLACE([Destination],'Condo.xlsx',[#1]) ENDIF

All comments

Claje

I'm assuming that the filenames are consistent.

There is an option in the Action Tool called "update value with formula"

You could write something like:

IF Filename = "Wrongname" THEN "Wrongname.xlsx|WrongSheetname" ELSE Filename+".xlsx|RightSheetName" ENDIF

you could also leave your current file name update piece alone and add a second action which only updates the sheet name using a similar set of logic.

MM_Chris

Hi Claje,

I'm not using a directory input. I am just updating the file name within the full path that's given in the input file. How would I apply this logic if I were to stick with this? I also tried using a directory and it says "Unknown variable: FileName".

ClumsyLoki

For those cannot solve their problems by referring to solution previously.

Found another possible case: - not using Update with Formula in Action box

Following below and using Update value and replace specific string, I have no corrupted excel anymore

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/OutPut-xlsx-format-tab-name-garbage/td-p/23875

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335