How to move untouched Excel Sheets to an existing workbook? VBA? Run Command?

[TheMattLeonard]

Hello,

The Situation: I have ReportABC and ReportXYZ that I am performing various reconciliations between. Both reports have multiple tabs. I know what I'm doing, so I have batch macros set up for each to deal with the multiple tabs. I am performing various mappings, comparison, analysis, and using the Blob tool to output the final results into a nicely formatted Workbook template.

The Problem: What I would like to do is be able to have a copy of the exact input tabs from the two reports, lets say 4 a piece, input into the final report alongside my Alteryx/blob produced outputs. These tabs won't be referenced by formulas or anything in any way, they would just be there for visual confirmation/sanity checks back to the original data. It would be great if formulas came along however breaking links and pasting values would be a concession I am willing to make. Let me run through the various methods one might try and why they either don't do what I want, require additional work, or I can't figure out how to get it to work and could use some help:

The Ideas:

1. Use a batch macro and write each tab to the Blob output - Unfortunately, Alteryx touching this data at all ruins the whole point of it being an untouched, exact recreation of the original input.

2. Compile all of the data into a nice extract with fields to identify its source - I am already doing this, but again, I want the original reports.

3. Use the blob tool itself so the formatting doesn't get ruined - This doesn't work because I am using the blob for the main report outputs. One of the two would get overwritten, no matter how you set up the Block Until Done.

4. Incorporate the original report tabs into your existing blob setup - This is the closest to working. I have empty copies of each of the 8 tabs in the Blob Template and a macro that does absolutely nothing but input them from their source, and output to the Blob. The issue I run into here is that it means formulas don't carry over, the numbers get formatted as text since the report does not contain headers in the first row (or any row) consistently, and the report columns change enough that the static formatting in the template doesn't always make sense with what actually gets output. In theory, I could conditional format the hell out of these tabs, but at that point I'd rather just record a bunch of excel formatting macros and run those.

5. Run Command - This is where I start to get out of my depth, but from my understanding, Run Command can move whole workbooks, but can't deal with individual tabs, so I basically run into the same issue as number 3. Run Command cannot extract four tabs from my input and then input them into an existing workbook. I don't even need to be picky with which tabs get input, I'll take them all, but it's the output into an existing file without overwriting that seems to be the issue. 

6. Run Command to run VBA Script - From my "research", it seems VBA can move multiple tabs of an excel workbook into an existing one. Even with AI assistance (don't judge), I am not sure how to get a VBA script working and/or trigger it from Alteryx correctly. I'm aware you can configure "Events" to run a command upon successful completion of a workflow which I think could also be useful.

The Visual Aid:

I cannot upload any data or workflows, but I put together this super dumbed down version to better illustrate the goal:

This is a use case I think a lot of us run into, having an exact copy of the source automatically included with the output. I am a fairly experienced Alteryx user but I have never seen a foolproof solution that accomplishes exactly what I want, despite the instructions being relatively simple in theory.

I am capable enough to figure out how to batch macro any solution and make it dynamic for my use case(s), it's just the initial functionality I could use some help with.

Thanks for reading all of this if you made it this far!

[jrlindem]

Okay, skimmed through this.  What I would do is use BLOB or Run-Command to copy the original INPUT files and drop them to a new location.  Then use that location dynamically in your workflow and append the new tabs (of data from your workflow) to the existing copied file.  This way you don't have to break formulas nor worry about handling those tabs inside of Alteryx.

Does that spark some inspiration?  -Jay

[TheMattLeonard]

@jrlindem 

Hi Jay - I appreciate the reply, and I understand what you are going for. At the start of the workflow, I would essentially make a copy of the input and then write all of my results as new tabs there. However there are two big issues here that prevent this from being the solution:

1. I would then lose the formatting on the new tabs from the workflow, since any attempt to use the blob there would cause an overwrite. I guess in theory I could use the reporting tools in Alteryx, but I have always found those super tedious and unreliable.

2. More importantly, I also have two separate inputs here, and this approach would only be able to output one of the reports without overwriting the other.

Thanks,

Matt

[jrlindem]

@TheMattLeonard Hmmm, I see your point.  I think in that case, if I'm following, I would attempt everything you need to do to read the INPUTS, generate your output to it's own file and then use Python with openpyxl and/or xlwings libraries, for example, to access the spreadsheets and copy the worksheets into a final deliverable.  I have not done this myself but a quick read online suggests you may be able to do this while retaining formatting and formulas for each sheet.

Sorry I couldn't be of more specific help.  Maybe others can chime in too.  Cheers,  -Jay

[TheMattLeonard]

@jrlindem I appreciate the reply nonetheless, it's reassuring to know that the solution is not an obvious one I have missed. Unfortunately I do not believe I am able to access and use Python libraries with my work laptop, but I will do some more research into the Python solution and maybe see if IT can make an exception for me.

If it isn't, I forsee VBA being the solution for me.

Thanks!

[TheMattLeonard · Accepted solution]

@jrlindem I appreciated your input so I thought I would give you an update. I was able to bypass python by using powershell scripts and .bat scripts. Please see my solution post here: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/How-to-Use-Alteryx-to-Dynamica...