Overwrite Sheet (Drop) to a Dynamically Named File

[Jesse_Riegel]

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. 

[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

[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

[Jesse_Riegel · Accepted solution]

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

[Jesse_Riegel]

Thanks Sean - see my comment to Andrew - it was actually not a timing thing but a renaming issues with the asterix. Thanks again for the help!