Open the only .xlsm file in the current Folder regardless of its name

[dvdfrck]

Hi,

I want to import the data from the only .xlsm file from the current folder, regardless of the files name. I use the directory tool to get the file path. How do I get this path into the Input data tool to import the data?

Or do I have to use a complete other way? 

Thanks in advance,

David

[neilgallen]

You’ll need a dynamic input tool. The tough idea can be found here. 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/load-multiple-excel-files-from-a-folde...

[Sntrada]

Hi @dvdfrck , 

I have attached a sample that imports a .xlsm file from a specified folder. This solution would import any files ending in that .xlsm extension...so if there is more than one such file in the specified folder, it will import them all.

Let me know if you meant something else by "current folder", or if you have any questions. I attached a screenshot of the pop-up you get once you add the file path, make sure you select .xlsm as the file type. 

[Sntrada]

Hi again @dvdfrck,

Saw your other post and I see that by the current folder you mean the folder your workflow is saved it. 

I've attached a solution that does this. Here is a screenshot of the config. 

Let me know if you have any questions! 

[dvdfrck]

Hi Sntrada,

thank you for your help. I tried to use your solution, but two problems occured I do not know how to solve:

1. for example: I have the folder structure C:\A\B\D and the folder D is my "current folder". So all my Atleryx documents and data is saved there. If I use your solution, it does not search in folder D for .xlsm files, but in folder B. Why ist that and how can I change it?

2. How do I specify the sheet I want to get from the .xlsm-file? The sheets will always have the same names so I could hardcode those.

thanks again for your time!

Greetings David

[dvdfrck]

Hi neilgallen,

if I use the dynamic input tool, I have to ad the "Input Data Source Template" manually. The workflow shall work in different folder structures and on different PCs without adapting it. It would be necessary to enter this template every time it is used. Is there maybe a workarround?

thank you for your time!

Greetings David

[Sntrada · Accepted solution]

Hi @dvdfrck 

I made a video with the fix to both questions. You can watch that here if you need more detail: -  

https://drive.google.com/file/d/1tvTGkwFfa079obecU6yUmMODHeCD1YDn/view?usp=sharing

I believe that the first issue is because I had an extra period in that wildcard path, it should be like it is in this picture:-

For your second question, the first time you build this on your end, manually import the file. After you make the path dynamic, the sheet you selected before should still stay in the config. 

Let me know if you encounter any more issues with this! 

[dvdfrck]

Hi Sntrada,

thank you so much! It works 🙂

Greetings, David

[dvdfrck]

Hi Sntrada,

now I want to do the same for the output, to overwrite the sheet I importet first.

I tried it with the Output Data tool:

 

Do you have a solution for this? 🙂

Greetings, David

[Sntrada]

@dvdfrck 

This seems to be working. Let me know if you have any questions, or encounter any issues.