App doesn't link to Excel named range when running in Gallery

[adriansilk]

I have an analytic app that reads from multiple named ranges in a single Excel workbook. It works fine when run locally but when I save the app to my Gallery and run it from there it fails to automatically link to the named ranges it is linked to by design, instead offering me the list of sheets and named ranges to choose from.

I've reduced the problem to a trivial example attached. You will need to re-connect to the given Excel and specify your required output path in the penultimate Formula tool.

All I want to solve is it auto-connecting to the named range when running in my Gallery (which is on local web server); could be something I need to do differently in my Action tool between the File Browse and Input tools?

[patrick_digan · Accepted solution]

@adriansilk On the gallery, you will have to select a sheet, but your action tool doesn't necessarily have to use it. I would change your action tool to update value with formula and try something like: left([#1],findstring([#1],"|")) + "|||`FruitAndVeg`" . For explanation, the file browse will send over this syntax: FileName|||`SheetSelected`. So my formula strips out just the filename and adds the named range in the action tool. Then the action tool will update the input tool. 

[adriansilk]

Patrick, thanks for your tip, it's a shame I still need to pick one sheet/range name but it works fine when I do so cheers, Adrian