This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have tried to read in the community about possible solutions to my issue, but the differentiator here is that my sheets are different unlike the other posts in the community. So I have one Excel Workbook with 30 tabs and then 10 Workbooks with 1 tab each. I want to use Alteryx to combine my 11 total workbooks into one functional workbook with 41 tabs. Can anyone help provide guidance on how to combine these workbooks into one singular one?
This article about halfway down talks about a batch macro that can assist with your needs. You can build the macro to ignore the schema of your worksheets and have the macro look at the field names or use the position of the fields to bring in the data.
If your data does not meet any of the three criteria (same schema, same field names, same position) you will have to use Input Tools for each type of schema or build multiple macros such as one that looks at field name and other position. You can then use a Union tool to bring all of that data together.
You might get some good tips from this article here - I believe in this case with multiple tabs & files, a batch macro is the way to go.
Essentially, for the document that has multiple tabs, you will select that file in your Input Tool but then instead of selecting a single sheet name, you will bring in the "<List of Sheet Names>" for that file. You can then use a formula to concatenate the full file path with each tab name, like this: Filename.xlsx|||TabName
Once you have your list of file names (to which you can also add a list of the file paths + tab names for each of the individual files), you can feed them into a batch macro where each file/tab will get input one at a time and output to a tab in your combined file before processing the next file. This helps get you around the whole "different schema" thing.
If you need additional guidance, please let us know! There are probably more than a few Community members out there who have macros... @MarqueeCrew can probably tell you a few different methods, including one of the CReW macros that might work, the Wildcard Input tool?
I was able to do it by using Data Inputs and selecting each individual sheet and outputting it to a new workbook. Thus, having each sheet sent over to a new Excel file. The only issue with this is that I lost source formatting and formulas. If anyone knows how to resolve that aspect of it, then this solution works.