I am not experienced with reporting tools, but since noone has yet replied I'll guess that the issue is that in render tool you are choosing to "Replace entire path with Group" but the group is neither path nor filename. Try changing this option to "Append Group to Filename", or "Replace Filename with Group" and you should be OK to start with.
In my experience, the "Error: InboundNamedPipe GetOverlappedResult: The pipe has been ended" error is one of the non-descript errors that comes up in a number of situations when another, more specific error message isn't available.
The problem here circles around how that the Render tool and Output tool utilize Excel sheet names in file paths. Spoiler alert: the Render tool doesn't use sheet names in the file path, they have to be worked into the layout.
In this workflow, it looks like the [Type] field is intended to be your sheet names so let's build that into the Layout. The idea is that we're going to combine each sheet into one Layout record with vertical breaks. I added another Layout tool to group the sheets by Fullpath (so the field passes through), and set "Vertical with Section Breaks". In the case of Excel rendering, "Vertical Breaks" = "Sheets".
Check out the attached workflow and let me know if you have any questions.
@CharlieS Thank you so much for this! Can I just ask a couple questions - this is mostly likely the solution, but I didn't follow all the steps:
a) You've disabled my full path formula - is that correct?
b) Where is the final data getting output to? Is the %temp% the path I should override with the constant path? Where is the path getting defined? When I ran your workflow, it didn't save the file anywhere
Ah yeah, I updated your output path so it would work for testing on my machine.
The shortcut %temp%' in Alteryx will always reference the temporary directory as configured by your Designer installation (this can be changed in System Settings). It's a suuuuper handy shortcut to know for when you want to test output in a temporary location (so you don't write over anything important while testing/building) AND it will always work. Since my computer doesn't have an F:\ drive, I used this to make the workflow run in the first place. The contents of the '%temp%' folder is deleted when you close Designer.
To see/find the files written, use the Results window and find the message from your Render/Output tool. Click on the link there and it will open the file for you. You can see that the exact location is pretty much alphabet soup, so I just stick to the links and never try to navigate on my own.
Just replace the %temp% with your original F:\ path in the Formula that creates it and you should be all set.