Output to multiple sheets within an Excel file

[alexisjensen]

Hi,

is is there a way to have separate output tools output to different sheets in the same excel file?  I selected the same file with different sheet names, but I get an error that the file is in use.

i appreciate any pointers.

regards,

Alexis

[KaneG · Accepted solution]

Hi Alexis,

This is possible with the use of the 'Block until Done' tool. This will cause one write to finish before the next starts.

Kane

[jb]

@KaneG or anyone else that has background, could you elaborate on how the Block Until Done tool could be used for this? I've got 3 separate data processes in my workflow. I am looking to write each of these as a sheet within 1 master excel file. The data is not structured the same in the 3 processes so they all need to be output independently.

Thanks!

[KaneG]

Hi @jb,

I might see if I can get this question moved to the Data Prep & Blending section of the forum as it is a diversion from the KB article and will see more eyes in the forum.

You can put the Block Until Done tool back where your data splits or if you put a Block Until Done tool before each of your outputs, this should work also. The other option is to seek out @AdamR_AYX's Parallel Block Until Done tool from the CReW Macro Pack. There are other ways if your data is disconnected but they all basically involve either connecting your data streams in some way or splitting them out to separate Workflows/Apps.

Kane

[cbz]

Hi Kane

I am facing similar issue as well. Where I have an excel file called worksheet 1, and within it there are 3 tabs called sheet 1, sheet 2 and sheet 3.

I have used 'Block until Done' Tool. which is works fine to output tab names only to different excel files.

But the content are not included at all.

Could you be able to advise please?

Please see the attachment.

Thanks

[Rasmus]

Not an expert at all, but in the attached you have selected .json as file format, and you are requesting excel as output?

In your case I would assume you need 3 flows extending from the "Block Until Done" function, each ending with an Output Data function configured to output data into the respective sheet. This can be done manually (thats what I did anyway) by pasting the path and adding the sheetname at the end:

L:\My Documents\Desktop\\MultipleSheetOutputs.xlsx|||Raw_data

Where Raw_data in this case is the sheetname.

Hope this helps :)

[jlefeaux]

Block Until Done isn't doing it for me.

I have three separate streams that each write to a (different) sheet in the Excel workbook.

Each of the three Output tools is preceded by a Block Until Done.
Nevertheless,I'm still getting the error "...cannot access the file because it is being used by another process."

In the three Output tools, the output option is Overwrite Sheet (Drop) -- does that make a difference?

I like this option because it should save me from having to go back and delete the file each time I re-run the workflow. But, it seems like when I do delete the files, the workflow runs OK the next time, without the error.

Puzzled.

[StephenR]

There are a couple of options.  The first is the parallel block until done tool from the Crew Macro Pack.  This tool makes the workflow order much more explicit.  The other option is to create a field that contains the tab name, then check the Take File/Table Name From Field option.  This will write each separate table and only open the file once. In order to do this, the data streams will need to be unioned.

[jlefeaux]

@Sruhl, thank you for your reply!  I wanted to check in and let you know that I haven't gotten a chance to look into what you wrote yet, but that I will soon and let you know how it goes.

[wildflower]

@jlefeaux I had the same error with Block Until Done! Do you have browse tools in conjunction with your output tools? I'm not sure if it's a bug but I played around by adding the Browse tool where ever I had an output tool and it worked, it finally wrote 4 sheets to one xlsx. After the workflow generated the excel, I removed the Browse tools and tested it again and it still worked! I have no idea why I received the "in-use" error initially without the browse tools. This issue was driving me crazy but it finally worked for me! Perhaps give it a try?

Output Tool Configs:

• I manually pasted in the Write to File path and replaced each <sheetname>: C:\Users\<username>\Desktop\FileNameHere.xlsx|||<sheetname>
• The first output tool has Overwrite file(Remove) and 3 other output has Overwrite Sheet(Drop)
• I have three separate streams with one Block Until Done connecting them to the input -- The first output tool is connected to the input