Output data into Excel template and export as new file copy

[Rich_Stebles]

Hello all

There's a few questions around outputting to an Excel template, but nothing is quite what I'm after, so hopefully someone can help.  

I have an Excel template file with a tab called "Data" and a tab called "Report" which auto-adapts around whatever happens to be in "Data"

I would like Alteryx to produce a *new file* each time the workflow is run, with new data outputted into the "Data" tab but also includes the "Report" tab exactly the same as it is in the template.  

Ultimately, this workflow would sit on the Gallery, and multiple users would be able to call down a new report just for them which is based on - but not amending directly - the templated version sitting centrally.

So, this is not about exporting data into an existing file; the goal is specifically to serve a new excel file to users which includes pre-built tabs next to the data tab

Thanks very much for any advice given

Regards

Richard

[NicoleJohnson · Accepted solution]

This one is a bit tricky, but looks like there might have been a similar request a while back that could point you in the right direction:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Automate-copy-and-paste-Excel-file-bef...

Unfortunately, Alteryx does not have a straight "copy everything in this file exactly as is with all formatting/etc. and create a new file" option... however, it does have the ability to run Batch commands, so with a fairly simple script (should see examples in the link above in the accepted solution at the bottom of the post), you could have Alteryx call a command that would copy the file for you, rename it, and then the remainder of your Alteryx workflow could be used per usual to populate the Data tab in the new workbook with new data. 

Hope that helps!

Cheers,

NJ

[Rich_Stebles]

Hi Nicole

Thanks for your response.

I have tried that method now, and it seems to work OK as a solution so I've marked your answer as correct, thanks very much :-)

I found some other ideas on including the Excel sheet as an asset, but I couldn't get those to work.  Somehow including the sheet bundled in with the workflow feels intuitively like a more tidy solution, do you think this is a possible way of doing this?

Thanks

Regards

Richard

[Rich_Stebles]

Hi Nicole

I have a futher problem actually, that I hope you (or someone) can help with.

The process you've highlighted works, but I also need the file copy to be given a new unique name.  I have done that with the DateTimeNow tool, but how do I feed this new name into the Output Tool's source?

Thanks in advance for any help

Regards

Richard

[Rich_Stebles]

Oh, I am being a bit silly, the solution is obvious.

For the benefit of any passing readers, dynamically renamed copies of files can be output to using the "Change Entire File Path" option:

Regards

Richard

[JakobJ]

@NicoleJohnson  has Alteryx updated an option regarding your reply:  "copy everything in this file exactly as is with all formatting/etc. and create a new file"  would be solved?  Is there a way to keep all formatting?

[NicoleJohnson]

There is a method for this that I've started using recently that involves the Blob tools - Using Blob Input to pull in the existing formatted file, then Blob Output to output a new copy of it, formatting etc. included, to a new filename. Then, by leveraging the "output to range" option in the Output Tools, and checking the box to keep formatting, you can update the data in the file while still having all the pretty formatting available. There are a few Community articles out there on this, I believe... AND, coincidentally, it's a topic I am presenting on at Inspire in May :) So more materials should be forthcoming in the next few months! But hopefully "Blob tools" will point you in a good direction in the meantime.

Cheers!

NJ

[JakobJ]

@NicoleJohnson Awesome thank you!  I'll give it at try.  Hopefully I'll be able to make it to Inspire and check out your presentation!

[EV]

Thank you for this! I wonder though, could this be adjusted to only copy certain sheets? I have a workbook with 5 sheets and only want to copy the first 3.