Overwrite Sheet (Drop) to a Dynamically Named File

Hey A-Team,

I am currently trying to do 2 things:

- Dynamically rename an Excel file using the filepath and sheet name from a field (Working)

- Drop another sheet to the same Excel document that is being dynamically renamed (NOT Working)

I was able to drop two other sheets (one table, one text) into the main workbook prior to dynamically renaming the file by using the: "filepath\*.xlsx|||Sheet1" method, using the asterix to be a wildcard for any Excel sheet in that folder. This does not work with the dynamically renamed file... Has anyone ever successfully done this before?

Thanks - my Alteryx crashed as I was about to attach a screenshot (it didn't autosave my last changes) so I don't have time before leaving but if someone needs it I'll put it here tomorrow.

Publish

Reporting

Output

Tips and Tricks

Macros

Preparation

Interface Tools

Accepted answers

Jesse_Riegel

Hey Andrew,

I installed the Crew Macros but the Parallel Block still wasn't jiving with my workflow. I found out that for the dynamically renamed file I was trying to drop into, Alteryx wasn't liking the "*.xlsx". What I ended up doing was creating a workflow to append the same filename to all three pieces I was trying to combine. That way, all three were being dynamically renamed at the same time with only different sheet names and that worked.

Thanks for the crew macros though, I'm going to try them out on more things later on!

Jesse

All comments

andrewdatakim

I would recommend the Crew Macro "Parallel Block Until Done", this will allow you to http://www.chaosreignswithin.com/. I use it all the time to create a new workbook and then add new sheets to it. You will need to use the same file path for both and then swap out the sheet name for the second sheet output. Bingo! Multi-sheet workbook from the same workflow. All thanks to the CREW Macros.

Parallel Block Until Done

SeanAdams

Hey Jesse,

I suspect that the problem is that when you set up the output tool, it defaults to CSV type output. The other problem may be that the file is being blocked by a lazy write process - this would be when you have two different output tools both writing to the same file.

Lets eliminate option 1 first

I've done a very simple flow to demonstrate multi-tab across multi file for you, with screenshot and alteryx flows.

If it's option 2 - that there's a write lock on the file and you have 2 different outputs, then like @andrewdatakim says you may need to deal with blocking. we can't use the CREW macros at work (they don't allow them inside the firewall) but I've had some success with using a "Bock until done", and 100% success in wrapping these in macros.

I'll demonstrate the block until done in a followup post, and if that doesn't work on your machine we'll do the Batch Macro solve

Cheers

Sean

Dynamic Output.yxmd

SeanAdams

Hey Jesse,

If you're writing from two different excel outputs, then you may need to block the one until the other is complete. There's two ways to do this - one is blocking tools, and the other is serializing via use of macros. I've mocked up the blocking one below - if this doesn't work then we can build the macro for serializing via macros next.

cheers

Sean

DynamicOutput-ParallelWrite.yxmd

Jesse_Riegel

Hey Andrew,

Thanks for the crew macros though, I'm going to try them out on more things later on!

Jesse

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703