Topic Thread

Expand all | Collapse all

Excel Write Collection Keep Collection Format

Jump to Best Answer
  • 1.  Excel Write Collection Keep Collection Format

    Posted 11-29-2019 16:07
    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
    ------------------------------


  • 2.  RE: Excel Write Collection Keep Collection Format

    Posted 11-30-2019 11:43
    Have you tried writing the collection into a preformatted excel file?

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



  • 3.  RE: Excel Write Collection Keep Collection Format

    Posted 11-30-2019 18:24
    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
    ------------------------------



  • 4.  RE: Excel Write Collection Keep Collection Format

    Posted 11-30-2019 17:14
    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.


  • 5.  RE: Excel Write Collection Keep Collection Format

    Posted 11-30-2019 18:25
    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
    ------------------------------



  • 6.  RE: Excel Write Collection Keep Collection Format

    Posted 11-30-2019 18:32
    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
    ------------------------------



  • 7.  RE: Excel Write Collection Keep Collection Format

    Posted 12-02-2019 05:15
    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
    ------------------------------



  • 8.  RE: Excel Write Collection Keep Collection Format

    Posted 12-02-2019 11:05
    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
    ------------------------------



  • 9.  RE: Excel Write Collection Keep Collection Format
    Best Answer

    Posted 12-02-2019 16:30
    Edited by Marc Morin 9 days ago
    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
    ------------------------------



  • 10.  RE: Excel Write Collection Keep Collection Format

    Posted 04-24-2020 17:19
    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
    ------------------------------