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

[MM_Chris]

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?

[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".

[Claje]

Sorry - I was responding off the top of my head and didn't actually throw together a concrete example, so I missed part of it.

So today, you have an Input Data tool which you are updating using an Action configured to "Update Value", and you chose the text to replace as your excel filename from the "File - value" piece of the "Value or Attribute to Update" menu.

For this example, I'm going to assume that the input file you used is called "Replaceme.xlsx" and it has a sheet name of "Sheet1".  As such, replace any instance in my solution of either of these values with the correct file name and sheet name.

Based on this, your formula would look something like this:

IF [#1] = 'BadFileName' THEN REPLACE([Destination],'Replaceme.xlsx|Sheet1',[#1]+'.xlsx|Sheetname') ELSE REPLACE([Destination],'Replaceme',[#1]) ENDIF

Replace the values "BadFilename" and "Sheetname" with your misbehaving excel file name and unusual sheet name, respectively.

If you continue to have trouble, I can take a sample file and build out this Action tool to show you what I mean.

[Claje]

Duplicate

[MM_Chris]

So here's a mock of exactly what I'm trying to do so you have a better idea. And let's say I want to change the tab names based on the selection of Apartment.

[Claje]

Sure, this is helpful, thanks!

So I'm assuming that if you open Apartment Datasheet.xlsx you want the Table "Rental", and otherwise you want the Table "Rent", but that "Rental" and "Rent" have the same structure and will be processed the same way.  If that assumption is wrong, the solution changes a little.

Here's a corrected version of the formula I gave you before, to tie into your example.  You would paste this into the Formula space at the bottom of your top Action (Update Value with Formula)

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

[MM_Chris]

So I am trying that solution and it seems when I pick "Apartment" it still uses Condo.

[Claje]

Based on that issue I'm guessing you don't have the right line selected in the "Value or Attribute to update" section of the Action tool.

here's an example of how that should look - note that the line with 'File - value ="Condo.xlsx|Rent"' is highlighted.

[MM_Chris]

I do have that selected, but for mine it looks like this Condo.xlsx|||Rent