Sheet Name Variable

[rparr009]

All,

I am trying to set up a variable for my sheet name so that it is held constant as ComCon no matter what file I put in the excel reader.

Right now, every time I add a new file I have to go and select the sheet name.

Could anyone help?

[bruno29a]

Hi @rparr009 , the only thing that you need really is this:

That is provided that your variable ComCon contains the value ComCon.

The rest can be left as blank.

Can you please show the results of your String Configuration?

EDIT: I just realized that you showed the configuration of the String Configuration node.
The issue is that you are not using ComCon as variable name:

The node should be configured as follows:

I am not sure where your variable ComCon is coming from, but it’s not from the String Configuration.

If you don’t plan on having interaction where someone is going to enter a value, you can use Variable Expressions instead.

I put something together for you, which looks like this:

The Variable Expressions looks like this:

I basically defined a variable called “sheetname” and assigned the value “ComCon” to it.

I have 5 sample Excel files, each having a sheet called “ComCon”.

The Excel Reader is configured as follows:

And I can point to any of the 5 files without having to specify the sheet since the flow-variable is taking care of it. For example, for file 2:

For file 4:

It automatically points to the ComCon sheet without having to change anything.

Here’s the sample workflow (Sample Excel files included in the data folder):
Sheet Varible name.knwf (35.1 KB)

[rparr009]

Is this what you mean?

And then here is the results of string configuration.

[bruno29a]

Hi @rparr009 , I edited my post when I realized that you shared the configuration of the String Configuration, but you had replied before I finish editing. Please read from the EDIT part, and you will get the answer

[rparr009]

Hi Bruno,

First off thanks so much for such a detailed answer!

However, when I link up your variable expression node, anytime I switch files I still have to click select sheet with name. Additionally, if I change the text in a variable expression to be another sheet name in the workbook nothing get changed in the excel reader node.

[takbb]

Hi @rparr009 , what @Bruno29a has said is right, but what you also need to do is actually manually select the “select sheet with name” option on the config, which I can see from this screenshot you haven’t done.

otherwise, it will just ignore the sheet name that you are passing it (which I think is what it’s doing).

That’s all you should need to do. If you really want to be certain, you could create a String flow variable that is passed to the “sheet_selection” on the Flow Variables config tab (which I know is kind of where you started), but rather than passing the sheet name into that flow var, you would need to pass the literal string “NAME”, as this is the flow variable setting that defines which of those options buttons are selected.

However the additional flow var shouldn’t be necessary as long as you select the “Select Sheet with name” option in config for Excel reader, and press Apply and OK (or probably just OK but better safe than sorry!)

[izaychik63]

I personally use different combination of nodes.
Start with Local File Browser Configuration — NodePit
to pick the file and pass Path variable to the Excel Reader.
In excel reader I specify the tab name. It will not be reset by new file.

[bruno29a]

Hi @izaychik63 , I think the Preview sometimes does not refresh, if you click on the File Content tab, it usually refreshes:

You can also simply run the node, it should collect the data properly.

As per @takbb , you can also skip the variable altogether and hardcode ComCon as the name. However, if you want to separate this as a separate configuration, you’d use the Variable Expressions or the String Configuration (as you had originally. Just use the proper variable name) nodes.

[izaychik63]

@bruno29a , I’m not saying about refresh view but about tab name hardcoding. My solution is minimal. No extra variables just pass file name from Local File Browser. Excel reader will keep all the parameters for the tab for any file.