How to append new data to existing formatted Excel tables, so the table can extend?

[yxie12]

Hi all, I tried to set the Output Options to "Append To Existing Sheet", and it did append new data to the existing excel table, it's just that the new data was not included in the excel table, and no formatting or filters either.

So anyone knows how can we do it? Thanks in advance!!

My Output:

My expected output:

[Jonathan-Sherman]

Hi @yxie12,

I don't believe it would be possible to append to the existing table (would love to be proved wrong!), however you could import the existing table, union to the new data (to append), use a table tool to create the reporting snippet (table) and write out to a excel file using a render tool. In this example i'm appending 5 new records to my existing 30 record table.

Workflow:

Excel Output:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[atcodedog05 · Accepted solution]

Hi @yxie12 

I am assuming you are using below config in your output tool.

One thing you can do it what @Jonathan-Sherman you can read master data and new data. And append new data to master data.

Another trick is, this is bit of an excel hack. You can extended the range of the table to a large range(i have taken 10000 you can set a larger range depending on your data). 

So that when alteryx writes it will be in the format.

A slight drawback would be filter will show blank as an option. But shouldnt be a major issue.

Hope this helps 🙂

[yxie12 · Accepted solution]

Hi Jonathan,

Thanks for your answer. In the end, I found a way that has a similar logic to @atcodedog05 's. Basically, I append the new data to the existing table using the config like this:

And then I created an event that will activate the vb script that calls the embedded macros in the excel to extend the range of the current table:

Event config:

VB script:

macros in excel:

Thanks,

yilin

[yxie12]

Thanks, your solution inspired me to create one that fits my needs better!

[atcodedog05]

Hi @yxie12 

Glad to hear my approach helped to build a solution.

And Wow, your solution is really a great hack for solving the requirement 😎

You should definitely mark your approach also as solution. It will be helpful for others to know how it could have solved 🙂

Happy to help 🙂

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂