Excel Write Collection Keep Collection Format

[MarcMorin]

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
------------------------------

[GabrielaEsquive]

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
------------------------------

[Mukeshh_k]

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
------------------------------

[GabrielaEsquive]

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
------------------------------

[Mukeshh_k]

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
------------------------------

[KK]

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
------------------------------

[Mukeshh_k]

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
------------------------------

[LeonardoSQueiroz]

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
------------------------------