using dynamic input on excel files with different tab / worksheet names

[simon]

Here's my dilemma. I have 200 excel spreadsheets but they all have different/unique worksheet names (so not all Sheet1). I would like to create one output from all 200 sheets.

I used the directory tool to get fullpath which feeds into the dynmaic input tool. The DI tool works great but ONLY when worksheet name or tab is the same in all files (sheet1). Same is true when just using input tool and doing *.xlsx.

How can I get input / dynamic input tool to work when all excel files contain different worksheet names (LA, NY, ATL, etc). Has anyone found a workaround for this? (I am not about to drag 200 sheets to my canvas and merge them since this will become an app.)

Thanks,

Simon

[JAIN_P]

Hi I want to upload  to mutiple excel  files  from Column  eg: 4  to  10 and dynamically changing from Quarter to Quarter field values and also  want to pick up the data from the starting column A which is the months column .How will I do it through Macro .Can any help ?

[asteryx]

Hi @JAIN_P 

I think I've done something similar, though it sounds like you will have to add some steps so that you can change the sheet range as needed (which is why, I suppose, you are looking at a macro).

To get a specific range from an Excel sheet, I first used a Directory tool to get the full path to all the workbooks in my source folder. I then parsed the output field containing the path so I could work with the complete path. You can add a sheet name and range to the path to get what you want.

The formula for my case assigns a new value to the [FullPath] field like this:

[FullPath]+"|'PFE Metrics$a1:c7'"

[FullPath] is what comes out of the Directory and Parse tools

The rest is in double quotes: first a | character, which Excel requires and then the range syntax in single quotes: 'sheetname$range'

You could then add multiple formulas for each of your use cases and then feed them into a macro that reads the data with a dynamic path string.

You can also query the workbook for the sheet names if you need to get them dynamically.

[JokeFun]

Hi @Joe_Mako May I ask where are the two macros you mentioned? I downloaded the package from the link you provided and had it installed. Now I can see CReW Macros and CReW Test in the Alteryx tool bar. Are they what you mentioned? I am confused.

[leahpenelope]

Hi Joe_Mako,

My organization's administrator will not allow access to chaosreignswithin.com unless I have a valid reason so I'll need to know if the Wildcard XLSX Input macro will work with my project.

I have two excel files, template.xlsx and temporary.xlsx.

Template.xlsx has two sheets each with:

1 table

1 dynamic chart based on the data in the table

Temporary.xlsx has four sheets each of the exact same data structure.

After importing them I do not need to work with their data within, but to simply combine both files into one excel file and rename with a dynamic filename of the datetime format.

Will the Wildcard XLSX Input macro be able to support the initial reading in of the two excel files given that one contains a dynamic chart in one of the sheets?

Thanks!

Connie