Marco that Import specific sheet from Multyple excels

[Jes_Li]

Hi everyone,

Is there a possibility to create a Macro that allows user to select Excels' directory(using Directory Tool) and specify the sheet name that want to input(assuming all those Excels have multiple sheets but all have this common sheet to be input)?

I have seen this one:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t...

which will import all Excels' all sheets.

I want to import the specific one, and specified by the user(i.e. so that user doesn't need to go into Marco to change sheet name).

Hope it makes sense....

Thanks

J

[TomWelgemoed]

Hi,

Yes, I think what you're asking is possible. Attaching an example that will take a text input (which you can turn into a file from a user or input in an Alteryx Gallery app) and then scan the directory for the type of files mentioned and then also the sheetname. The directory scan is not really necessary, but just added it to show the flexibility.

I don't know how well you know macros, but I'm highlighting below where the sheet bit is modified - it's in the long string of the file name (see image below). Be careful to select only an existing sheet name as the text you're looking to replace - and voila, the business can change it to whichever sheet they like (currently via the text input tool).

This is a more complex area of Alteryx, so hope this makes sense.

[Jes_Li]

Hi Tom,

Really appreciate your reply. When I connected this marco using Directory tool, it will ask me choose 2 fields(for each control parameter), one is file directory, another is for sheetname I want to input. How can I find the sheetname from Directory tool output? It seems only one input anchor is in that Macro...

Thanks,

J

[TomWelgemoed · Accepted solution]

Hi @Jes_Li ,

Sorry for the delay in my reply. I'm not sure if this answers you question, but you can edit the sheetname in the directory tool by clicking on Edit next to the Input template and then you will see a pop-up as below. Note that that is only to change your input "template", i.e. the format of the incoming data.

To thereafter tell the macro where to read the sheet details from, you need to select the drop-down as in the image below. You can choose to control this (e.g. by hard-coding the answer in a text input like I did) or you can give users control over this (e.g. by allowing them to change a shared file that you're reading into the macro).

That should be all there is to it. Let me know if I've misunderstood.

Best,

Tom