[Urgent] Macro that gives back the data from any filepath

[dvdfrck]

Hi,

I am trying to create a macro, which takes a 1x1 table with any filepath of any .xlsm file as an input and gives me the data form that file as an output.

Later I will have multiple Folders which all contain 3 .xlsm sheets. the workflow will be saved in every folder an is going to be used only once in every Folder. Thats why I want a really dynamic implementation without adjustments for every Folder. With the directory tool (.\ and *.xlsm*) I get all the file paths and with that I want to get the data behind the paths. I mean it must be possible somhow to get the data from the file with "only" the filepath. But I dont know how and I am running out of approaches.

Thanks for your help in advance!

Greetings, David

[paulfound]

Hi @dvdfrck 

Think it's not possible as different files have different outputs. I Completed an old weekly challenge recently #180. 

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-180-Thousand-File-Challenge/td-p/461356 

This is the whole challenge, I would check it out for some answers, or ideas.

Thanks

[dvdfrck]

Hi PaulFound,

That is not a Problem. The Files are pretty similar and I already have a Filter which transforms all possible data into the same structure. I just dont get the data importet.

Greetings David

[paulfound]

Maybe it would help if you shared an example? 

Maybe share what you have already?

[dvdfrck]

Hi PaulFound,

saidy the data is all confidential. I really just need something that takes the 1x1 table containing the filepath and puts out the data from that file. 

for example:

table 1x1: C:\user\david\a1.xlsm -> "Something" -> data from a1.xlsm

or

table 1x1: D:\user\alex\abxdfg.xlsm -> "Something" -> data from abxdfg.xlsm

The "Something" is that what I need and it should work for all possible filepaths without the need for a manual interaction each time. 

Does that help you?

Thanks for your time!

Grettings David

[paulfound]

Sounds like you want the Dynamic Input Tool.

https://help.alteryx.com/2020.2/DynamicInput.htm

This takes a filepath as a parameter and returns the data.

[dvdfrck]

I tried it put it always want another path first otherwise i get an error

[paulfound · Accepted solution]

@dvdfrck I think I know the issue.

Its a bit of a messy fix, but it should work.

1. Drop in a dynamic input. 

2. Type into the template source to %temp%test.csv and click ok. 

Notice that this is set to a CSV file:

3. Make sure your able to view xml properties:

If you cant' go to user settings and enable it.

4. Once enabled click on the dynamic tool and view xml

5. You should see this:

or something similar.

6. In the top right click edit and change the FileFormat from "0" to "-1"

if you click back onto the dynamic input and click edit you'll see that format is blank:

And that's it. as long as the outputs have the same number of columns it will work.

[dvdfrck]

Thanks PaulFound,

I followed your instruction and another error has occured when I tried to run it:

I manualy add the name of the Sheet with a formular tool and the syntax : "|||'Datenbasis$'". Is that maybe the mistake?

[paulfound]

Hi @dvdfrck 

filename|||Datenbasis

should be enough, there as long as the excel has a sheet called Datenbasis.