Bring in a Sheet From Multiple Workbooks

[smoskowitz]

Hi All --

Ok, next question. I have 3 Workbooks named:

1) Cash Taxes for Treasury Q4 2014.xlsx

2) Cash Taxes for Treasury Q4 2015.xlsx

3) Cash Taxes for Treasury Q4 2016.xlsx

I need to bring in the same sheet from each 'Cash Taxes - WW$'. Individually, each of these sheets works with my workflow -- but how to I bring in all three at one time to get a single dataset?

Thanks, 

Seth 

[Treyson]

Okay this is where dynamic input is getting a little fidgety. I downloaded 2015 and 2014 that you gave as examples and it's saying that they are structure differently when I ran it through the dynamic input, which if you look, 2015 has some things in column L, which means that there are more columns than the 2014 file and won't pull.

I have attached my example workflow. This won't work 100% for you since the directory tool is referencing my desktop, however you can see how we are going to beat this thing.

In older versions of Alteryx, when you went into a file it would say something like this: C:\Users\markst\Desktop\Directory Example\Cash Taxes for Treasury Q4 2014.xlsx|Cash Taxes - WW

Notice how there is a table name at the end of the string, which does not exist in the "full path" field from the directory. In the example I created a field where you can input the table name and it appends it to your string, which is working "fine" in the dynamic input tool.

Try that stuff out and let me know what you see.

Treyson

[Treyson]

Okay this is where dynamic input is getting a little fidgety. I downloaded 2015 and 2014 that you gave as examples and it's saying that they are structure differently when I ran it through the dynamic input, which if you look, 2015 has some things in column L, which means that there are more columns than the 2014 file and won't pull.

I have attached my example workflow. This won't work 100% for you since the directory tool is referencing my desktop, however you can see how we are going to beat this thing.

In older versions of Alteryx, when you went into a file it would say something like this: C:\Users\markst\Desktop\Directory Example\Cash Taxes for Treasury Q4 2014.xlsx|Cash Taxes - WW

Notice how there is a table name at the end of the string, which does not exist in the "full path" field from the directory. In the example I created a field where you can input the table name and it appends it to your string, which is working "fine" in the dynamic input tool.

Try that stuff out and let me know what you see.

Treyson

[suli]

Hi,

If you are passing 'Full Path' as an argument sheet name should be there. You can add '|SheetName' to the end of the path with a formula.

[Joe_Mako · Accepted solution]

Attached is a starting point, this can be turned into a macro with some interface elements to make it reusable. I am happy to work with you or anyone else to adjust this to fit needs.