topic RE: Excel Write Collection Keep Collection Format in Product Forum

Excel Write Collection Keep Collection Format

MarcMorin — Fri, 29 Nov 2019 16:07:00 GMT

Hi i need to write a collection to excel but using Excel VBO Write collection i am loosing the collection format, ex (003 in collection end up as 3 or 123.00 ends up as 123 in excel)
is there a way to keep collection format when writing collection, collections i need to write are not standard and are always a mix of text and number.

Thank you
Marc

------------------------------
Marc Morin
Consultant
PWC
America/New_York
------------------------------

RE: Excel Write Collection Keep Collection Format

kawaycheung — Sat, 30 Nov 2019 11:43:00 GMT

Have you tried writing the collection into a preformatted excel file?

------------------------------
kaway cheung
medewerker communicatie en begeleiding
aegon
den haag
------------------------------

RE: Excel Write Collection Keep Collection Format

blue_meanie — Sat, 30 Nov 2019 17:14:00 GMT

The easiest way is to set the excel worksheet columns as Text format. Use the Format Cell action, with a parameter of "@" to set the cells to Text.

RE: Excel Write Collection Keep Collection Format

MarcMorin — Sat, 30 Nov 2019 18:24:00 GMT

i did 'play' with it a bit with no success, but given i have 100's of files to process i didn't spent a lot of time on this as this is not really an option

------------------------------
Marc Morin
Consultant
PWC
America/New_York
------------------------------

RE: Excel Write Collection Keep Collection Format

MarcMorin — Sat, 30 Nov 2019 18:25:00 GMT

but then number column wont get calculated in sheet, sheet will not only contain info in writing in it

------------------------------
Marc Morin
Consultant
PWC
America/New_York
------------------------------

RE: Excel Write Collection Keep Collection Format

blue_meanie — Sat, 30 Nov 2019 18:32:00 GMT

You could prefix the collection values with a ', then Excel will treat them as text and retain formatting.

You can use Format Cell as you please, eg: 0.00 will format that Excel cell/range/column to number with 2 decimal places, etc.

------------------------------
Scott Robson
------------------------------

RE: Excel Write Collection Keep Collection Format

KrishnaA — Mon, 02 Dec 2019 05:15:00 GMT

Have you explored the option of saving the source file in .CSV format instead of .xlsx format? This could potentially eliminate the auto-format feature of Excel.

------------------------------
Krishna A
Blue Prism
------------------------------

RE: Excel Write Collection Keep Collection Format

AndreyKudinov — Mon, 02 Dec 2019 11:05:00 GMT

You could format columns to text first, but this is not in default VBO I think.
Writing to a preformatted template is another option, like kaway mentioned.

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

RE: Excel Write Collection Keep Collection Format

MarcMorin — Mon, 02 Dec 2019 16:30:00 GMT

Thank you all for your inputs what i ended up doing is (and it prob need some ironing out) is inside the Write collection i added

Dim Format as String
Dim ColType as Type

ColType = col.DataType
If ColType = GetType(String) Then
Format = "@"
else
Format = "0.00"
End if
GetWorkbook(handle,Nothing).ActiveSheet.Range(cell,cell).NumberFormat = Format

so i can format cell before writing data, so far it's working fine

Cheers

------------------------------
Marc Morin
Consultant
PWC
America/New_York
------------------------------

RE: Excel Write Collection Keep Collection Format

JonathanHolstin — Fri, 24 Apr 2020 16:19:00 GMT

Hi Mark,
I'm getting a bunch of errors when I paste that into a code stage. Can you explain a bit more what you are passing in or where you are doing this at.....

thanks!
Jon

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: Excel Write Collection Keep Collection Format

GabrielaEsquive — Thu, 02 Mar 2023 04:19:00 GMT

Hi Marc!

Could I see your code please? I'm having issues adapting the Write Collection Code to achieve the same result

Thanks in advance

...................

Gabriela Esquivel

RPA Developer

------------------------------
Gabriela Esquivel
------------------------------

RE: Excel Write Collection Keep Collection Format

Mukeshh_k — Fri, 03 Mar 2023 12:32:00 GMT

Hi Gabriela Esquivel,

You can use format to Text before writing anything on the excel sheet, for numbers you can still convert them through data conversion. This Text formatting will ensure that all the data from collection are pasted in its original format without any auto formatting.

Dim ws As Object = GetWorksheet(handle, workbookname, worksheetname)

ws.Range(CellRange).NumberFormat = "@"

------------------------------
Mukesh Kumar
------------------------------

RE: Excel Write Collection Keep Collection Format

GabrielaEsquive — Fri, 03 Mar 2023 23:15:00 GMT

Oh thanks! it's just that i don't have the cell range as it varies , so i couldn't fill that field, is there a way to format per column?

------------------------------
Gabriela Esquivel
------------------------------

RE: Excel Write Collection Keep Collection Format

Mukeshh_k — Sat, 04 Mar 2023 02:06:00 GMT

Hi Gabriela Esquivel,

Just pass the cell range as A:Z if you want this to apply on all the columns and rows- i.e. entire sheet.

and If you know which all columns requires only text or all orginal format- you can do that as well.

Let's say - Column B should be in all orginal Format and you dont wish to disturb the format of column A(for example Number column)

Just Pass B:B in cell range - it will be applied to Column B only.

See snippet on my previous answer where I am passing AI:AI - that's because I only wanted to keep AI column format as all Text.

Hope this helps.

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS England, United Kingdom, GB
------------------------------

RE: Excel Write Collection Keep Collection Format

KK — Mon, 25 Sep 2023 07:49:00 GMT

Hello Mukesh

We have one issue from collection --where writing the data in to excel from collection using the loop record by record. We miss some of the rows in writing to the excel. For example we have 80000 rows in collection after writing in to the excel we only get 60000 where we are missing 20000 due to which the number of the keyfigure value is not correct. So Please let us know if you have any idea or faced this kind of situation.

Thanks in advance..

------------------------------
KK
------------------------------

RE: Excel Write Collection Keep Collection Format

Mukeshh_k — Mon, 25 Sep 2023 08:40:00 GMT

Hi @KK - https://community.blueprism.com/discussion/using-collection-loop-the-record-one-after-another-and-paste-it-in-excel

------------------------------
Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------

RE: Excel Write Collection Keep Collection Format

LeonardoSQueiroz — Mon, 25 Sep 2023 08:41:00 GMT

Hello KK,

apparently this is a common issue: https://community.blueprism.com/discussion/api-auth-token-issue#bm3e4e4946-414f-4898-ab74-018acb6d1dcf

Use a proposed solution by Mukesh Kumar if it doesn't work I suggest opening a support ticket.

Regards,

------------------------------
Leonardo Soares
RPA Developer Tech Leader
Bridge Consulting
América/Brazil
------------------------------