topic RE: Export collection to Excel problem in Product Forum

Export collection to Excel problem

MarcoMatuttis1 — Fri, 06 Nov 2020 14:56:00 GMT

I have a small process that works with data from excel tables.

I basically import c Excel files as collections.

INPUT:
Prozessmodell.xlsx -> collection 1 named INTER-Prozessmodell
Vorlage.xlsx -> collection 2 named Zieldatei

I take specific data from collection 1 and add it to collection 2 and in the end collection 2 needs to be exported as an excel file again.

OUTPUT:
collection 2 -> Output.xlsx

My Export Page looks like this:

My Write Collection stage:

When I execute the stages it actually looks and feels like it is actually doing something since it takes some time. But when I open the Output.xlsx file later it is always empty.

Anyone got an idea what I'm doing wrong ?

Is there a documentation on these specific excel functions and if so, where can I find it ?

Also a in depth documentation for all the actions would be great. If anyone has a link I would be glad.

------------------------------
Marco Matuttis
Robot Process Automation Developer
INTER Krankenversicherung AG
Mannheim
------------------------------

RE: Export collection to Excel problem

MarcoMatuttis1 — Fri, 06 Nov 2020 15:04:00 GMT

I think I'm a little bit further to the solution I'm looking for.

I removed the open workbook and close current workbook stages and changed the Cell Reference to A1 in the Write Collection stage.

Now Blueprism asks me to provide a filename and lets me save the changed collection after the close Instance stage.

Somehow it does not automatically save to the already existing Output.xlsx

------------------------------
Marco Matuttis
Robot Process Automation Developer
INTER Krankenversicherung AG
Mannheim
------------------------------

RE: Export collection to Excel problem

david.l.morris — Fri, 06 Nov 2020 15:47:00 GMT

While debugging it, you could insert a 'Show' action just after Create Instance, and that way you could watch what it does on the screen. Helps a lot. I would remove the 'Show' action afterward for prod because I believe it'll go slower with it displayed on the screen.

If you're looking to write out to a new Excel file that doesn't exist, then the order would be this:

Create Instance
Create Workbook
Write Collection
Save Workbook As
Close Instance

You may also want some additional actions in order to create a new sheet instead of just using Sheet1 and what not, but the above should at least work.

------------------------------
Dave Morris
Cano Ai
Atlanta, GA
------------------------------

RE: Export collection to Excel problem

MarcoMatuttis1 — Mon, 09 Nov 2020 08:43:00 GMT

Thank you for your reply Dave. Unfortunately my initial problem changed a bit now.

The excel file (vorlage.xlsx) that I receive will have 2 worksheets.

Worksheet 1 has the data that I will be importing as a collection.

Worksheet 2 has several formulas that rely on the data from Worksheet 1.

With the bot process I have to add some data to the collection that I extracted from Worksheet 1 (that part already works) and then I need to write the modified collection back to Worksheet 1 in the same file.

Worksheet 2 will stay untouched by the bot.

EDIT:

I solved it now. I left initial instance where I imported the Vorlage.xlsx file open and used the actions: Write Collection and Save Workbook

------------------------------
Marco Matuttis
Robot Process Automation Developer
INTER Krankenversicherung AG
Mannheim
------------------------------