Automate copy and paste Excel file before running workflow

My workflow outputs to a hidden sheet in an Excel file - each time I run the module, the data in my hidden sheet gets refreshed and the Excel sheet updates its graphs and tables using the hidden tab.

The process runs once a week, picking up scheduled text file outputs from our BI scheduling environment. So far so good. I'm struggling with automating the following steps and need some help please:

I need a way to automate the creation of the new weekly Excel file. Here are the steps:

1. Use Directory tool and sort descending on creation date to find the most recent report - let's call it Week1.xlsx

2. Copy Week1.xslx (which contains multiple tabs) and export it as Week2.xlsx

3. Run module and output data to hidden sheet in Week2.xlsx

I have figured out steps 1 and 2 already. Step 2 was quite hard, but I use the "Take File Name from Field" output option, so I can create the new file name in a formula tool.

However I'm struggling with step 2 - although I can input and then output an Excel file, I can't handle the dozens of sheets. Is there a way to get alteryx to read in all Excel Sheets, change the filename, and then output all sheets?

Or, do I need some kind of batch file to duplicate week1.xlsx and turn it into week2.xlsx before the module runs? I'm struggling to figure out the most logical way to do this. Thanks

Input

Output

Accepted answers

jt_edin

Thank you for this extremely helpful solution. Unfortunately I need a like for like copy of my Excel file because it contains millions of pivots, slicers and all sorts of things that Excel gets grumpy about if you try to change the internal data source or structure.

So, I eventually found a solution which creates a .bat file to copy the Excel file from one place to another, using the 'COPY /Y' command and flag which I'm guessing is a DOS command. I don't know much about .bat files, but I can dynamically create the filenames and folder locations in Alteryx, and then feed those into the run command. Script attached, which is stolen and adapted off @AdamR_AYX who posted a brilliant solution in the thread below:

https://community.alteryx.com/t5/Data-Preparation-Blending/Run-a-batch-file-from-Run-Command-tool/m-p/64889/highlight/true#M12126

Thanks @derekbelyea for your useful suggestions, I'm sure they will benefit lots of people in future.

BatchScriptExample v2.yxmd

All comments

derekbelyea

This simple example should help you to complete your project.

WHAT IT DOES: Copies the data from all tabs in an Excel spreadsheet (.XLSX) file to a new file with a different name.

LIMITATIONS: Only copies data. Does not copy formulas, formatting, pivot tables, etc. Source file name needs to be in the form : ???????ddd.xlsx

HOW IT WORKS: Captures the tab names from the source file and using a BATCH MACRO copies each tab to a new target file

TransferExcelDatatoNewFile.yxzp

derekbelyea

These Knowledge Base tutorials are also worth reading:

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Read-in-Multiple-Excel-Files-with-Multiple-Tabs-that-have/ta-p/51145

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Using-Reporting-Tools-to-create-Multiple-Excel-Files-with/ta-p/20780

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-multiple-files-using-the-same-excel-template/ta-p/20493

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-to-an-Excel-Template-File/ta-p/35978

jt_edin

https://community.alteryx.com/t5/Data-Preparation-Blending/Run-a-batch-file-from-Run-Command-tool/m-p/64889/highlight/true#M12126

Thanks @derekbelyea for your useful suggestions, I'm sure they will benefit lots of people in future.

BatchScriptExample v2.yxmd

Quick Links

Unanswered
Community Events
Groups

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335