Block Until Done Usage: Order of the worksheets in excel is not as expected

[Dhiren]

Hello all, 

As per the workflow, I am trying to use Block Until Done to write this into the Excel workbook into different tabs.

In the attached workflow, i am expected summary tab to be written first (In the top right part of the workflow), followed by the detail tab (the tranche below the Summary tab) and then finally the bottom part of the Block Until done. I have checked the IDs and the ID of the first Block Until Done is 165 and the second one is 166. Any idea why the tabs are not in the expected order in the output.

Regards,

[Jean-Balteryx]

Hi @Dhiren ,

Everything in your upper container is computed from the output "1" of the Block Until Done tool so it's not executed sequentially. The Block Until Done works the following way : anything after output 1 is executed then anything after output 2 then anything after output 3.

[HomesickSurfer]

Hi @Dhiren 

Your data is processed appropriately, however, your output file contains various sheets in undesired order?

If so, it's because of the sheetnames being output in alphanumeric order.  Can you consider appending a numeric prefix to the sheetname prior to output to force the order?

eg.

01 - SUMMARY

02 - DETAIL

03 - BLAH

04 - BLAH BLAH

[mceleavey]

Hi @Dhiren ,

as the other guys have said the tool works in sequence according to the output, however with writing to Excel on multi-tabs in the same sheet you are subject to Excel as a third party platform, and sometimes that behaves like...well, like Excel.

The safest and most consistent way to do this is to create the output in a macro, create the full path names and use these as the control parameter in the macro. This will allow you to loop through each combination of workbook and tab without trying to access the file while it is still in use.

M.

[Dhiren]

Thanks for that. I did prefix the tabs with numbers so that desired order is seen. In Block until done. I am expecting that my big container which is on the Output 1 tranche of the main container gets executed first, followed by output 2. However as @mceleavey suggested, excel seems to be messing up the order. I am constrained about using macro, so cannot use it here.

Regards,

Dhiren

[HomesickSurfer]

Hi @Dhiren 

Try rebuilding your workflow into another new one ensuring that you add your containers to the canvas in the execution order you desire.  It will run in that order.  I do that when .stuck.  try.

[mceleavey]

Hi @Dhiren ,

I'm sure what you mean by "constrained". If you provide some mock data with your workflow I can build it and package it for you, so you can publish to a server without problems.

M.

[Dhiren]

Hello, Apologies for the delay. Alteryx had become a lesser priority in the last couple of days. :(. Please find the revised sequence with the mock input files. Hope you are able to replicate and provide best use of Block Until Done.

Please add appropriate path for Directory and Directory 2 in the Text Input tool for the workflow to run.

Hope I will get some idea for the ideal use of 'Block until done' tool.

Kind Regards,