How to: dynamic dropdown of excel sheet with dynamic filepath input?

Question

Hello!

I am working on a custom macro (not an app) for a workflow. We have excel files located on sharepoint. I am able to read these using the normal input tool. It works great. Now I want to stream this file into an in-db stream to enrich some data.

The challenge: I want to paste inn the sharepoint file path (or local excel file. it should act the same) in the macro, and then I want to macro to refresh the list of available sheets in that workbook.

I have succeeded in each step individually, but not when it comes to getting an updated sheet list based on the filepath i paste in.

Anyone have a good idea for this?

BenMoss · Accepted Answer

If you want this to work then you can not use a macro in a sense because everything is worked out at run time.

This type of thing you can do using the HTML gui extension, where the user would put in the site address and hit a button, this would trigger a script to run to go and fetch the list and populate the drop down.

An example of this can be found with the 'Publish to Tableau Server' tool on the gallery.

With a normal macro everything is generated at run time, so hence why your list doesn't refresh when the user pastes something in.

You can do this with a chained app, but as you mentioned at the start of the post, this isn't what you want!

Ben