How to Macro with 1 Excel with 40 tabs / sheets?

I have 1 Excel file, but it has 40 tabs / sheets.

Each tab / sheet has around 4 columns, but they are not consistent throughout the 40 tabs / sheets in terms of column position.

How do I dynamically import them with a macro? I can't seem to get my macros working at all.

Batch Macro

Accepted answers

FilipR

@caltang I forgot about updating the entire file path in the macro. Please see the updated version attached.

Unify Multiple Tabs with a Dictionary.yxzp

All comments

FilipR

If the column count and content is not consistent throughout the tabs, how would you like to have it returned? Creating a single table through a Union doesn't make sense, so a macro will not help you much here.

You have to find a way to unify your tabs, or at least find a way to group them, and later use the Dynamic Select tool to return the values.

caltang

Hi FilipR,

Wouldn't I need to load all the sheets into Alteryx first? Seems weird if I do it manually first.

How would you go about it? I am rather stumped on this.

binu_acs

@caltang you need to use the batch macro for your use case. first read all the tab's names using one batch macro and with the second batch macro you can read each tab's data and consolidate into one sheet

I am attaching a sample workflow for your reference

ExcelSheets_Combined_updated_0802.yxzp

caltang

Sorry, I cannot access nor run the task as I do not have the dependent files and I do not have your macro installed / created on my local Alteryx.

Could you assist? Thank you!

FilipR

@caltang The problem with inconsistent data structure is that no program will understand it unless you tell it exactly how to read it. Either way you will have to go in and see how each tab looks like, and if it is inconsistent then how exactly.

If it's just a matter of having one column in 38 tabs and it being missing in just 2 tabs, the solution will be different than, when you have completely different columns in all of your sheets and their placements are not always the same (say: you sometimes have your Project ID in column A, sometimes in D, and sometimes the field is called "Project ID", sometimes it is "ProjectNr"). If it's like the latter, there probably are no easy solutions.

DawnDuong

Hi @caltang

Depending on the answers to these 3 questions, then the approach will be different
A) does each tab (i) only contain the clean table or (ii) also have non-data stuffs that need to be specifically excluded? >> if (ii) then you will need at least a batch macro to do the cleaning first. If (i) then depending on the answers to the subsequent B and C you may be able to solve it with a dynamic input or batch macro.

For the 40 tabs, are the columns the same but having different positions only? >> if yes, Dynamic input should be sufficient.

C) Or do you have a fixed set of columns, but different tab has different number of columns and again in different positional orders? >> if this is the case you may need to specify a template that has all the possible columns then use the Dynamic Input. Or you can use a batch macro, and specify in settings to allow for dynamic columns.

dawn

caltang

Hi @DawnDuong,

The situation is currently B - 40 tabs with 5 columns, but the column positions are inconsistent throughout the sheet and the column names are slight variations of each other.

For example, Trx, Txn, Transaction, Transac, #

Dynamic Input -> Is there a way to specify the changes / positioning? Data types etc?

Thank you!

FilipR

@caltang I thought how I would tackle a similar case myself, and eventually I figured it out.

First you have to create a dictionary with all of your different column names and add the position of the column you want them in, in the final report (just put the fields you need, not all of them).

Then you add an Input tool and configure it to return the sheet names instead of data:

Then I created a batch macro for unifying the tabs. It will look at your dictionary and correct the column names and order, so that you can get your result as one table:

If in the tab there are fields that are not in the dictionary, they will be dropped. If a field from the dictionary is not found, you will see blanks in the result.

Unify Multiple Tabs with a Dictionary.yxzp

FilipR

@caltang I forgot about updating the entire file path in the macro. Please see the updated version attached.

Unify Multiple Tabs with a Dictionary.yxzp

caltang

Thank you @FilipR !

I will study it in more detail, but so far this works for me.

Thank you!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703