How to Use Alteryx to Dynamically Copy Tabs from Source Workbook(s) to Target Workbook

[TheMattLeonard]

Hi Everyone,

I made a post a little while ago describing a situation I frequently found myself in and my desire for a robust solution. You can read it here if you like (https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/How-to-move-untouched-Excel-Sh...), but the tl;dr is this:

Essentially, I needed a way to make exact copies of the excel sheets I was working with, formatting and all (really the crux of the whole issue), and transfer them into the output excel workbook that Alteryx is creating. The manual solution of just opening up the input and output files and copying the tabs over is not that bad, but for whatever reason I was determined to figure out a way to automate this part of the solution. In my example there are only two source workbooks, but in a situation with 5+ it could really save you some time once you get it set up.

You know what they say, if you can’t find someone to do the job, do it yourself. I have solved my own problem, and have attached the entirely self-contained solution macro below, and I’ll put some instructions/guidance further down in this post. The next few paragraphs will be me yapping about how it works if there’s any interest. Be warned that it involves running .ps1 and .bat files so you will see your command prompt come up when running it. Not sure how everyone’s IT department feels about this.

The yap:

The solution ended up being a PowerShell script that is created and updated dynamically by an Alteryx formula, and then converted to a blob and output as a .ps1 file. As part of that Alteryx formula you also dynamically create a .bat script that calls the powershell script. I'll be honest, I don't recall the exact reasoning why Alteryx couldn't run the powershell script directly, but it works, so I'm sticking with it. You actually end up having to do two .bats because I can't figure out how to get the Run Command Tool to point to a dynamic location either, and I wanted the titles for these scripts to be specific to the files they’re affecting. This minimizes the chance of any accidental error occurring. Let me describe the complete sequence below: 

1. Source Filepaths and Source sheets are one input into the macro. The Target Workbook FilePath is a second input.
2. Summarize by Source File, Concatenate Sheet Names onto a single line per source file
3. Append the Target Workbook FilePath to each line of the Source File/SheetNames records
4. Here is where we start creating the scripts. You create one base script as a placeholder, and for the sections that need the filepaths and sheets specific to each source workbook, you build those individually by pointing the expression to the relevant fields
5. You then use a summarize tool to concatenate those file specific lines of code together
6. You then use a formula to replace the placeholder sections in the base script with the ones you just concatenated. This is also where other various Utility scripts are created (a .bat that creates a folder for all these scripts to go into, the .bats that run the .ps1 file, and a cleanup .bat). Everything is generated IN the formula tool so it remains self contained.
7. Now we output and run the executables
   1. First we create a folder called “MoveTabsExecutables” that all of our scripts will be output to. This is purely for organization so that the macro isn’t just leaving leftover executable files all over the place
   2. Next we output our actual .ps1 script. Then we output the .bat file that calls it, and then we output the second .bat file with a hard coded name that the Run Command Tool can point to. Subsequently and hopefully obviously, we then run the Run Command tool. This is where the copying of tabs actually happens.
   3. Finally, we output and run the “Cleanup Script”. The point of this is to “leave no trace” so to speak. It deletes the “MoveTabsExecutables” folder when it’s done, as well as the various temporary files that the Run Command Tool requires you to create
      
      

The Instructions/Guidance:

• I have no idea if anyone else will actually find value in this tool, but if you do, you will need to implement block until done tools into your workflow to get it to work properly. Half the reason it exists is due to issues with overwriting when attempting to use the "out of the box" tools.
• The macro has three modes:
  • Input Rows – You feed your FilePaths, SheetNames, and OutputFilePath into the input nodes of the macro directly. This is the way it’s really intended to be run.
  • File Browse – The macro is capable of operating as a standalone tool if you point it to a source workbook and target workbook directly. As simple as it gets.
  • Folder Browse – Same as File Browse, but with a folder. It will move every tab in every workbook that is input.
    
    
• The tabs it copies will overwrite anything that exists with those same tab names in the output. This means you can run it over and over and not have to do anything because it will overwrite each time.
  
  
• What this also means, however, is that you cannot have a sheet name in your target workbook that matches the sheet name of a source workbook the first time you run it. It will start appending things and it will get messy.
  
  
• If there are tabs with the same names in different source workbooks however, it will append (_WB1, or _WB2) respectively. Regardless of whether there is a conflict, the macro outputs a tab in your target workbook with a report of which tabs were copied and which _WB suffix relates to which source workbook.
  
  
• There can only be ONE Target Workbook. The filebrowse and folderbrowse methods force you into this. If you try to use more than one with the input rows method, it will simply sample the first one and copy all of your tabs there. If you have a use case with different destinations, I suggest you throw this macro inside another batch macro and figure it out from there.
  
  
• There can be any number of source workbooks if using the Input Rows or Folder Browse method.
  
  

The Leftovers:

• One of the tasks the “Cleanup” scripts does is delete temporary files. If you happen to have any .yxdb files with the word “delete” in it, contained in the same folder as this macro, it will be deleted.
  
  
• If you open up the macro you might see me label certain things as “MoveTabs” but in all instances, Move means Copy. The source workbooks will retain everything.
  
  
• Similarly, you might see FileName and FullPath get used interchangeably. This is because when you use an input data tool and choose to input the fullpath, it gets labeled as FileName. In most cases when I say path, I mean the complete path up until the file extension, and "file" will be just the file name itself
  
  
• While working on this I named the folder everything was in "VBA Script Test" because I thought VBA would be the solution. That wasn't the case, but I never updated the folder name. No VBA is used.
  
  
• If you try and open up the macro and run it, it won't work properly for a couple reasons, but most importantly, it is only meant to be run one method at a time, so disable those other containers.
  
  
• AI did help with creation of the .ps1 and .bat scripts. 

You might be thinking “wow, this was a lot of work for you to set this up, to accomplish very little”.

You are correct. But it’s done now.

[TheMattLeonard · Accepted solution]

Macro copied here. Enjoy

[Richard_Data]

@TheMattLeonard Thank you for posting. I like your solution because it showed me how you approached a very similar problem of mine. My problem was i needed to upload several hundred sheets to cloud based excel workbooks, reachable via hyperlink. I ended up using Visual Basic instead of Powershell, although Microsoft is planning to remove it in 2027. I approached this problem by using the report text tool instead of the formula tool because it is a much larger field for the script and i can keep it as one large text without 15 formula tools. 

One good thing about vba, it creates a worksheet with a (1) after the name instead of overwriting if the same sheetname exists. 

The issue with report text tool and powershell is that some of the syntax such as } curly braces get removed by the report text tool. If anyone has a solution to this problem please let me know.

[TheMattLeonard]

@Richard_Data I appreciate the response! I'm glad there's at least one other user out there with a similar use case. I had no idea about VBA being discontinued, but that's honestly a bit of a relief as someone who never got around to learning it (and didn't use it for this project).

I had the option of appending (1) for new versions of the sheet instead of overwriting, but I chose the overwrite for my use case. I'll end up in situations where I'll be asked to generate a report in the morning, but then one of the source files will have an update that will require running the workflow again in the afternoon, and in those cases I just want to overwrite. I think any situation where I want to keep adding the new sheets I'll do a one off modification adding some kind of timestamp or another identifier.

I usually avoid the reporting tools like the plague since they can be tedious to setup when you're trying to use them to actually format an excel workpaper, but I can see how having the larger field helps.

In regards to your question about the curly bracket, I believe I have quite a few curly brackets in the scripts. Formula tool does not seem to have an issue with them. You're using single quotes (') instead of double quotes (") to surround your code, correct? I realize it might be a little different since the Report Text tool does not require everything to be in quotes to begin with.