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

[kawaycheung]

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

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

[ScottRobson]

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.

[MarcMorin]

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

[MarcMorin]

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

[ScottRobson]

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

[KrishnaA]

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

[AndreyKudinov]

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

[MarcMorin · Accepted solution]

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

[JonathanHolstin]

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