BlockUntilDone: Multiple outputs from macro written to different sheets of same excel

I have a macro that processes data and outputs three datasets, which I need to write to the same excel workbook into different sheets. When I had the code running as a normal workflow, I was able to get BlockUntilDone to work properly.

I converted my workflow to a batch macro with three outputs and I am trying to write the macro outputs to Excel using BlockUntilDone from the workflow where the macro is called. But only one sheet gets written and I get errors for the other two. I also tried placing the BlockUntilDone inside the macro just before the Output tool, but that doesn't work either. Can somebody please guide me here?

Thanks.

Edit: Adding some screenshots of the macro and the workflow

Macro called in workflow Macro MacroOne of three outputs within the macro

Error Message

Tips and Tricks

Batch Macro

Accepted answers

danilang

Hi @aliensurfer

The Block Until Done(BuD) tool only attempts to control execution priority to the 3 connection on the same tool.

There's no mechanism to synchronize the output of multiple BuD tools like you have in your workflow.

Unfortunately, the three outputs need to have the same or very similar schema in order to flow through a single BuD. If the three streams of data have widely differing schema, you'll need to use something like the Parallel Block until Done from the CReW macro pack.

Dan

aliensurfer

@danilang Thanks I understand now how the BUD tool should be used. I modified my workflow by breaking down the single macro into three different macros (that would still write to different tabs within the same file) and used the Join tools as workarounds to pass the same input to the other two macros. The schemas of all three outputs are different, btw.

All comments

binu_acs

@aliensurfer would you be able to screenshot your batch macro and the workflow?

aliensurfer

@binu_acs Thanks for the reply. I have added screenshots of the macro. Unfortunately, I cannot add the screenshot of what the macro does, but I've added a couple of macro screenshots where I could.

binu_acs

@aliensurfer Can you also screenshot your output tool configuration? or check is it something like below

aliensurfer

Hi @binu_acs yes I confirm that all the three output tools are the same as what you have shown in your screenshot. The only difference was that Skip Field Names was unchecked. I checked that and ran the workflow, but I still see the same errors for two out of three Output tools.

Error: Output Data (8): Unable to open file for write: ... The process cannot access the file because it is being used by another process. (32)

binu_acs

@aliensurfer One more question, are the sheet names different? Also, can you explain how you are generating the 3 output files using the batch macro? Are they having the same schema?

danilang

Hi @aliensurfer

The Block Until Done(BuD) tool only attempts to control execution priority to the 3 connection on the same tool.

There's no mechanism to synchronize the output of multiple BuD tools like you have in your workflow.

Dan

aliensurfer

@binu_acs Sheet names as well as the schemas of the three tables are all different. I got a solution to my problem when I realized the way I was using the BUD tool was fundamentally wrong. Thanks for taking the time to look into this.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335