Write Excel from collection

[DavidAssouline]

Hello, Hope you are doing well. I've automated the search and download of various files on specific companies from different sources. All files are saved in a dedicated folder. Now, I'd like to merge the files together following a condition (here all company A files together, company B all together etc...) I've now managed to filter my collection, i.e. having a temporary output collection only with the company A files. Now, from this temporary collection (let's say there are 4 rows in the collection), I'd like to actually merge the 4 files together with the condition to have one tab in the Excel per file (i.e. here 4 tabs)?  Could you please help? Thanks

[AndreyKudinov]

It looks straightforward: create workbook, for files in collection [open file, copy sheet to new workbook, close file], save workbook, close. (assuming only 1 sheet in each file) What did you try already? What part of this are you getting stuck on?

[DavidAssouline]

Hi Aikudino, Thanks for your answer.    I ended up doing what you suggest but by copying the content of the worksheet to the new workbook, creating each time a new sheet. It works but it's not perfect I'd say. You mention ""copy sheet"" which I could not find in the Excel VBO. Where is it? 

[AndreyKudinov]

There is no existing action for it, but it is possible to create new one to copy sheet. Method you could use is: https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.ex…   I guess your way is fine, unless you can create your own custom excel vbo action. I'm not sure if copying sheets instead of data would be much faster.

[DavidAssouline]

Thank you I will have a look.

[AndreyKudinov]

In general, I record VBA macro, then paste it in code stage and change syntax to match interop (mostly the same) and make it more generic