Can't write/access excel files with multiple tabs -and crew macro doesn't work

[ck2019]

I have the Crew macros in place and up until recently if I used the parellel block until done I was able to write to an Excel tab, and then it would go on to the next anchor to allow it to write to another tab in the same file.  However, nowadays I am getting the error 

 Output Data (215) Unable to open file for write: ... The process cannot access the file because another process has locked a portion of the file. (33)

 The only solution I can use is to use the Crew Macro wait a second, but the issue here is that instead of waiting a second it takes like ten minutes to run a process that runs in seconds if i write each tab individually by connecting and deleting each anchor in turn.  

I can't use the block until done because the tabs are feeding off one join tool - or at least i think that's the case unless someone has a solution!

I can't share the workflow as it is sensitve data

Thanks

Cass
 

[mceleavey]

@ck2019 ,

You can wrap the output in a macro and simply feed in the output fullpath, including the sheet, as the control  parameter. Then it will simply loop through and write out each one in sequence.

M.

[ck2019]

oh thanks @mceleavey - it makes sense what you are saying but i am not sure how to do it! I'll search the community and post back if I need help! Thanks

[mceleavey]

@ck2019 ,

If you post your workflow I can show you.

M.

[ck2019]

@mceleavey  thank you for the assist.  I can't post the workflow because of confidential info, but I have mocked up one with the same principles, albeit that the content is so short that it only fails out on one of the joins when using the parellel block.

If you could demo how it would work I can then apply it to my workflow...thank you...!

[mceleavey]

Hi @ck2019 ,

I've built a workflow with two methods to achieve this. Try the first method first, this simply uses the Block Until Done output sequence to ensure the data completes the processing in the order dictated by this tool. This should allow you to write to each sheet in sequence, each one completing before the next one begins.

If that doesn't work, try the second method, which applies similar logic but wraps each subsequent output in a macro process.

I hope this helps,

M.

ps - I've exported the package into a .yxzp. If you're on an earlier version of Alteryx, open the file in notepad and change the version number in the xml to your version, then save the file. If you don't want to do that, I've attached the workflow and the macros.

[ck2019]

Thanks for this @mceleavey , I don't think it quite gets me where I need to be though.  You have done your solution all from the left join, where as I need to write to three different tabs from each join. The block until done doesn't work in this way because of the three streams.

I did try adapting your workflow with a parellel block but i still get the same write problem on the third file like beforehand. However, I added a wait function into each macro and it was much quicker but when I applied that same logic to my workflow 11 minutes later and it is still running, yet when you write the file individually it takes seconds.

I don't think the wait macro is working as it should in this instance, but i don't really understand how it does work tbh.

If you have any further ideas they'd be appreciated...

[ck2019]

meant to add the mod that works here because of the low volume, but not in my file where there is c3000 lines of data on one (macro join) and 20 on the other - it is the 3000 line that is taking an age...

[mceleavey · Accepted solution]

Hi @ck2019 ,  the one I posted was just an example, but I've built that now.

This is sequential between all join outputs. It outputs using a Fullpath field in each macro which you will need to change in the formulae tools:

When I run this, it outputs to multiple tabs on the spreadsheet:

You will obviously need to pick which fields you want to carry through etc, but the logic on the joins is simply taking the left and joining it on two fields that will never join. It's a non-join, these tools simply make it sequential.

Hope this helps,

M.

[ck2019]

Brilliant - thank you - wouldn't have thought of that...will put it into action...my solution did work, but it took an hour and 20 minutes to run!!  Hopefully this one will be much quicker! I guess when you do it like this you don't need the macros either - will try it without first and see if it works and then revert to your option - thanks a lot for your help.