Marco that Import specific sheet from Multyple excels

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/ta-p/20480

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

Macros

Batch Macro

Interface Tools

Accepted answers

TomWelgemoed

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.

Sheet specification v3.jpg

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).

Sheet specification v4.jpg

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

Best,

Tom

All comments

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.

Macro changes.jpg

Solution 12.yxzp

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,

TomWelgemoed

Hi @Jes_Li ,

Sheet specification v3.jpg

Sheet specification v4.jpg

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

Best,

Tom

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703